How does SQL Server reduce index fragmentation?

Question

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?

Answer 1

No, there is no auto-magical defragging of indexes. If you have fragmentation, you need to REBUILD or REORGANIZE.

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.

Answer 2

If the user never run REBUILD or REORGANIZE on the database, would the SqlServer engine defragment the indexes?

No.

Would running REORGANIZE multiple times do the same things as REBUILD

Also No.

A REORGANIZE reorders the leaf pages (the pages that hold the actual index data) to be in the correct order per the index specifications. Pages can also be consolidated and empty pages dropped, but no new pages will be allocated and the btree structure and stats will not be rebuilt.

A REBUILD drops the old index and makes it from scratch. This updates all b-tree structures, removes unneeded slack space, and puts fragmentation at 0 (or as close to 0 as the continuity of free pages in the database allows).

Answer 3

No SQL Server doesn't do any kind of automatic maintenance.

Running REORGANIZE multiple times will have no effect after the first successful run (ignoring pages modified since being reorganized or those it could not lock and so skipped on the first attempt).

It swaps out of order pages into the correct order so once it has done that and they are all in the correct order additional runs will have no effect. It cannot remove fragmentation that arises due to non contiguous allocations. For that you need to rebuild.

Answer 4

I'd just like to add some points to the existing answers:

• Books Online has a page regarding Reorganizing and Rebuilding Indexes
• The thresholds in the Books Online were made up by Paul Randal
• Rebuilding an index updates the statistics with the equivalent of a full scan
• REBUILD can be multi-threaded, while REORGANIZE is always single-threaded
• REBUILD can use minimal logging (if it's not an ONLINE REBUILD), while REORGANIZE is always fully logged
• Ola Hallengren provides popular scripts for index maintenance