Microsoft SharePoint 2010 Unleashed

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 ...