If a user never runs REBUILD or REORGANIZE on their database, does SQL Server still somehow defragment the indexes?
MSDN suggests that if an index is over 30% fragmented, it is recommended to run REBUILD instead of REORGANIZE. Would running REORGANIZE multiple times do the same things as REBUILD?
I wonder about this because I have a client that has a highly fragmented index. They run REORGANIZE against that index every weekend, and over time it seems like their index becomes defragmented.
Does this make sense?
Best Answer
No, there is no auto-magical defragging of indexes. If you have fragmentation, you need to
REBUILDorREORGANIZE.Reorganizing an index defragments the leaf level of an index by physically re-ordering pages to match the logical order. Lock durations are short and will cause minimal blocking of queries.
Rebuild drops an index and builds a new one. With Enterprise edition this can be done as an online operation if the index doesn't include any LOB types. With Standard edition or where LOB types exist, it will cause blocking. See How Online Index Operations Work for an explanation of how online rebuilds can occur concurrently with user operations.
The Reorganize vs Rebuild recommendation is roughly the threshold at which the amount of work required to defragment by reorganizing is comparable with a rebuild i.e. at >30% fragmentation it will require more resources and greater elapsed time to complete a reorganize than a rebuild.
Multiple reorganize runs would not further defragment the index.