Фрагментация индексов

Индексы являются важнейшим компонентом, обеспечивающим оптимальное выполнение запросов. Однако в отличие от других объектов SQL Server 2005 индексы могут со временем потерять свою эффективность при неправильном обслуживании, так как индексным страницам свойственна фрагментация. Оптимизатор может отказаться от использования индексов, достигших высокого уровня фрагментации, что ухудшает производительность запросов.

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

Фрагментация индекса может происходить при изменении данных в таблице. Какая бы операция над данными таблицы ни осуществлялась — INSERT, DELETE или UPDATE, она также влияет на соответствующие индексы.

Выполнение инструкции DELETE часто вызывает освобождение дискового пространства, что может привести к тому, что индексные страницы будут содержать лишь фрагменты. Состояние, при котором страницы заполнены не полностью, называют внутренней фрагментацией (internal fragmentation).

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

Операции INSERT и UPDATE могут приводить к созданию SQL-сервером дополнительных индексных страниц, если страница, в которую нужно поместить строку индекса или данных, уже содержит максимально возможное количество информации.

Если SQL Server записывает дополнительные строки данных на индексную страницу, которая не может их вместить, происходит разбиение страницы — добавляется новая страница, и SQL Server делит строки индексной информации между исходной и вновь созданной страницами. Разбиение страниц поддерживает логический порядок строк в ключе индекса; SQL Server знает, на какой странице находится следующий ключ индекса. Но разбиение страниц не поддерживает физический порядок страниц — новая страница обычно не следует на диске за исходной. Если страницы физически не упорядочены, это называют внешней фрагментацией (external fragmentation).

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

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

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

Для определения внешней и внутренней фрагментации индексов, содержащихся в базе данных, в SQL Server 2005 имеется функция динамического управления sys.dm_db_index_physical_stats. Используя эту функцию в простой инструкции SELECT, можно проверить значения в столбцах avg_fragmentation_in_percent (степень внешней фрагментации) и avg_page_space_used_in_percent (степень внутренней фрагментации), как показано на примере запроса, и определить, фрагментированы ли индексы:

-- Определение фрагментации индексов для всех таблиц базы данных Adventureworks

DECLARE @db_id int

SET @db_id = DB_ID()

SELECT OBJECT_NAME(dt.object_id), si.name,

dt.avg_fragmentation_in_percent, dt.avg_page_space_used_in_percent

FROM

(SELECT object_id, index_id, avg_fragmentation_in_percent,

avg_page_space_used_in_percent

FROM sys.dm_db_index_physical_stats (@db_id, NULL, NULL, NULL, 'DETAILED')

WHERE index_id <> 0) as dt -- не возвращает информацию о кучах.

INNER JOIN sys.indexes si

ON si.object_id = dt.object_id AND si.index_id = dt.index_id

При обнаружении внешней или внутренней фрагментации индекса периодически выполняйте реорганизацию (ALTER INDEX...REORGANIZE) или перестройку (ALTER INDEX...REBUILD), чтобы фрагментация индекса не влияла на производительность запросов.

Инструкция ALTER INDEX...REORGANIZE реорганизует индекс. Эта инструкция реорганизует страницы в соответствии с логическим, слева направо, порядком, уплотняя страницы индексов. Уровень уплотнения определяется существующим коэффициентом заполнения индекса.

USE AdventureWorks;

ALTER INDEX PK_Employee_EmployeeID ON HumanResources.Employee REORGANIZE;

С помощью инструкции ALTER INDEX...REBUILD можно перестроить индекс. Эта инструкция устраняет как внешнюю, так и внутреннюю фрагментацию путем удаления и воссоздания индекса. Внешняя фрагментация устраняется путем переупорядочивания строк индекса на соседних страницах, а внутренняя — путем уплотнения страниц в соответствии с заданным или существующим коэффициентом заполнения.

USE AdventureWorks;

ALTER INDEX PK_Employee_EmployeeID ON HumanResources.Employee REBUILD;

При перестроении индекса можно указать параметр ONLINE (доступен только в версии Enterprise). Если для него установлено значение ON, таблица и связанные с ней индексы доступны для запросов и модификации данных во время процесса перестроения.

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

avg_fragmentation_in_percent (степень внешней фрагментации)

до 5 (10, по другим источникам) – дефрагментация не нужна

от 5 (10) до 30(15) – реорганизация

больше 30 (15) – перестройка

avg_page_space_used_in_percent (степень внутренней фрагментации)

больше 75 – дефрагментация не нужна

от 60 до 75 – реорганизация

меньше 60 – перестройка

Более подробно о реорганизация и перестроении индексов можно узнать здесь

http://msdn.microsoft.com/ru-ru/library/ms189858.aspx

sys.dm_db_index_physical_stats здесь

http://msdn.microsoft.com/ru-ru/library/ms188917.aspx

Разработан документ "Дефрагментация индексов" (скрипт документа Defrag_indexes.sql -http://www.ecosoft.ufanet.ru/download/lexema/Scripts/UpdateLexema55%20(170-)%20for%20Lexema%205.5/Defrag_indexes.sql), который в удобной форме показывает степень дефрагментации индексов в базе данных, рекомендует действия по дефрагментации, позволяет выполнять рекомендации или сохранять их в файл скрипта.

Материалы

1. Microsoft SQL SERVER 2005. Реализация и обслуживание. Учебный курс Microsoft

2. Электронная документация по SQL Server 2005