TSQL To Find Fragmented Indexes

SQL Fragmentation is one of those things that is easy to visualize with Database Health Monitor.

Prior to the Database Health Monitor with the Fragmented Indexes Report, I used the following query to track down fragmented indexes.

SELECT ps.avg_fragmentation_in_percent, name
  FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS ps
 INNER JOIN sys.indexes AS idxs
    ON ps.OBJECT_ID = idxs.OBJECT_ID
   AND ps.index_id = idxs.index_id

But now it is so much easier with the Database Health Monitor with the Fragmented Indexes Report.

Understanding Index Fragmentation

Index Fragmentation occurs when the data in your database becomes scattered across the physical storage medium, making it inefficient for the database engine to retrieve information. This happens over time as data is inserted, updated, or deleted, leaving gaps in data pages or causing pages to be split. Fragmentation can lead to performance degradation, as SQL Server has to work harder to read and write data. For large-scale databases or systems with frequent data changes, understanding and addressing fragmentation is essential for maintaining optimal performance.

Types of Fragmentation in SQL Server

There are two main types of SQL Server fragmentation: internal fragmentation and external fragmentation. Internal fragmentation happens when there’s unused space within a data page due to inefficient storage of rows. This can occur when rows are deleted or updated to a smaller size, leaving gaps that reduce storage efficiency. External fragmentation, on the other hand, refers to data pages being stored out of order on disk, which can lead to longer disk I/O times as SQL Server reads or writes data across non-contiguous pages. Both types of fragmentation can negatively impact performance, but they require different approaches to resolve.

How to Identify Index Fragmentation

SQL Server provides tools to identify fragmentation levels in your database. The sys.dm_db_index_physical_stats dynamic management function is commonly used to analyze the fragmentation of indexes. This function provides details such as the average fragmentation percentage, the number of pages in the index, and the type of index. A fragmentation level above 30% is generally considered high and requires immediate attention, while anything below 10% may not be a significant concern. Regularly checking for fragmentation can help you plan maintenance tasks and ensure your database stays healthy.

Another way is with the Index Fragmention report in Database Health Monitor.

Resolving SQL Server Fragmentation

To resolve fragmentation, SQL Server offers several options depending on the severity and type of fragmentation. Reorganizing an index is a lightweight operation that reduces fragmentation by defragmenting the leaf level of the index, making it more efficient without consuming significant system resources. For higher levels of fragmentation, rebuilding an index is a more effective option. This process recreates the entire index and eliminates fragmentation completely, though it requires more system resources and may lead to downtime in non-enterprise editions of SQL Server. Choosing the right approach depends on the impact of fragmentation on performance and the available maintenance window.

Preventing Fragmentation in SQL Server

While it’s impossible to eliminate fragmentation entirely in most systems, you can minimize its impact with proactive maintenance and database design. Scheduling regular index maintenance, such as reorganizing or rebuilding indexes during off-peak hours, can keep fragmentation under control. Additionally, carefully choosing appropriate fill factors for indexes can reduce the likelihood of page splits by leaving some free space on each data page. Monitoring fragmentation levels as part of routine database health checks ensures that you can address issues before they significantly impact performance. By staying proactive, you can maintain a smooth-running SQL Server environment and avoid costly performance degradation.

-Steve Stedman / Stedman Solutions, LLC