Поиск отсутствующих индексов в базе на 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 можно сделать вывод что в данной системе этот индекс используется достаточно часто, а эффективность от его использования высока (была оценена первым запросом).
