Should We Defragment Indexes?
summary
Argues that defragmenting indexes for external fragmentation can hurt throughput on VM/hypervisor storage that uses an SSD cache fronting capacity devices because a rebuild or reorg can push all index pages onto a single capacity device and controller, reducing parallel I/O; demonstrates with a card game split across 4 controllers where distributed pages are faster to read. Explains rebuild vs reorg (rebuild creates a new index, reorg fixes order), notes internal fragmentation still matters for page count and optimizer decisions, and urges checking storage topology and benchmarking.
AI-written summary · about
chapters (8)
- 0:00Introduction to maintenance
- 1:43Internal vs external fragmentation
- 3:32Reorgs vs rebuilds
- 5:26Impact on storage architecture
- 8:56The controller analogy
- 9:44Conclusion of main talk
- 10:47Q&A session
- 19:04Closing remarks
related talks
19mShould We Defragment Indexes?- 19mShould We Defragment Indexes?
53mIndexing Internals for Developers & DBAs- 53mIndexing Internals for Developers & DBAs
53mOvercome Blast Radius Anxiety With Ultrafast Rebuilds and Performance for High-capacity SSDs
14mRe-think Data Management Software Design Upon the Arrival of Storage Hardware with
53mSQL Server Internals: The Practical Angle
27mPreview: Storage