Monitoring SQL Server Index Fragmentation

Another important task of database administrators is to monitor existing index health and identify where new indexes are needed. Every time data is inserted, updated, or deleted in SQL Server tables, indexes are accordingly updated.

Why Monitor

Over time, the distribution of data in data pages can become unbalanced. Some data pages become loosely filled, whereas others are filled to the maximum. Too many loosely filled data pages create performance issues as more data pages need to be read to retrieve the requested data.

On the other hand, pages filled close to their maximum may create page splits when new data is inserted or updated. When page splits occur, about half of the data is moved to a newly created data page. This constant reorganization consumes resources and creates data page fragmentation.

The goal is to store as much data into the smallest number of data pages with room for growth to prevent excessive page splits. This delicate balance can be achieved by fine-tuning the index fill factor. For more information on fine-tuning the index fill factor, refer to Books Online at http://msdn.microsoft.com/en-us/library/ms177459(v=SQL.110).aspx.

Monitoring Index Fragmentation

You can monitor index fragmentation through the provided Data Management Views (DMVs) available in SQL Server 2012. One of the most useful DMVs is sys.dm_db_index_physical_stats, which provides average fragmentation information for each index.

For example, you can query the sys.dm_db_index_physical_stats DMV as follows:

SELECT index_id,avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats
(
DB_ID(‘AdventureWorks’),
OBJECT_ID(‘AdventureWorks’),
NULL, NULL, ‘DETAILED’
)

Figure 1 below shows the results of this query.

Figure 1. Example Query Results for Indexes with High Fragmentation

From execution results of this DMV, you can observe indexes with high fragmentation. Indexes with high defragmentation percentages need to be defragmented to avoid performance issues. Heavily fragmented indexes are stored and accessed inefficiently by SQL Server depending on the type of fragmentation, internal or external. External fragmentation means that data pages are not stored in logical order. Internal fragmentation means that pages store much less data then they can hold. Both types of fragmentation cause query execution to take longer.