asked
Index fragmentation level
What would be the fragmentation level for Rebuilding or Re-indexing an Index ?
To make this question and these answers as helpful for others as possible, remember to please indicate every helpful answer by clicking on the thumbs up next to it. If any one answer helped to solve your problem, indicate that by clicking on the check mark next to it.
Best Practice and general rule of thumb is 30%. Keep in mind that may need to be adjusted depending on the size of the database and your maintenance window time frame. SQLFool (Michelle Ufford) has an awesome script you might want to look at that can help with that tasking.
http://sqlfool.com/2011/06/index-defrag-script-v4-1/
Agreed. However, seeing as you've mentioned SQLFool, I will also mention Ola Hallengren's set of scripts.
http://ola.hallengren.com. These two scripts helped when I was having to write similar for SQL 2000 (yes, I work on elderly systems).
for a roll-your-own version, books online has a good starting point. Its very basic but is essentially what the others have in their core.
avg_fragmentation_in_percent value ALTER INDEX REORGANIZE > 5% and 30%
I'm going to buck the trend and suggest you completely toss reorganize and only rebuild the index based on a combination of the fragmentation and it's size. The standard rule of thumb for size is 1000 pages, but I would generally go a little lower, between 200 & 500 pages. But, for indexes less than about 10,000 or so pages, I wouldn't defrag until it got about 60% or so. The number of pages is just too small to sweat. After that, I'd probably go with the standard 30% everyone else is suggesting.
An opportunity for the standard answer of "it depends" wasted... Shame on you, @Grant, shame on you. But yeah - absolutely top advice there.
Ha! You're right. I totally blew that opportunity. What was I thinking?
others have given you thresholds pertaining to the fragmentation and I wouldnt argue against any of them. What you do need to is to control when you do the defrag (to refer to rebuild or reorganise). if you blindly see an index has reached 30% (or whatever you choose as your 'tipping point') and instantly defrag it you will potentially bring your system to its knees until the defrag is complete. Schedule you scans for fragmentation for out of hours so that the defrag can take care of your indexes when other users are not pounding the data.
Good point. The bit I found helpful was to put in a cut-off point - in other words, don't blindly do the whole lot, but just get it to do an hour or so a night. That way, the indexes will get sorted out, but activity will be limited to out-of-hours / low-use hours.
18 People are following this question.
