7

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?

Improve this question

4 Answers 4

Reset to default
11

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.

Improve this answer
6

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).

Improve this answer
5

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.

Improve this answer
4

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

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.