liveBook · Manning

Index reorganization

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

Index reorganization is an online index-maintenance operation that compacts and orders existing index pages without creating a replacement index structure.

Index reorganization is a lower-cost maintenance operation that condenses leaf-level pages toward FILLFACTOR and rearranges them without creating a replacement B-tree. It can address internal and external fragmentation, but it cannot lower already-full pages to the configured FILLFACTOR. section 11.3

Mistake: reorganizing a low-FILLFACTOR index

Suppose an index intentionally uses a 60% FILLFACTOR. Reorganizing it after observing low external fragmentation leaves pages full rather than restoring 60% density. Inserts can then cause page splits and quickly recreate external fragmentation, forcing a rebuild soon afterward. section 11.3

Use these rules when choosing index maintenance:

  • Measure both avg_fragmentation_in_percent and avg_page_space_used_in_percent with sys.dm_db_index_physical_stats; focus on leaf-level data rather than aggregated fragmentation. section 11.3
  • Choose a rebuild when page density must be reduced or fragmentation requires more complete repair; do not rebuild every index indiscriminately. section 11.12 section 11.3
  • Interpret fragmentation operationally: external fragmentation harms scans, while seeks are not impacted. section 11.12
Table 11.1 sys.dm_db_index_physical_stats parameters (view table figure)

Parameter

Description

database_id

The database ID of the database for which we wish to return results. NULL returns details for all databases.

object_id

The object ID of the table for which we wish to return results. NULL will return information for all tables.

index_id

The index ID for which we wish to return results. NULL will return results for all indexes.

partition_number

The partition number for which we wish to return results. NULL will return information for all partitions.

mode

Can be specified as LIMITED, SAMPLED, or DETAILED. LIMITED will only scan the root and intermediate levels of the B-tree. SAMPLED will scan 1% of the pages within a table* and DETAILED will scan every page of the index.

* If the table has fewer than 10,000 pages, then DETAILED mode will be used.

Inspect the physical-stats parameters to choose database, object, index, partition, and scan scope before diagnosing fragmentation. section 11.3

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

How deep it goes
Explains page-density and fragmentation mechanics, diagnosis, consequences, and stopping limits without prescribing a universal threshold.
Examples
Uses a 60% FILLFACTOR scenario and sys.dm_db_index_physical_stats measurements to demonstrate an ineffective reorganization.
What it assumes
Assumes familiarity with indexes, B-trees, FILLFACTOR, scans, seeks, and SQL Server dynamic management functions.
What you can do after
Diagnose fragmentation with physical-statistics metrics and choose reorganization or rebuilding without relying on fragmentation alone.
How it connects
Connects FILLFACTOR, page splits, external fragmentation, internal fragmentation, index rebuilding, scans, seeks, and physical-statistics analysis.
Cover of 100 SQL Server Mistakes and How to Avoid Them
liveBookmanning.com
Read 100 SQL Server Mistakes and How to Avoid Them on liveBook Get the book at manning.com

Related concepts

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.