I've googled this and I know that some people say index fragmentation doesn't matter, but they go on to describe scenarios where it might matter. Mine might be such a scenario.

I have an index on a huge table (86 million rows) that was about 33% fragmented.

A few days ago I started noticing certain queries were taking too long. It seemed like they weren't using one of the indexes even though the queries are written in such a way that they should use the index (I could be wrong about the index not being used)

So one of the things I did was look at the index fragmentation. It was 33% I did a rebuild at a time when nothing would be using the table (except that as a test I did run a query on the table at the same time as rebuilding the index - Foolish perhaps).

The rebuild took approximately 10 to 20 minutes. For some reason I didn't check the fragmentation immediately after. I checked it the day after (queries still slow) and saw that it had increased to 56.06%!

Screenshot of fragmentation information

If I try another rebuild will I make it even worse? Should I try a reorganize instead? (I read that Microsoft recommends a reorganize if fragmentation is less than 30% and a rebuild if more than 30%)

Disclaimer: I am aware that there may be some bad practices going on here (size of table, design, or anything) I didn't create this, I merely inherited responsibility for it.

Edit: I risked another rebuild. This time checked immediately. It was 0.01 fragmented. I checked again ten or so minutes later and it was about 1% fragmented. I checked again approximately one minute intervals and it's becoming gradually more fragmented. Some hours later it's now 53.55% fragmented.

Output of @@version:

Microsoft SQL Server 2005 - 9.00.3042.00 (X64)
Feb 10 2007 00:59:02
Copyright (c) 1988-2005 Microsoft Corporation
Standard Edition (64-bit) on Windows NT 6.0 (Build 6001: Service Pack 1)

Autoshrink is on (shows 'true' in the database properties dialog box)

Edit : Some further information to give extra detail to the question....

At a fixed time each day the table gets a bulk update of approximately 50,000 to 100,000 rows of new data. For the rest of the day there are no updates or new records, it's purely queried for data.

The primary key is on 'recordid' and it is an auto increment field. It's value is higher for new inserts than anything else in the table so there's no having to sandwich the data between existing data in the index.

Here is the create index code for the index in question...


/****** Object:  Index [IX_AmtoteAccountActivity]    Script Date: 03/03/2016 21:07:14 ******/
CREATE CLUSTERED INDEX [IX_AmtoteAccountActivity] ON [dbo].[AmtoteAccountActivity] 
    [AccountNumber] ASC,
    [_Date] ASC

In case it matters/is relevant (I'm not sure if it is) here is the create index code for the primary key...


/****** Object:  Index [PK_AmtoteAccountActivity]  Script Date: 03/03/2016 21:10:11 ******/
ALTER TABLE [dbo].[AmtoteAccountActivity] ADD  CONSTRAINT [PK_AmtoteAccountActivity] 
    [RecordID] ASC

Finally, as suggested by @Shanky and @KookieMonster I believe the fragmentation (which steadily grows after rebuild) is caused by auto_shrink being on for this database.

  • 1
    You should seriously consider upgrade to SQL Server 2005 Sp4 and SP4 CU2 at least
    – Shanky
    Mar 3, 2016 at 10:51
  • 2
    Do you have auto shrink enabled for databases ?
    – Shanky
    Mar 3, 2016 at 11:16
  • 1
    In the properties dialog it shows auto shrink is on (true)
    – MrVimes
    Mar 3, 2016 at 11:25
  • 4
    By all means disable this. Do the REBUILD again and see again in a few days. I'm pretty confident it won't reach the same levels as before. Mar 3, 2016 at 11:50
  • 1
    @KookieMonster I will think about disabling it, and I want to. But this is a long-standing database server with limited file space, and it may soon be migrated (the data) to a mysql server. If it's auto-shrink then it's been that way for years and years. But thankyou for the reply. I believe you are right about the fragmentation being related to the auto shrink.
    – MrVimes
    Mar 3, 2016 at 21:00

4 Answers 4


Just like @KookieMonster noticed, You have Auto Shrink turned on. And one of disadvantages of shrink commands is fragmenting Your indexes once again:



The Page Fullness is 98.23%. This means there is only 140-ish byte free per page. This is larger than the minimum row size. So any insert in the middle of the key range, and likely any update too, will cause a page split and fragmentation.

You'd need to affect about one million rows to get 30% fragmentation since there are about three million page in the index. Do you have processes which write at this magnitude?

  • There's typically about 60-100 thousand rows inserted in bulk/one go daily but no updates or inserts throughout the day. There's a bulk insert somewhere in the region of 10 minutes, and then another one about 10 minutes later, again taking about 10 minutes. So 20 minutes of insert activity per day.
    – MrVimes
    Mar 3, 2016 at 11:07
  • And the key values for these new rows - are they all greater than those currently in the table before the insert starts? Could it be that any of the new rows has a key value that sorts between two pre-existing key values? Mar 3, 2016 at 11:15
  • The primary key is an auto increment key, and I'm fairly certain it will be larger for the inserted data than any other record in the table. One of the indexed columns is a date stored as varchar, yy/mm/dd (I know, bad design) and this is newer than what's already in the database in terms of sorting. (the order yy mm dd means that when sorting as a string it is still sorted correctly) there is also an index on account numbers. They will be all over the place (i.e. not higher value than what's in the table already)
    – MrVimes
    Mar 3, 2016 at 11:22
  • 1
    @MrVimes So which index is the one that's getting fragmented? It doesn't matter how dates-as-strings or identity columns are sorted if the leading key in the index is something else... also, how did you jump to the conclusion that fragmentation must be the cause for a slow query? What other investigation have you done, such as waits involved when the query is running, digging into the query plan, etc.? Seems knee-jerky to blame fragmentation and spend this much time on it when I don't see any obvious correlation. Mar 3, 2016 at 11:34
  • I'm not jumping to conclusions. I'm considering it as one of many possibilities. I'm going to investigate the slow queries/index in another question (or just google hopefully). What's happening here is that I saw the fragmentation was very high and got bogged down trying to solve it, even if it doesn't help my query speed.
    – MrVimes
    Mar 3, 2016 at 11:46

If recordID is your PK and it is not fragmenting then you should flop and make recordID the clustered index.

With IX_AmtoteAccountActivity as a non clustered index give it a fill factor of less then 100%. This will also speed up inserts. Since it is a big table don't go crazy bet even 90% will slow down fragmentation. And just schedule a rebuild of that index every night.


You should set FILLFACTOR=90 or less, when rebuilding your indexes. Default it's 0 which means 100%.

Transaction (INSERT,UPDATE & DELETE) on a tables with 100% FILLFACTOR cause to page split which finally cause to index fragmentation.

There are many reasons which leads you to fragmentation. I can details you all, so please go through given link it's described here.



Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.