Index fragmentation

As explained in 100 SQL Server Mistakes and How to Avoid Them

External fragmentation is the degree to which an index's pages are stored out of logical sequence rather than physical order.

External index fragmentation occurs when an index’s pages are physically out of logical sequence. A page split in the middle of an index can create this disorder: SQL Server allocates a new page, moves rows, and updates page pointers. The result adds I/O and can degrade performance. section 9.2 section 11.1

Figure 9.2 Bad page split

Compare a middle-of-index page split with an end-of-index allocation: the former leaves the new page out of order and creates external fragmentation. section 9.2

Do not treat fragmentation as the first explanation for every slow query. Physical page order matters to an index scan, which reads leaf-level pages in sequence; an index seek traverses the B-tree to find a row and is not affected by external fragmentation. Queries returning a scalar or one row are therefore unlikely to benefit from an emergency rebuild. section 11.12 section 11.2

Diagnose before scheduling maintenance:

• Measure external fragmentation with avg_fragmentation_in_percent and page density with avg_page_space_used_in_percent from sys.dm_db_index_physical_stats. section 11.3
• Check seek, scan, and lookup usage through sys.dm_db_index_usage_stats; prioritize fragmentation where scan activity makes it relevant. section 11.12 section 11.2
• Inspect leaf-level data rather than aggregating fragmentation statistics across index levels. section 11.12

After shrinking a data file

Purging old archive data and shrinking its data files may reclaim space but can fragment indexes, causing slower user queries after maintenance. Take a fragmentation baseline, shrink only when necessary, then rebuild affected indexes. Do not rely on autogrow to manage database size. section 9.2 section 9.12

Rebuilding creates a new B-tree and nearly removes fragmentation, but costs more resources. Reorganizing has lower cost and attempts to reorder leaf pages, yet it can leave pages full; subsequent inserts may quickly cause more bad page splits. Select rebuilds from measured need instead of rebuilding every index indiscriminately. section 11.3 section 11.12

How 100 SQL Server Mistakes and How to Avoid Them covers it

How deep it goes

Explains page splits, scan and seek effects, diagnostic DMFs and DMVs, and rebuild-versus-reorganize tradeoffs.

Examples

Uses a full LastName nonclustered-index page, an archive-data purge and file shrink, and SQL Server fragmentation and usage queries.

What it assumes

Introduces B-tree pages and page splits while assuming practical SQL Server index-maintenance responsibilities.

What you can do after

Diagnose/debug with it by measuring leaf-level fragmentation and usage before selectively rebuilding indexes.

How it connects

Connects external fragmentation to page splits, internal fragmentation, page density, FILLFACTOR, index scans, index seeks, and index maintenance.

liveBookmanning.com

Read 100 SQL Server Mistakes and How to Avoid Them on liveBook Get the book at manning.com

Related concepts

• sys.dm_db_index_physical_statsmeasured by · sys.dm_db_index_physical_stats is a SQL Server dynamic management function that reports physical storage and fragmentation information for indexes.
• bad page splitproduced by · A bad page split is a page split in the middle of an index that relocates data to a newly allocated page outside the existing physical order.
• database shrink operationproduced by · A database shrink operation reduces the allocated storage footprint of database files by releasing unused space.
• index scancontrasts with · An index scan is a database query operation that traverses an index to locate or retrieve records matching query conditions.

This explainer was generated by Manning's knowledge graph from the text of 100 SQL Server Mistakes and How to Avoid Them. The section links point to the parts of the book it draws on.