What is the best practice for measuring SQLServer database fragmentation/performance rolloff and determining when to defrag a SQLServer database table?
I'm most interested in learning what the useful metrics are and what level of performance degradation should trigger the defrag.
I'm sure there will be some interesting answers to this, as there is a lot of disagreement on what metrics to look at. I wrote DBCC INDEXDEFRAG, SHOWCONTIG and designed their replacements for 2005, plus wrote the Books Online content, so I'll give you my view, and explain the numbers in Books Online and the maintenance plan wizard for 2005, which I chose.
The two best metrics to look at for index fragmentation are: 1) (2005) average fragmentation in percent / (2000) logical scan fragmentation 2) (2005) average page density / (2000) average bytes free per page
These apply equally to clustered and nonclustered indexes.
1 is measuring how much logical fragmentation there is. This is when the logical order of pages at the leaf level of an index does not match the physical order. This prevents the Storage Engine from doing efficient readahead during range scans. So #1 affects range scan performance, not singleton lookup performance.
2 is measuring how much wasted space there is on each page at the leaf level of an index. Wasted space means that you're using more pages to store the records, which means more disk space to store the index, more IOs to read the index, and more memory to hold the pages in memory in the buffer pool.
Thresholds? My general rule of thumb is less than 10% fragmentation, do nothing. 10-30%, do an ALTER INDEX ... REORGANIZE (2005) / DBCC INDEXDEFRAG (2000). More than 30%, do an ALTER INDEX ... REBUILD (2005) / DBCC DBREINDEX (2000). These are complete generalizations and the thresholds for you will vary.
To find your thresholds, do some tracking of workload performance against fragmentation levels and decide when the performance degradation is too much. At that point you'll need at address the fragmentation. There's a balancing act between living with fragmentation and taking the resource hit of removing it.
I haven't touched here on the trade-offs between the two methods of removing fragmentation, things like FILLFACTOR/PADINDEX to try to mitigate fragmentation and do less defragging, changes to schema/access patterns to alleviate fragmentation, or different kinds of maintenance plans.
Oh, btw, I always recommend not bothering about fragmentation in indexes with less than 1000 pages. This is because the index is probably mostly memory resident (and because people asked for a number and I had to come up with one).
You can read more on this in my TechNet Magazine article on database maintenance at http://technet.microsoft.com/en-us/magazine/cc671165.aspx, in the 2000-based whitepaper on index defrag best practices I helped write at http://technet.microsoft.com/en-us/library/cc966523.aspx, and on my blog under the Fragmentation category at http://www.sqlskills.com/BLOGS/PAUL/category/Fragmentation.aspx.
I kind of over-answered this I think, but it's one of my hot-buttons. Hope this helps :-)
