Устранение фрагментации индексов

блог о bi, №1 в рунете

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

В нашей практике столкнулись со случаем, когда на клиентской базе средняя фрагментация индексов превышала 50%, что больше критического значение в 30%. Такая высокая фрагментация значительно увеличивает время чтения данных из таблицы. В этой статье расскажем о том, что такое фрагментация, чем она опасна и способы ее устранения.

Появление фрагментации

Файл базы, в котором хранятся данные, логически поделен на равные части по 8 Кб. Эти части называют страницами.
При попытке вставить или обновить данные, что приведет к переполнению страницы, SQL Server разбивает ее примерно поровну.
После этой операции данные хранятся на 2-ух полупустых страницах. Разбиение приводит к тому, что логически расположенные рядом страницы физически могут располагаться на значительном расстоянии друг от друга.

На рисунке 1 изображена попытка вставить новое значение “3” в заполненную страницу. Так как места на странице нет, создается новая, на которую переносится половина данных (“4” и “5”) с первой, в результате получается две частично заполненные страницы.

Рисунок 1. Разбиение страницы при вставке

Внешняя фрагментация

Созданная страница находится в произвольном месте на диске, поэтому данные физически не расположены последовательно. Такое непоследовательное расположение страниц называется внешней фрагментацией.

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

Фрагментация индекса приводит к увеличению времени на поиск и чтение данных. Если данных хранятся на 200 страниц, но в сумме занимают только 100, чтение будет производится приблизительно в 2 раза медленнее. Поэтому важно следить за фрагментацией и устранять ее.

Информацию о фрагментации индексов можно получить из табличной функции sys.dm_db_index_physical_stats. Ей можно указать id базы данных, id таблицы, id индекса. Следующий запрос находит в текущей базе данных индексы, процент фрагментации которых больше 5.

select 
	 s.name as schema_name
	,t.name as table_name
	,i.name as index_name
	,c.avg_fragmentation_in_percent
from sys.dm_db_index_physical_stats(db_id(), null, null, null, 'LIMITED') as c
inner join sys.tables as t
	on c.object_id = t.object_id
inner join sys.schemas as s
	on t.schema_id = s.schema_id
inner join sys.indexes as i
	on c.index_id = i.index_id
where c.avg_fragmentation_in_percent > 5
order by c.avg_fragmentation_in_percent desc;

В своей практике мы считаем, что индекс имеет высокую фрагментацию, если она составляет более 5%. Если значение фрагментации превышает 30%, индекс имеет критический уровень фрагментации.

Внутренняя фрагментация

Коэффициент заполнения определяет загруженность страниц при создании индекса. Со значением коэффициента 20 страницы будут изначально заполнены на 20%. Это уменьшит количество операций деления страниц и внешнюю фрагментацию, но увеличит время чтения в 5 раз и внутреннюю фрагментацию. По умолчанию коэффициент равен 0, что означает полное заполнение страниц при создании индекса.

В большинстве случаев выигрыш от уменьшения количеств операций деления страниц нивелируется увеличением времени чтения, поэтому рекомендуется оставить значение коэффициента равным 0 или 100.

Изменить значение можно в настройках индекса, как показано на рисунке 2.

Рисунок 2. Изменение коэффициента заполнения страницы

Исправление фрагментации

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

ALTER INDEX { index_name | ALL } ON <object> REBUILD

Запрос пересоздает индексы таблицы с названием <object>. Можно пересоздать конкретный индекс, указав его имя index_name, или все индексы таблицы, указав ALL.

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

ALTER INDEX { index_name | ALL } ON <object> REORGANIZE

Также можно реорганизовать все или конкретный индекс таблицы <object>.

Итог

Увеличение фрагментации индекса избежать невозможно. Поэтому важно постоянно мониторить этот процесс. Рационально будет автоматизировать обновление индексов. В наших проектах как лучшая практика используется проверка фрагментации и перестроение индексов на регламентной основе (ежедневно). Это позволяет добиться сокращения занимаемого индексами пространства в 2 раза и увеличить скорость чтения данных в 1,5 - 2,5 раза для таблиц, фрагментация индексов которых была критическая.

ВЕРНУТЬСЯ К БЛОГУ