x

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

Melvyn Harbour 1 gravatar image

Melvyn Harbour 1 ♦♦
1.4k 18 20 22

(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

TimothyAWiseman gravatar image

TimothyAWiseman
15.5k 20 23 32

(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:

http://www.sqlsaturday.com/downloadsessionfile.aspx?downloadfilename=Automating%20Routine%20Maintenance.zip&presentationid=642

*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

Jorge Segarra gravatar image

Jorge Segarra
419 2

(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

Matt Whitfield gravatar image

Matt Whitfield ♦♦
29.4k 61 65 87

Yes, a good point to add. But also remember that even with those the performance implications are large during the re-indexing.
Oct 16, 2009 at 05:10 PM TimothyAWiseman
Yep, you wouldn't want to try it when the server was maxed out! :)
Oct 16, 2009 at 05:15 PM Matt Whitfield ♦♦
(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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x131
x20
x4

asked: Oct 16, 2009 at 12:03 PM

Seen: 1752 times

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