Significant level of index fragmentation

In BOL, it talks about ignoring index fragmentation below 5%, but out 'in the real world', what sort of levels of index fragmentation should be considered to be significant and hence worth worrying about. Do you find that you need to have things in place to actively re-organise indexes in some of your tables, or is that usually a sign that something else is wrong?

more ▼

asked Oct 16, 2009 at 12:03 PM in Default

avatar image

Melvyn Harbour 1 ♦♦
1.4k 19 41 26

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

Tony Davis discusses this in depth at http://www.simple-talk.com/community/blogs/tony_davis/archive/2009/10/01/75072.aspx .

But the bottom line is the standard answer of "It Depends". If you really want a rule of thumb that does not take much thought, then I think around 10% is the traditional answer and as close to correct as you are likely to find. If you truly want to optimize though, you need to look at your specific situation. If you have a huge amount of maintenance time when there is low-to-no other server activity, then rebuild/reorganize aggressively to minimize the fragmentation. If maintenance time is at a premium then you may want to allow relatively high (30% or even higher) before even dreaming of taking major action.

And if you have a write-heavy database, ensure you give it an adequate fill factor, otherwise write performance will be affected massively.

more ▼

answered Oct 16, 2009 at 12:40 PM

avatar image

15.6k 22 57 38

(comments are locked)
10|1200 characters needed characters left

Ken Simmons covered this in his SQL Saturday presentation on automating routine maintenance. In his slide deck he showed that if fragmentation is > 5% and < = 30% then REORGANIZE. If it's > 30% REBUILD

You can see whole slide deck + his scripts here:


*note: when you download the file, rename the file and add .zip to end of it. For some reason download stripped data type. The zip file contains his powerpoint and sql scripts. Any questions about anything in his deck contact Ken at http://cybersql.blogspot.com

more ▼

answered Oct 19, 2009 at 08:40 AM

avatar image

Jorge Segarra
419 2 4

(comments are locked)
10|1200 characters needed characters left

The only thing i'd add to the above is that the enterprise editions of 2005 and 2008 support online indexing, which means you can re-index during periods of low activity without blocking access to the table that the index references...

more ▼

answered Oct 16, 2009 at 12:45 PM

avatar image

Matt Whitfield ♦♦
29.5k 62 66 88

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Oct 16, 2009 at 12:03 PM

Seen: 1975 times

Last Updated: Oct 16, 2009 at 12:03 PM

Copyright 2018 Redgate Software. Privacy Policy