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

Всего комментариев: 1

  1. Добрый день, есть вопрос
    Как добавить индекс в 1С если запрос на недостающие индексы предлагает добавить составной индекс на несколько полей? Или например на «скрытые» поля в дереве метаданных (типа «Проведен»)? Я так понимаю делается это sql запросом, и как бороться если 1с при обновлении (или создании новой базы) перезатрет потом добавленные «вручную» индексы при помощи запроса sql.

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *