Tips for DBA: Using sys.dm_db_index_physical_stats in a script to rebuild or reorganize indexes

В электронной документации к SQL Server описано замечательное динамическое административное представление sys.dm_db_index_physical_stats. Описание сопровождается примерами использования, один из которых (в русской редакции страницы это пример "Г", а в английской "D") предлагает метод и правила автоматизации операций дефрагментации индексов в базе данных. Суть метода в том, что если значение avg_fragmentation_in_percent находиться в диапазоне от 10 до 30, то в инструкции ALTER INDEX используется ключевое слово REORGANIZE, а если значение больше 30, то используется ключевое слово REBUILD. Однако, существует и другой алгоритм выбора метода дефрагментации, который подробно изложен в книге: " Microsoft SQL Server 2005. Реализация и обслуживание. Учебный курс Microsoft". Вот выдержка из этой книги, со страницы 368: "Исполняйте инструкцию ALTER INDEX … REORGANIZE, чтобы выполнить дефрагментацию индекса, при соблюдении следующих пороговых значений: значение avg_page_space_used_in_percent в диапазоне от 60 до 75 или значение avg_fragmentation_in_percent в диапазоне от 10 до 15. Исполняйте инструкцию ALTER INDEX … REBUILD, чтобы выполнить дефрагментацию индекса, при соблюдении следующих пороговых значений: значение avg_page_space_used_in_percent меньше 60 или значение avg_fragmentation_in_percent больше 15.".

Ниже представлен сценарий, демонстрирующий в упрощённом виде этот алгоритм. В этом сценарии кластеризованные и не кластеризованные индексы анализируются отдельно, если для таблицы необходима дефрагментация кластеризованного индекса, её некластеризованные индексы отбрасываются. Кроме того, не анализируются маленькие индексы, занимающие меньше дюжины страниц.

SELECT 'ALTER INDEX ALL ON [' + OBJECT_NAME(afp.OBJECT_ID) + '] REBUILD WITH (SORT_IN_TEMPDB = ON, ONLINE = ON);' AS [Инструкция T-SQL]

FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, 'SAMPLED') AS afp

WHERE afp.database_id = DB_ID()

AND afp.index_type_desc IN ('CLUSTERED INDEX')

AND (afp.avg_fragmentation_in_percent >= 15 OR afp.avg_page_space_used_in_percent <= 60)

AND afp.page_count > 12

UNION ALL

SELECT [Инструкция T-SQL] =  

            CASE   

            WHEN afp.avg_fragmentation_in_percent >= 15 OR afp.avg_page_space_used_in_percent <= 60  

            THEN 'ALTER INDEX [' + i.name + '] ON [' + OBJECT_NAME(afp.OBJECT_ID) + '] REBUILD WITH (SORT_IN_TEMPDB = ON, ONLINE = ON);'   

            WHEN (afp.avg_fragmentation_in_percent < 15 AND afp.avg_fragmentation_in_percent >= 10) OR (afp.avg_page_space_used_in_percent > 60 AND afp.avg_page_space_used_in_percent < 75) 

            THEN 'ALTER INDEX [' + i.name + '] ON [' + OBJECT_NAME(afp.OBJECT_ID) + '] REORGANIZE;' 

            END

FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, 'SAMPLED') AS afp

JOIN sys.indexes AS i ON (afp.OBJECT_ID = i.OBJECT_ID AND afp.index_id = i.index_id)

                                AND afp.database_id = DB_ID()

                                AND afp.index_type_desc IN ('NONCLUSTERED INDEX')

                                AND (  

                                     (afp.avg_fragmentation_in_percent >= 10 AND afp.avg_fragmentation_in_percent < 15)

                                      OR (afp.avg_page_space_used_in_percent > 60 AND afp.avg_page_space_used_in_percent < 75) 

                                     )

                                AND afp.page_count > 12

                                AND afp.OBJECT_ID NOT IN (      

                                                          SELECT OBJECT_ID      

                                                                       FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, 'SAMPLED')      

                                                                       WHERE database_id = DB_ID()     

                                                                       AND index_type_desc IN ('CLUSTERED INDEX')     

                                                                       AND (avg_fragmentation_in_percent >= 15 OR avg_page_space_used_in_percent < 60)     

                                                                       AND page_count > 1    

                                                                    )

ORDER BY [Инструкция T-SQL]