Overview
Explore the intricacies of index fragmentation in SQL Server databases through this comprehensive conference talk from the PASS Data Community Summit. Delve into the internal workings, analysis techniques, and effective solutions presented by expert Paul Randal. Gain a deep understanding of index structure, singleton lookups, and range scans, including the concept of readahead. Learn about logical fragmentation, its impact on buffer pool usage, and the mechanics of page splits. Discover real-world examples of fragmentation causes and how to track them. Master the configuration of FILLFACTOR and the use of sys.dm_db_index_physical_stats for fragmentation analysis. Conclude with practical knowledge on correcting fragmentation using the ALTER INDEX ... REBUILD command, equipping you with valuable skills to optimize database performance.
Syllabus
Intro
The SQLskills Team
Overview
Index Structure
Singleton Lookup
Range Scan
Range-Scan: Readahead (1)
Logical Fragmentation Defined
Low Buffer Pool Usage
What is a Page Split?
Updates During a Page Split
Page Split Mechanism
Tracking Page Splits
What Causes Fragmentation?
Real-World Examples
Configuring FILLFACTOR
sys.dm_db_index_physical stats a
How to Correct Fragmentation?
ALTER INDEX ... REBUILD
Taught by
PASS Data Community Summit