Internal index fragmentation occurs when index pages are not being used to their maximum volume.
Row fragmentation can occur when rows are deleted. If you delete an entire page of rows, that page is freed, but if some rows on a page are unused, unused space remains on the disk.
DML operations (INSERT, UPDATE, DELETE) that act on tables cause index fragmentation. Two stored procedures report fragmentation:
sp_iqrowdensity reports row fragmentation at the default index level. See “sp_iqrowdensity procedure,” in Chapter 7, “System Procedures,” in Reference: Building Blocks, Tables, and Procedures.
sp_iqindexfragmentation reports internal fragmentation within supplemental indexes. See “sp_iqindexfragmentation procedure,” in Chapter 7, “System Procedures,” in Reference: Building Blocks, Tables, and Procedures.
The database administrator may create other indexes to supplement the default index on a column. These indexes can use more space than needed when rows are deleted from a table.
Neither procedure recommends further action. The database administrator must examine the information reported and determine whether to take further action, such as recreating, reorganizing, or rebuilding indexes.
