2

In the Books Online for Reorganize and Rebuild Indexes Microsoft mentions:

Rebuilding or reorganizing small rowstore indexes often does not reduce fragmentation.

What magnitude of number of rows or number of data pages is considered around the cutoff of where it's worth considering index reorganize or index rebuild operations?

Improve this question
3
  • 1
    I don't have an official source for this, but it was demonstrated at PASS Summit by the SQL CAT in 2013. The guy showed me that if the index had less than 1000 pages, "most of the time", rebuilding the index would not reduce the fragmentation of the index.
    – SQL_Hacker
    Jan 31, 2020 at 20:58
  • 1
    I kept looking and found on Ola's site that it is considered a "Microsoft recommendation". ola.hallengren.com/… About 1/3 of the way down there's a "Min Pages" section that shows this.
    – SQL_Hacker
    Jan 31, 2020 at 21:12
  • 2
    @SQL_Hacker - the "Microsoft recommendation" should be taken with a pinch of salt. sqlskills.com/blogs/paul/…
    – Martin Smith
    Jan 31, 2020 at 22:40

1 Answer 1

Reset to default
7

Also "reducing fragmentation" is not per se a performance goal. On many (most?) modern storage platforms there is little difference between sequential and random IO, which is a major historical reason for defragmenting.

I've worked on systems where the difference in throughput between sequential and random IO was 10x or more. As SQL Server attempts to scan a fragmented index, the physical file locations of the linked-list of pages jumps around every few extents, reducing the IO size, randomizing the IO, preventing read-ahead IO, and eliminating the benefit of speculative reads into the SAN controller cache.

On modern systems, the storage either has lots of spindles, or is flash-based. Both of these reduce the performance differential between small, random IOs and large, sequential IOs.

Also if tables are heavily cached, the benefit of defragmenting their storage on disk diminishes. And small rowstore tables will typically be cached.

Improve this answer
2
  • I don't disagree but this is an inherited process I have to support for the moment until I can transition into partitioning these tables. I've always been under the impression that defraging is a thing of the passed but I have seen (temporary) performance improvements from it. But it certainly seems like the work is not worth the reward.
    – J.D.
    Feb 1, 2020 at 3:24
  • Perhaps those improvements cane from the updated statistics instead of the reorg? Not uncommon. FWIW I've tried to measure cost of fragmentation, but I mostly learned how difficult it is to measure things. :-) Here's is my final blog on the topic: sqlblog.karaszi.com/fragmentation-the-final-installment
    – Tibor Karaszi
    Feb 3, 2020 at 10:41

Your Answer

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

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