Monitoring and Reducing Fragmentation

Although indexes can speed up the execution of queries, some overhead is associated with them. Indexes consume extra disk space and involve additional time to update themselves any time data is updated, deleted, or inserted in a table.

When indexes are first built, little or no fragmentation should be present. Over time, as data is inserted, updated, and deleted, fragmentation levels on the underlying indexes may begin increase.

When a page of data is completely full and further data must be added to it, a page split occurs. To make room for the new arriving 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 nonlinear. Therefore, when searching for data, SQL Server has to jump from one page to somewhere else in the database looking for the next page it needs instead of going right from one page to the next. This results in performance degradation and inefficient space utilization.

Monitoring Fragmentation

The fragmentation level of an index is the percentage of blocks that are logically linear and physically nonlinear. In SQL Server 2008 R2, SQL Server 2008, or SQL Server 2005, administrators can use the sys.dm_db_index_physical_stats dynamic management function and keep an eye on the avg_fragmentation_in_percent column to monitor and measure the fragmentation level. The value for avg_fragmentation_in_percent should be as close to zero as possible for maximum performance. However, values from 0 percent through 10 percent may be acceptable.

Reducing Fragmentation

In the previous version of SharePoint, it was recommended to track and reduce the fragmentation level by running the database statistics timer job, which in turn updates the query optimization statistics and rebuilds all indexes in the content databases every time it runs. Another option was reorganizing or rebuilding the indexes on a regular basis using the SQL Server 2008 or SQL Server 2005 Maintenance Wizard.

In SharePoint 2010, administrators no longer need to worry about fragmentation because SharePoint can do that on their behalf via the health analyzer. The health analyzer performs “health checks” based on timer jobs and self-heals the database index fragmentation automatically.