Tips for DBA: Missing indexes recommendations (SQL Server 2005)

Вы, наверное, уже заметили, какую полезную информацию об индексах и о статистике использования существующих и, как это не удивительно, не существующих индексов можно получить в SQL Server 2005 из системных динамических административных представлений. Особенно впечатляет эта информация, когда её видишь в виде пользовательского отчёта приборной панели производительности SQL Server, которую в качестве бесплатного дополнения к SQL Server Management Studio 2005 предлагает использовать Майкрософт.
Ниже представлен сценарий, который позволяет получить рекомендации по индексам, которых в обозримом прошлом недоставало базе данных, в контексте которой этот сценарий исполняется. Учтите, что сценарий в целях наглядности был упрощён, поэтому результирующие команды по созданию индексов, наверняка, будут содержать ошибки, т.к. тут не учитывается возможность отсутствия не относящихся к предикатам эквивалентности колонок, а также колонок, которые в индексе будут присоединёнными. Впрочем, эти ошибки легко устранимы, просто поищите лишние запятые перед запуском сценария, а также не забудьте удалить пустые инструкции INCLUDE.

    -- Не забудьте поправить сценарий создания индексов, 
-- убрав лишние запятые или пустую инструкцию INCLUDE
SELECT 	[Рекомендуемый индекс]= 
		'-- CREATE INDEX [IX_' + OBJECT_NAME(mid.object_id) + '_' + 
		CAST(mid.index_handle AS nvarchar) + '] ON ' + 
		mid.statement + ' (' + ISNULL(mid.equality_columns,'') + 
		', ' + ISNULL(mid.inequality_columns,'') + 
		') INCLUDE (' + ISNULL(mid.included_columns,'') + ');', 
	[Число компиляций] = migs.unique_compiles,
	[Количество операций поиска] = migs.user_seeks,
	[Количество операций просмотра] = migs.user_scans,
	[Средняя стоимость ] = CAST(migs.avg_total_user_cost AS int),
	[Средний процент выигрыша] = CAST(migs.avg_user_impact AS int)
FROM	sys.dm_db_missing_index_groups mig
JOIN	sys.dm_db_missing_index_group_stats migs 
ON	migs.group_handle = mig.index_group_handle
JOIN	sys.dm_db_missing_index_details mid 
ON	mig.index_handle = mid.index_handle
AND	mid.database_id = DB_ID()


10 правил здравого смысла для индексов

  1. Создавайте только те индексы, которые будут использоваться. Удалите неиспользуемые индексы.
  2. Создавайте индексы по колонкам, которые в запросах используются в предложениях: WHERE, ORDER BY, GROUP BY или DISTINCT.
  3. Располагайте в ключе индекса вначале самые избирательные колонки, предикаты которых вводят самые большие ограничения выборки.
  4. Некластеризованный индекс максимально полезен, если его ключ высокоселективен.
  5. Создавайте индекс для всех внешних ключей.
  6. Используйте покрывающие индексы для часто используемых ресурсоёмких запросов.
  7. Используйте минимально-возможную длину ключей кластеризованных индексов.
  8. Кластеризованные индексы хороши для выборки диапазонов.
  9. Предпочтительнее много коротких, чем один очень длинный индекс.
  10. На периодической основе используйте программу «Помощник по настройке ядра СУБД».
В тему:
More Refinements on the Missing Indexes Query
SQL Server 2005: Определение ценности предложенных индексов
A Look at Missing Indexes

Учебник Майкрософт: Проектирование и оптимизация доступа к базам данных Microsoft SQL Server 2005. Учебный курс Microsoft (+ CD-ROM) рекомендует при разработке индексов соблюдать следующие правила:

  • В первую очередь всегда изучайте план запроса. Он покажет оптимальный с точки зрения подсистемы обработки запросов текущий план выполнения. Найдите самую дорогую часть плана выполнения и займитесь её оптимизацией. Перед этим удо-стоверьтесь, что статистика по всем таблицам в вашем запросе актуальна, запустив команду обновления статистики для всех таблиц в вашем запросе.
  • Если вы видите просмотр строк таблицы, выполните оптимизацию. Просмотр таб¬лицы - это самый медленный способ выполнения. Просмотр таблицы означает не только то, что не используется никакой индекс, но и то, что эта таблица вообще не имеет кластеризованного индекса. Даже если вы можете только заменить просмотр строк таблицы просмотром кластеризованного индекса, это уже стоит сделать.
  • Если вы видите просмотр кластеризованного индекса, выясните, может ли он быть заменен поиском в индексе. Для этого найдите условия, примененные к этой табли¬це. Как правило, существуют условия для двух или трех полей таблицы. Найдите самое селективное условие (т.е. условие, применение которого привело бы к полу¬чению наименьшего количества записей) и посмотрите, существует ли индекс на этом поле. Будет рассмотрен любой индекс, который содержит это поле. Если тако¬го индекса нет, создайте его и посмотрите, возьмет ли его в обработку подсистема обработки запросов.
  • Если подсистема обработки запросов не отрабатывает существующий индекс (т.е. если все еще выполняется просмотр кластеризованного индекса), проверьте список выходных данных. Возможно, поиск по вашему индексу может выполняться быст¬рее, чем просмотр кластеризованного индекса, но вызывает поиск закладок, что де¬лает общие издержки больше, чем использование кластеризованного индекса. Опе¬рации на кластеризованном индексе (сканирование или поиск) никогда не требуют поиска закладок, потому что кластеризованный индекс уже содержит все данные. Если список выходных данных не очень большой, добавьте эти поля в индекс и по¬смотрите, подхватила ли его подсистема обработки запросов. Помните, что общий размер важнее, чем количество полей. Добавление трех целочисленных полей в ин¬декс менее дорого, чем добавление одного поля типа varchar со средней длинной данных, равной 20.
  • Если вы видите поиск закладок, это означает, что ваш индекс не является покры¬вающим. Попытайтесь сделать его покрывающим, если это имеет смысл (см. пре¬дыдущий пункт списка). План выполнения, выбранный подсистемой обработки за¬просов, может оказаться не самым лучшим. Подсистема обработки запросов делает определенные допущения о стоимости дисковой подсистемы и центрального про¬цессора против стоимости ввода/вывода. Эти допущения иногда могут быть непра¬вильными. Если вы не верите, что выбор подсистемы обработки запросов является лучшим, запустите запрос на выполнение в цикле в течение 10-15 минут с планом, выбранным оптимизатором. Потом измените запрос, чтобы он использовал ваш индекс (вы должны будете использовать для этого подсказку индекса), и затем снова запустите запрос на выполнение в течение 10-15 минут. Сравните результаты, чтобы увидеть, какой запрос работает лучше.
  • Избегайте любых вычислений или операций на столбцах, где это возможно. Неко¬торые операции не допускают использования индекса на этом поле, даже если он существует, например, применение функций LTRIM или RTRIM на строковых дан¬ных серьезно влияет на производительность. Например, вместо приведения условий (DateField as varchar (20)) = @dateString, попробуйте сначала преобразовать @dateString в выражение типа datetime и затем сравнить со значением DateField. Если невозможно обойтись без функций или вычислений на столбце, используйте индекс, построенный на этом выражении. Это можно сделать двумя способами:
     создайте вычисляемое поле на базе вашего выражения;
     создайте представление и постройте на нем индекс.
Published Tue, Nov 13 2007 6:59 by gladchenko
Filed under: ,