Measuring SQL Server Fragmentation

Before we can address fragmentation in your database, we have to understand what causes fragmentation, how you can measure fragmentation, and how your can address fragmentation. After database record insert, update and delete operations there will be empty spaces on pages, the basic storage object. There is nothing wrong empty spaces, since the situation is regular enough, but there is a slight problem with empty spaces. Your database size can be far larger than required, and with additional pages to store data also comes the potential for your queries to take longer as the database engine must access additional pages to collect your data.

To prevent fragmentation, SQL Server provides commands to reorganize and rebuild indexes. Rebuilding an index involves deletion of the old one and creation of a new instance of the index, in which data on pages is neatly compressed and organized as continuously as possible. It is important to note that the index rebuilding operation is rather costly and should be avoided when possible. When fragmentation is insignificant, it is preferable to reorganize the existing index. The reorganizing operation requires less system resources than rebuilding indexes. In addition, reorganization compresses index pages to help save disk space.

The index fragmentation level can be learned from a dynamic system view – sys.dm_db_index_physical_stats:

SELECT * 
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL)
WHERE avg_fragmentation_in_percent > 0

You can change the “avg_fragmentation_in_percent” value to match the value at which you want to take action, which depends on your environment and database. You might want to only address those indexes that are more than 20 or 30 percent fragmented. Most evaluations will show that defragmentation of indexes with a low level of fragmentation or with a small number of pages does not bring any major improvements, so it probably won’t be worth the effort when working with these types of fragmentation issues.

This script will help identify those indexes with fragmentation and it will generate a script you can execute to resolve the fragmentation issue if the fragmentation (avg_fragmentation_in_percent) is above 30%:

DECLARE @IsDetailedScan BIT
SELECT @IsDetailedScan = 1

DECLARE @SQL NVARCHAR(MAX)
SELECT @SQL = (SELECT 'ALTER INDEX [' + i.name + N'] ON [' 
               + SCHEMA_NAME(o.[schema_id]) + '].[' + o.name + '] ' 
               + CASE WHEN s.avg_fragmentation_in_percent > 30
               THEN 'REBUILD WITH (SORT_IN_TEMPDB = ON'
               -- Enterprise, Developer
                  + CASE WHEN SERVERPROPERTY('EditionID') IN (1804890536, -2117995310) 
                    THEN ', ONLINE = ON'
                    ELSE ''
                    END + ')'
               ELSE 'REORGANIZE'
               END + ';
               '
               FROM (SELECT
                 s.[object_id]
                 , s.index_id
                 , avg_fragmentation_in_percent = MAX(s.avg_fragmentation_in_percent)
                 FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 
                      CASE WHEN @IsDetailedScan = 1 
                      THEN 'DETAILED'
                      ELSE 'LIMITED'
                      END) s
                 WHERE s.page_count > 128 -- > 1 MB
                       AND s.index_id > 0 -- <> HEAP
                       AND s.avg_fragmentation_in_percent > 5
                 GROUP BY s.[object_id], s.index_id
               ) s
               JOIN sys.indexes i WITH(NOLOCK) ON s.[object_id] = i.[object_id] 
                    AND s.index_id = i.index_id
               JOIN sys.objects o WITH(NOLOCK) ON o.[object_id] = s.[object_id]
               FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)')
               OPTION (OPTIMIZE FOR (@IsDetailedScan = 1))
--PRINT @SQL
EXEC sys.sp_executesql @SQL

 If you want to “automate” the process, you can build your own scripts, or download this script of a process that has already been created and tested by Ola Hallengren.

Advertisements