Избыточное и недостаточное индексирование — насколько всё плохо на самом деле?

Автор: Paul Randal, Over and under indexing – how bad is it out there?

Как-то я запустил опрос, в котором предложил выполнить код для получения сводного списка количества таблиц на вашем сервере с различным числом некластерных индексов. Я получил результаты с более чем 1000 серверов по всему миру — огромное спасибо всем, кто прислал мне данные!

Победители:

• Наибольшее количество некластерных индексов на одном кластерном индексе: 1032
• Наибольшее количество некластерных индексов на одной куче: 148
• Наибольшее количество кластерных индексов с нулевым количеством некластерных индексов на одном сервере: 185237
• Наибольшее количество куч с нулевым количеством некластерных индексов на одном сервере: 88042

Вау!

Теперь перейдём к некоторым деталям…

Таблицы с нулевым количеством некластерных индексов

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

Все запросы, у которых предикат поиска не совпадает с ключом кластеризации (или его левосторонним подмножеством), будут выполнять сканирование таблицы, что может создавать нагрузку на буферный пул (см. мой пост о Page Life Expectancy) и приводить к конкуренции за защёлку ACCESS_METHODS_DATASET_PARENT (проявляющуюся как высокий процент ожиданий LATCH_EX, PAGEIOLATCH_SH и, возможно, CXPACKET).

Для куч все запросы представляют собой неэффективные сканирования таблиц. Ну, эффективные, если вы каждый раз возвращаете все строки таблицы, полагаю :-)

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

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

1. Использовать DMV пропущенных индексов (осторожно!), чтобы определить, какие некластерные индексы создать. Я использую скрипт, опубликованный сотрудником Microsoft Бартом Дунканом (Bart Duncan). Однако не создавайте все индексы подряд. Обычно я ищу, чтобы столбец improvement_measure Барта был выше 100 000, прежде чем рекомендовать индекс клиенту (а в системах, где на таблице уже есть некластерные индексы, я ищу возможности для их объединения). Обратите также внимание, что DMV пропущенных индексов иногда добавляют ключ кластеризации как включённый столбец (INCLUDE). Это излишне, но безвредно.
2. Посмотреть непосредственно в кэш планов, чтобы найти планы запросов, выполняющие сканирования. Используя графический план запроса, я могу увидеть, какие столбцы ищутся и возвращаются при сканированиях, а затем создать для них правильные некластерные индексы.

Вы также можете использовать помощник по настройке базы данных (Database Tuning Advisor), но лично я его не использую.

Вы будете удивлены разницей в производительности, которую даёт хороший набор некластерных индексов.

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

Таблицы с некластерными индексами

Данные опроса показывают, что наиболее распространено наличие 10 или менее некластерных индексов, но даже это может быть слишком много.

Каждый некластерный индекс создаёт накладные расходы при вставке или удалении строки таблицы, а также при обновлении любых ключевых столбцов некластерного индекса (или включённых столбцов). Отфильтрованные индексы в SQL 2008+ являются особым случаем, очевидно. Накладные расходы принимают несколько форм:

• Накладные расходы на буферный пул (т.е. память и ввод-вывод) из-за необходимости поиска записи в некластерном индексе для обновления.
• Накладные расходы на ввод-вывод для сброса обновлённой страницы индекса на диск во время следующей контрольной точки.
• Пространство в журнале для записей журнала, генерируемых операцией над некластерным индексом.
• Накладные расходы на ресурсы для этих записей журнала в виде:
  • Времени на чтение агентом чтения журнала репликации/CDC.
  • Времени на чтение при резервном копировании журнала (и резервном копировании данных, если применимо).
  • Времени и пропускной способности для отправки записей журнала зеркальному серверу базы данных.
  • Дискового пространства для хранения записей журнала в резервной копии журнала.
  • Времени на восстановление записей журнала на вторичном сервере доставки журналов или при аварийном восстановлении.
• Накладные расходы на блокировки.
• Накладные расходы на разбиения страниц.
• Время на проверку согласованности.
• Время на проверку фрагментации.
• Время на обновление статистики.
• Накладные расходы на дисковое пространство и резервное копирование.

Как видите, некластерные индексы могут быть серьёзным бременем для системы — при их создании нужно быть осторожным, чтобы не иметь их слишком много.

Есть три вещи, которые вы можете сделать, чтобы уменьшить количество некластерных индексов в вашей системе:

1. Использовать DMV sys.dm_db_index_usage_stats, чтобы найти индексы, которые только обновляются. Опять же, будьте осторожны. Только потому, что индекс не использовался, не означает, что его следует удалить. Он может использоваться нечасто, но быть критически важным, когда используется. В идеале нужно смотреть на вывод этого DMV после того, как пройдёт полный бизнес-цикл. Даже тогда будьте осторожны с удалением индексов, которые обеспечивают ограничения уникальности, так как они могут использоваться оптимизатором запросов без отражения каких-либо поисков или сканирований.
2. Удалить дублирующиеся некластерные индексы. Кимберли опубликовала код для поиска дублирующихся индексов здесь. В этом нет никаких минусов.
3. Искать возможности для объединения. Это сложнее и больше искусство, чем наука. Вы ищете индексы, которые можно объединить в один, не влияя на способность оптимизатора использовать их для различных запросов, для которых помогали необъединённые индексы.

Например, индекс на c1, c2, c3 INCLUDE c4, c5 можно объединить с индексом на c1, c2, c3 INCLUDE c4, c6. Но только при условии, что c6 не является очень широким столбцом, который повлияет на производительность запросов, использующих первый индекс.

Более сложный пример: стали бы вы объединять индекс c1, c2 INCLUDE c3 с индексом на c1, c3 INCLUDE c2? Возможно. Это зависело бы от того, для чего эти индексы используются в запросах.

Резюме

Некластерные индексы необходимы для производительности большинства рабочих нагрузок, но сколько их должно быть? Я часто слышу на занятиях, которые проводит Кимберли по индексам, как кто-то спрашивает её: «Каково оптимальное количество индексов для таблицы?» — потому что я знаю, что это бессмысленный вопрос. (А она, в свою очередь, просит кого-нибудь спросить меня: «Сколько времени займёт CHECKDB?» :-)

Ответ — это большое, жирное «зависит от ситуации» — и, надеюсь, я дал вам несколько советов, как разобраться в этом самостоятельно.