План обслуживания "на каждый день" - Часть 1: Автоматическая дефрагментация индексов

Ошибочно рассматривать базу данных как некую эталонную единицу, поскольку, с течением времени, могут проявляться различного рода нежелательные ситуации - деградация производительности, сбои в работе и прочее.

Для минимизации возникновения таких ситуаций создают планы обслуживания из задач, гарантирующих стабильность и оптимальную производительность базы данных.

Среди подобных задач можно выделить следующие:

1. Дефрагментация индексов
2. Обновление статистики
3. Проверка целостности и резервное копирование

Рассмотрим по порядку автоматизацию каждой из этих задач.

Итак, пункт первый…

Помимо фрагментации файловой системы и лог-файла, ощутимое влияние на производительность базы данных оказывает фрагментация внутри файлов данных:

1. Фрагментация внутри отдельных страниц индекса

После операций вставки, обновления и удаления записей неизбежно возникают пустые пространства на страницах. Ничего страшного в этом нет, поскольку данная ситуация вполне нормальная, если бы не одно но…

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

Бороться с данным видом фрагментации стоит на этапе проектировании схемы, т. е. выбирать такие типы данных, которые бы компактно умещались на страницах.

2. Фрагментация внутри структур индекса

Основная причина возникновения этого вида фрагментации - операции разбиения страницы. Например, согласно структуре первичного ключа, новую строку необходимо вставить на определенную страницу индекса, но этой на странице недостаточно места, чтобы разместить вставляемые данные.

В таком случае, создается новая страница, на которую переместиться примерно половина записей со старой страницы. Новая страница, зачастую не является физически смежной со старой и, следовательно, помечается системой как фрагментированная.

В любом случае, фрагментация ведет к росту числа страниц для хранения того же объема информации. Это автоматически приводит к увеличению размера базы данных и росту неиспользуемого места.

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

Для предотвращения фрагментации в арсенале SQL Server-а предусмотрены команды по реорганизации и перестройке индексов.

Перестройка индекса подразумевает удаление старого и создание нового экземпляра индекса, в котором данные на страницах аккуратно сжаты и организованы максимально непрерывно, насколько это вообще возможно. Важно отметить, что операция по перестройке индекса весьма затратна.

Поэтому, в случае, когда фрагментация незначительна, предпочтительно реорганизовывать существующий индекс. Данная операция требует меньших системных ресурсов, чем пересоздание индекса и заключается в реорганизации leaf-level страниц. Кроме того реорганизация при возможности сжимает страницы индексов.

Степень фрагментации того или иного индекса можно узнать из динамического системного представления 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
В данном запросе, последний параметр задает режим, от значения которого возможно быстрое, но не совсем точное определения уровня фрагментации индекса (режимы LIMITED/NULL). Поэтому рекомендуется задавать режимы SAMPLED/DETAILED.

Мы знаем откуда получить список фрагментированных индексов. Теперь необходимо для каждого из них сгенерировать соответствующую ALTER INDEX команду. Традиционно для этого используют курсор:

DECLARE @SQL NVARCHAR(MAX) DECLARE cur CURSOR LOCAL READ_ONLY FORWARD_ONLY FOR 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, 'DETAILED') 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] OPEN cur FETCH NEXT FROM cur INTO @SQL WHILE @@FETCH_STATUS = 0 BEGIN EXEC sys.sp_executesql @SQL FETCH NEXT FROM cur INTO @SQL END CLOSE cur DEALLOCATE cur
Чтобы ускорить процесс пересоздания индекса рекомендуется дополнительно указывать опции SORT_IN_TEMPDB и ONLINE.

Первая позволяет перестраивать индексы в базе tempdb, что бывает особенно полезно для больших индексов в случае нехватки памяти и ином случае - опция игнорируется. Кроме того, если база tempdb расположена на другом диске - это существенно сократит время создания индекса. Вторая опция позволяет пересоздать индекс не блокируя при этом запросы к объекту для которого этот индекс создается.

Как показала практика, дефрагментирование индексов с низкой степенью фрагментации либо с небольшим количеством страниц не приносит каких-либо заметных улучшений, способствующих повышению производительности при работе с ними.

В дополнении, приведенный выше запрос можно переписать без применения курсора:

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
В результате оба запроса при выполнении будут генерировать запросы по дефрагментации проблемных индексов:

ALTER INDEX [IX_TransactionHistory_ProductID] ON [Production].[TransactionHistory] REORGANIZE; ALTER INDEX [IX_TransactionHistory_ReferenceOrderID_ReferenceOrderLineID] ON [Production].[TransactionHistory] REBUILD WITH (SORT_IN_TEMPDB = ON, ONLINE = ON); ALTER INDEX [IX_TransactionHistoryArchive_ProductID] ON [Production].[TransactionHistoryArchive] REORGANIZE;
Собственно, на этом первая часть по созданию плана обслуживания для базы данных выполнена. В следующей части мы займемся написанием запроса для автоматического обновления статистики.