Index Fragmentation: Internals, Analysis, and Solutions
Why Database Administrators Stack Exchange recommends it
Database Administrators Stack Exchange users link it when answering questions about index maintenance plans, rebuild thresholds, and fill factor, treating Randal's talk as the definitive explanation of fragmentation.
Recommended by 3 people across 3 different months, 2014 to 2016.
What it is
A 75-minute PASS conference session by Paul Randal on how SQL Server index fragmentation works, how to measure it, and what to do about it. It covers the internals of page splits and fragmentation types, analysis techniques, and maintenance strategies.
What people said
Strong rec DenisT · 2014-07 · Database Administrators Stack Exchange · on “Index Maintenance Plan”
You can watch this 1 hour video by Paul Randal -- Index Fragmentation: Internals, Analysis, and Solutions [this video] ! There is no better explanation than this.
Recommends Paul White · 2015-01 · Database Administrators Stack Exchange · on “Why not rebuild indexes with page count <1000?”
The guidance concerning the minimum number of pages is somewhat arbitrary [LINK] . The biggest benefits of reducing fragmentation are: It can improve read-ahead performance for large range scans; and It may improve the page density (number of rows per page) Both of these factors are less important for small indexes, by definition. The counter-argument to rebuilding small indexes is essentially: "Why bother? Don't you have more important things to worry about?". That said, rebuilding/reorganizing is not free. It may be worth avoiding the extra effort and log generation in some cases (for example, if the log is shipped/copied across a WAN for any number of possible reasons - mirroring, availab
Recommends Martin Smith · 2016-04 · Database Administrators Stack Exchange · on “Best fill factor for GUID clustering key”
Have a look at Paul Randal's fragmentation video on PassTV: SESSION: Index Fragmentation: Internals, Analysis, and Solutions [this video]