SQLServerCentral / Discuss Content Posted by Joey Doherty / Article Discussions / Article Discussions by Author / Indexes and Fragmentation in SQL Server 2000 Part 1 / Latest Posts

RE: Indexes and Fragmentation in SQL Server 2000 Part 1

dataguy2202 — Mon, 12 Nov 2007 20:31:48 GMT

Hey Cort Ellingson - email me !!! Victor Champney vwchampney@aol.com

RE: Indexes and Fragmentation in SQL Server 2000 Part 1

binu john — Thu, 29 Mar 2007 03:29:00 GMT

Good Article, looking forward to have next..

RE: Indexes and Fragmentation in SQL Server 2000 Part 1

John Kotuby — Wed, 20 Dec 2006 09:26:00 GMT

Thanks to both Joe and Billy. We are currently trying to optimize a fairly large production DB, and adding clustered indexes is part of the plan. The database gets steady inserts, updates from 50 workstations that hit primarily 13 tables. We plan to re-create everything from scratch and allocate enough space this time around to accommodate 5 years data input without the DB needing to grow. Fortunately we have lots of disk space to work with. We will then read the data back in from the archived database. We run a nightly maintenance job that rebuilds indexes leaving 20% free space. We have stopped the part of the job that shrinks the database, as we were noticing that there was 0 free space showing in EM. With heavy inserts and updates everyday, 0 free space doesn't seem appropriate.We also were informed that the Sys Admin at the customer site had not been running ANY type of disk defragmentation in the last 2 years. The RAID 1 drive where our log file resides showed 46% overall fragmentation. The RAID 5 data drive showed 30% fragmentation. Yes I know that RAID 10 is better, but the server can't take any more drives.

RE: Indexes and Fragmentation in SQL Server 2000 Part 1

Girish Rathi — Wed, 20 Dec 2006 05:53:00 GMT

Joe,I like the way you present this topic step by step. Thanks. I really learnt much from this article. It is really helpful.Girish

RE: Indexes and Fragmentation in SQL Server 2000 Part 1

Laxma Reddy P — Thu, 26 Oct 2006 01:10:00 GMT

Joe,

Thanks alot for nice article. Would like to go for next part of this with not much gap. Hope you will post it very soon. By the way, I have question here...how data stores if we define a clustured index on a column which contains duplicate data and how it results when we SELECT it..please clarify.

Onceagain thanks for spending time for this good work.

-LRP

RE: Indexes and Fragmentation in SQL Server 2000 Part 1

Joe Doherty-260822 — Wed, 25 Oct 2006 06:44:00 GMT

John,

You are right to point that out. Perhaps I should have made it clear that we were dealing with data that had NO indexes applied (clustered and non-clustered).

With part 1 of the series I simply wanted to get people thinking in terms of a heap of data stored in data pages. And to also introduce a few basics we could build on.As you will see in the following parts we will introduce clustered and non-clustered indexes.

Joe

RE: Indexes and Fragmentation in SQL Server 2000 Part 1

dbaforever — Wed, 25 Oct 2006 06:30:00 GMT

Thanks for an enlightening article, Joe.

You state: "Running any form of query on a heap will force a full scan of the table and could take a considerable amount of time. And the results from the DBCC SHOWCONTIG command, which is a tool for measuring fragmentation, is also of little use on a heap. "

I want to make sure I understand correctly. If TableA has a non-clustered index on ColumnA, but no clustered indexes..... and I select * from TableA Where ColumnA = 'abc'......... a table scan will be performed? I didn't know that. I thought by having an index on the column I'm querying on, that was sufficient to prevent a table scan (unless the table only has a few rows of course). Please elaborate.

Thanks, John

RE: Indexes and Fragmentation in SQL Server 2000 Part 1

Reecha Mishra — Wed, 25 Oct 2006 00:31:00 GMT

It was a great article!!! I am waiting for the next article as this gives ignition to all thoughts regarding Fragmentation.

RE: Indexes and Fragmentation in SQL Server 2000 Part 1

Steve Calverley — Tue, 24 Oct 2006 15:41:00 GMT

Thank you *very much* for a really interesting and useful article and I'll be watching closely for more installments.

RE: Indexes and Fragmentation in SQL Server 2000 Part 1

B Hilderman — Tue, 24 Oct 2006 14:47:00 GMT

Looking forward to someone being able to explain why extent scan fragmentation shows in the high eighties or ninties AFTER turning OFF sql server services... No change whatsoever...

RE: Indexes and Fragmentation in SQL Server 2000 Part 1

Thomas LeBlanc — Tue, 24 Oct 2006 09:26:00 GMT

Thanks for the article, very nice, and I look foward to the continuation on this subject.

ThomBeaux

RE: Indexes and Fragmentation in SQL Server 2000 Part 1

Charles Kincaid — Tue, 24 Oct 2006 08:18:00 GMT

In the days before good databases were avaialable for PC's I was having to retrive data from random files in an ordered manner. We used bianry files to store the data and then built an index for the ordering (MySQL anyone?). We used a tree. Not exactly a B-tree.

First, we did not give a rip about balancing. The ordering of blocks within the index file was of almost no concern. Even at 233 clock speeds we were able to fetch the first record in an order in way under a second.

Performance suffered when we had to add all of the overhead of making it multi-user capable. This was in the days when the only networking that we had was shared file access.

RE: Indexes and Fragmentation in SQL Server 2000 Part 1

Clive Strong — Tue, 24 Oct 2006 03:24:00 GMT

Nice article. Been doing lots of digging around with data pages/indexes/heaps of late as it's a hot topic where I work. Looking forward to seeing the next article.

RE: Indexes and Fragmentation in SQL Server 2000 Part 1

colin.Leversuch-Roberts — Tue, 24 Oct 2006 03:09:00 GMT

You're not totally correct concerning a HEAP, some rdbms allow the table and index structures to be defined ( not all B Tree's by default ) heaps do not always fragment. IF the table is not subject to updates which cause deferred updates and there are no deletes then your heap will not fragment, all new data arrives on the top of the heap - hence the term. Secondary indexes on a heap can be defragmented without issue. Technically if your heap is a fifo ( first in first out ) then even the deletes would not fragment the table structure.

I agree that it's best practice to have a clustered index, and in fact most tables I see are still heaps , but with a clustered index. Also agreed that the leaf or data fragmentation in a table cannot be removed without a clustered index, however not all heaps fragment.

RE: Indexes and Fragmentation in SQL Server 2000 Part 1

Joe Doherty-260822 — Mon, 23 Oct 2006 14:06:00 GMT

Mike,

I'll make sure information on all data types is included within this series. Thanks for your input.

Thanks to everyone who has contributed and shown an interest.

Joe

RE: Indexes and Fragmentation in SQL Server 2000 Part 1

Charles Kincaid — Mon, 23 Oct 2006 10:00:00 GMT

Great article Joe. Thanks a bunch. Your take on extent switching was new to me. I'm in hopes that the rest of your series will help clear up a mystery for us.

Billy, I agree with you. This goes into our mystery. One table with less than 100 thousand rows had sporadic inserts take MINUTES to complete. The majority of inserts take fractions of seconds. All DBCC indications were clean. Re-index had been done and still it happened. A specific insert would hang. If you drop the row and re-run that specific insert would take long.

In frustration I advised the guy working the problem to drop all the indexes and recreate them. Most are non-clustered and the clustered is PK on the identity column. Dropping the clustered index took 20 minutes. Re-creating the indexes seems to have fixed the problem.

What the ...?

RE: Indexes and Fragmentation in SQL Server 2000 Part 1

PaulSp — Mon, 23 Oct 2006 09:19:00 GMT

Hi Billy

Great additional help to Joe's article.

Thanks for this.

Paul

RE: Indexes and Fragmentation in SQL Server 2000 Part 1

Billy Eastwood — Mon, 23 Oct 2006 07:28:00 GMT

How this relates to the real world is this...

1) No clustered Index = A Heap

2) Add some Non-clustered indexes to a table and you still have a heap

3) Add more data with the non-clustered indexes and your heaped table will GROW by leaps and bounds !!

4) You want to make the table smaller BUT a heap cannot be defragged! (nothing works, DBCC indexdefrag etc..)

5) You MUST create a clustered index to force defragmentation

Moral - NEVER create non-clustered indexes without creating a clustered index first!

I have come across this knowledge through evaluation of a legacy database which is massive for apparently no reason. I could not shrink its size no matter what I did. Only after much reading did I come across the "Heap Gem" of knowledge and was able to cut the DB down to 40% of its original size by adding, yes adding, clustered indexes to the top 30 largest tables. There were hundreds of tables, dunno what would have happened by adding a CI to all appropriate tables.

- B

RE: Indexes and Fragmentation in SQL Server 2000 Part 1

Ravi Prashanth Lobo-275382 — Mon, 23 Oct 2006 06:58:00 GMT

Good article, To the point. Cleared the basics! Keep up the good work.

RE: Indexes and Fragmentation in SQL Server 2000 Part 1

Cory E. — Mon, 23 Oct 2006 06:45:00 GMT

Very nice - I have learned a lot, cannot wait till the next article.

RE: Indexes and Fragmentation in SQL Server 2000 Part 1

PaulSp — Mon, 23 Oct 2006 04:16:00 GMT

Hi Joe

I really enjoyed this article. I am not a SQL expert in any way, and found the step by easy step approach excellent. I really feel I have learned something and time spent here was very worthwhile. I look forward to the next article - particularly with a view towards indexes.

Many thanks for the time you spent on this.

Paul

RE: Indexes and Fragmentation in SQL Server 2000 Part 1

MikeTomkies — Mon, 23 Oct 2006 03:27:00 GMT

Thanks for the article cant wait for the next instalment. One thing that has confused me over the years is how sql stores and organises text data. You do not include this type of data field in your table,is their a reason for this? if you can just explain about text data as i find you explinations so far very easy to understand.

RE: Indexes and Fragmentation in SQL Server 2000 Part 1

Koby BILLER — Mon, 23 Oct 2006 02:23:00 GMT

Joe,Thank you for the excellant article.When I wrote a year ago my article about fragmentation, this is exactly what I had in mind - but since I am not an SQL expert, I could not do it myself.Please read my article at http://www.sqlservercentral.com/columnists/kbiller/performancemonitoringbyinternalfragmentationmeasur.aspand look at www.disklace.com where I present the fragmentation measurement model. This model is working for more than 100,000 users, and proves to be accurate. If you want to join forces and give the audience of this forum a solution - I am ready.Koby BillerKoby@disklace.com

Indexes and Fragmentation in SQL Server 2000 Part 1

Joe Doherty-260822 — Mon, 16 Oct 2006 09:35:00 GMT

Comments posted here are about the content posted at temp