Index and Row Fragmentation

Internal index fragmentation occurs when index pages are not being used to their maximum volume. Row fragmentation occurs when rows are deleted. Deleting an entire page of rows frees the page, but if some rows on a page are unused, the unused space remains on the disk. DML operations (INSERT, UPDATE, DELETE) on tables can cause index fragmentation.

Run these stored procedures for information about fragmentation issues:

sp_iqrowdensity reports row fragmentation at the FP index level.
sp_iqindexfragmentation reports internal fragmentation within supplemental indexes. .

Review the output and decide whether you want to recreate, reorganize, or rebuild the indexes. You can create other indexes to supplement the FP index.

Additional Information

Reference: Building Blocks, Tables, and Procedures > System Procedures > sp_iqrowdensity procedure.
Reference: Building Blocks, Tables, and Procedures > System Procedures > sp_iqindexfragmentation procedure.