Monitoring and Reducing Fragmentation

Although indexes can speed up the execution of queries, there is also some overhead associated with them. Indexes consume extra disk space and require some time to be updated whenever any data is updated, deleted, or inserted in a table. When indexes are first built, little or no fragmentation is present. Over time, as data is inserted, updated, and deleted, fragmentation levels on the underlying indexes can begin to increase.

When a data page is completely full and further data must be added to it, a page split occurs. To make room for the new data, SQL Server creates another data page somewhere else in the database (not necessarily in a contiguous location) and moves some of the data from the full page to the newly created one.

The effect of this is that the blocks of data are logically linear but physically fragmented. Therefore, when searching for data, the database engine is forced to jump from one page to somewhere else in the database looking for the next page it needs, instead of just sequentially moving to the next physical page. The end result is performance degradation and inefficient space utilization.

The fragmentation level of an index is the percentage of blocks that are logically linear and physically nonlinear. In SQL Server versions starting with SQL Server 2005, the sys.dm_db_index_physical_stats transact-SQL command can be used to monitor this, with the avg_fragmentation_in_percent column showing the fragmentation level. The value for avg_fragmentation_in_percent should be as close to zero as possible for maximum performance. However, up to 10% might be acceptable without noticeable degradation.