Правильное применение индексов позволяет во многих случаях существенно сократить продолжительность выполнения SELECT-запроса. Однако следует понимать, что эффективность SELECT-запросов достигается снижением эффективности выполнения других DML-операторов. Ясно, что, например, при добавлении строки в таблицу, имеющую некластеризованные индексы, автоматически выполняется изменение в индексах. Аналогично обновляются индексы при выполнении операторов UPDATE и DELETE. Наиболее трудоемкими при этом являются операции, обеспечивающие сбалансированность древовидной структуры индекса.

Чтобы повысить эффективность выполнения операций изменения таблиц БД, алгоритмы изменения индексов реализованы таким образом, чтобы отложить наиболее трудоемкую их часть на более позднее время. Например, удаление строки в таблице БД приводит к необходимости удалить соответствующие записи во всех индексах данной таблицы. Удаление записей индекса не выполняется физически, а данные страницы индекса при этом не уплотняются с целью ликвидации «дырки», образовавшейся после удаления этой записи – запись просто помечается специальным маркером. Аналогично операции добавления и изменения строк БД могут повлечь образование неиспользуемых фрагментов в области памяти индекса. Поэтому индексы таблиц, часто подвергающихся операциям изменения, становятся пористыми, или еще говорят – фрагментированными. Процесс образования неиспользуемых фрагментов памяти называют фрагментацией. Фрагментация индексов снижает эффект от их применения и в некоторых случаях может привести к значительным потерям производительности сервера БД.

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

Рис. 11.44. Получение информации о степени фрагментации индекса
 # EXPLORE_ TKEY

В сценарии на рис. 11.45 в таблицу # EXPLORE добавляется 10 000 строк и снова получается информация о степени фрагментации индекса. Заметьте, что уровень фрагментации превысил 99%.

Рис. 11.45. Фрагментация индекса после добавления 10 000 строк

От фрагментации индекса можно избавиться с помощью простого пересоздания индекса (рис. 11.46), но таким способом пользуются редко.

Рис. 11.46. Избавление от фрагментации простым пересозданием индекса

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

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

Реорганизация (REORGANIZE) выполняется значительно быстрее, но после этой операции фрагментация будет убрана только из листовых узлов индексного дерева (на самом нижнем уровне). Поэтому после реорганизации может оставаться некоторая степень фрагментации индекса. Операцию реорганизации индексов рекомендуется выполнять при уровне фрагментации 10–20%. Реорганизация индекса всегда выполняется в режиме ONLINE.

Операция перестройки (REBUILD) затрагивает все узлы дерева, поэтому после ее выполнения степень фрагментации равна нулю. Перестройку индексов рекомендуется выполнять при уровне фрагментации, превышающем 20%. По умолчанию операция перестройки индекса выполняется в режиме OFFLINE и полностью блокирует индексируемую таблицу для доступа других запросов, но с помощью специального параметра может быть установлен режим ONLINE. Исключением являются индексы локальных временных таблиц, их перестройка всегда выполняется в режиме OFFLINE.

На рис. 11.47 приведен пример выполнения оператора ALTER для реорганизации индекса # EXPLORE_ TKEY. Заметьте, что после реорганизации небольшой уровень фрагментации сохранился.

Рис. 11.47. Демонстрация выполнения реорганизации индекса
 # EXPLORE_ TKEY

На рис. 11.48 приведен пример выполнения оператора ALTER для перестройки индекса # EXPLORE_ TKEY в режиме OFFLINE.

Рис. 11.48. Демонстрация выполнения перестройки индекса # EXPLORE_ TKEY

Уровнем фрагментации можно в некоторой степени управлять, если при создании или изменении (реорганизации и перестройке) индекса использовать параметры FILLFACTOR и PAD_INDEX.

Параметр FILLFACTOR указывает процент заполнения индексных страниц нижнего уровня. Например, если установлено значение 80, то это означает, что на листовых страницах дерева индекса будет зарезервировано 20% свободной памяти для будущих операций. По умолчанию (значение может быть установлено помощью системной процедуры SP_CONFIGURE) обычно значение этого параметра устанавливается равным 100, т. е. индексные страницы заполняются полностью. Следует иметь в виду, что значение 0 тоже приводит к полному заполнению страницы (трактуется как 100). Отличное от 0 и 100 значение параметра FILLFACTOR позволяет уменьшить фрагментацию, возникающую из-за расщепления полностью заполненных индексных страниц, происходящее при добавлении и корректировке индекса. Но следует помнить, что чем больше зарезервировано на страницах места для будущих операций изменения, тем больше становится страниц индекса, и это снижает эффективность его применения. Считается, что если установить значение FILLFACTOR, равное 50, то это приведет к снижению скорости выполнения операций, базирующихся на применении индекса, примерно в 2 раза. Выбор значения зависит от интенсивности обновления индекса, количества строк в индексируемой таблице, а также от возможности выполнять операции реорганизации и перестройки.

Параметр PAD_INDEX распространяет действие параметра FILLFACTOR на страницы промежуточного и корневого уровня.

На рис. 11.49 демонстрируется фрагментация индекса # EXPLORE_ TKEY после добавления в таблицу # EXPLORE строк. Заметим, что значение параметра FILLFACTOR индекса равно 100.

Рис. 11.49. Пример, демонстрирующий процесс фрагментации индекса
 # EXPLORE_ TKEY при значении параметра FILLFACTOR, равном 100

В сценарии на рис. 11.50 пересоздается индекс # EXPLORE_ TKEY. При этом указывается значение параметра FILLFACTOR, равное 65. После добавления строк в таблицу # EXPLORE уровень фрагментации вырос только до 40%, что ниже, чем в примере на рис. 11.49

Рис. 11.50. Пример, демонстрирующий процесс фрагментации индекса
 # EXPLORE_ TKEY при значении параметра FILLFACTOR, равном 65

Для более подробного знакомства с параметрами индексов рекомендуется литература [3, 4, 5].