Tips for DBA: Using sys.dm_db_index_physical_stats in a script to rebuild or reorganize indexes (no partitions / SQL Server 2005)
В электронной документации к SQL Server 2005 описано замечательное динамическое административное представление 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] |