Поиск отсутствующих индексов таблиц в базе на MS SQL Server

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

• • чтению большего количества строк данных
  • увеличению времени выполнения запроса
  • появлению избыточных блокировок
  • и другим негативным последствиям

В данной статье мы рассмотрим как можно найти недостающие индексы с помощью динамических административных функций MS SQL Server.

Информация о динамических функциях

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

В данной статье мы воспользуемся тремя динамическими административными функциями (DMF): dm_db_missing_index_group_stats, dm_db_missing_index_groups, dm_db_missing_index_details

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

Запрос поиска недостающих индексов и анализ его результата

Следующий запрос выведет всю необходимую в дальнейшем информацию:

Выражение avg_total_user_cost * avg_user_impact * (user_seeks + user_scans), использованное в запросе, соответствует оценке выигрыша при добавлении отсутствующего индекса. Файл запроса можно скачать во вложениях к статье.

Выполним запрос и проанализируем его результаты на простом примере.

Как видно, отсутствует индекс в таблице AccumRg23573, при этом запрос, для которого необходим индекс, выполняется с предикатом равенства по полю Fld23580RRef. В поле user_seeks указано что этот индекс мог бы быть использован 750 раз в целях поиска по индексу, avg_user_impact говорит о том что средний процент выигрыша равен 99,99%. Так же имеет смысл обратить внимание на поле last_user_seek, оно указывает на дату и время последнего пользовательского запроса, который мог бы использовать отсутствующий индекс для поиска. Если последний раз подходящий запрос был давно, возможно, индекс будет использовать редко и необходимости в нем нет — необходимо оценить перед добавлением индекса. Теперь воспользуемся обработкой выводящей структуру хранения базы данных в терминах 1С:Предприятия (из статьи «Получение информации о структуре хранения базы данных в терминах 1С:Предприятие и СУБД»).

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

Статистика использования индекса

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

Оценив значения в колонке user_seeks можно сделать вывод что в данной системе этот индекс используется достаточно часто, а эффективность от его использования высока (была оценена первым запросом).