Что делать с рекомендациями MS SQL Server по созданию отсутствующих индексов в базе 1С?

При администрировании высоконагруженных систем на базе 1С:Предприятие и MS SQL Server 2019 системные администраторы часто сталкиваются с механизмом Missing Index (отсутствующие индексы). SQL Server анализирует выполняемые запросы и предлагает скрипт для создания нового индекса, который теоретически может снизить стоимость запроса на значительный процент (в нашем примере — на 68%). Однако в среде 1С слепое следование этим рекомендациям может привести к деградации производительности. Разберем по шагам, как правильно реагировать на такие советы СУБД.

Анализируем полезность предлагаемого индекса

Рассмотрим ситуацию: система предлагает создать индекс для таблицы _Document1493, включающий поля _Marked, _Fld56409_TYPE, _Fld56409_RTRef, _Fld56409_RRRef и другие, а также секцию INCLUDE с датой и временем. Прежде чем выполнять CREATE INDEX, проанализируем следующие аспекты:

1. Нагрузка на запись: Каждый новый индекс ускоряет чтение (SELECT), но замедляет запись (INSERT, UPDATE, DELETE). Для таблицы документов, где постоянно происходит проведение и изменение статусов, индекс из 7–9 полей может существенно замедлить работу пользователей.
2. Частота выполнения запроса: SQL Server дает рекомендацию на основе накопленной статистики. Если запрос, требующий этот индекс, выполняется один раз в неделю ночью — создание индекса бессмысленно. Если же это динамический список, который открывают сотни пользователей ежеминутно — оптимизация необходима.
3. Избыточность: Часто предлагаемый индекс на 90% дублирует уже существующий, отличаясь лишь парой полей в конце или их порядком. Проанализируем существующие индексы таблицы _Document1493 перед принятием решения.

Выясняем причину появления рекомендации через Query Store

Чтобы понять, какой именно объект 1С (отчет, динамический список или фоновое задание) генерирует тяжелый запрос, воспользуемся инструментом Query Store (Хранилище запросов), который в SQL Server 2019 включен по умолчанию или легко активируется. Проанализируем ситуацию:

Найдем текст T-SQL запроса, для которого предлагается индекс. В тексте запроса обычно содержатся имена таблиц и полей (например, _Fld56412RRef). Мы можем сопоставить их с объектами метаданных 1С, используя консоль запросов или функции получения имени таблицы БД.

Пример запроса для поиска тяжелых планов в Query Store:

SELECT TOP 10
    qt.query_sql_text,
    q.query_id,
    p.plan_id,
    rs.avg_duration
FROM sys.query_store_query_text AS qt
JOIN sys.query_store_query AS q ON qt.query_text_id = q.query_text_id
JOIN sys.query_store_plan AS p ON q.query_id = p.query_id
JOIN sys.query_store_runtime_stats AS rs ON p.plan_id = rs.plan_id
WHERE qt.query_sql_text LIKE '%_Document1493%'
ORDER BY rs.avg_duration DESC;

Особенности полей составного типа и секции INCLUDE

В предложенном индексе мы видим поля _Fld56409_TYPE и _Fld56409_RTRef. Это признаки составного типа данных в 1С. Рассмотрим подробнее, почему это важно:

Если в запросе 1С не используется ограничение типа (ВЫРАЗИТЬ ... КАК ...), SQL Server вынужден сканировать большие диапазоны данных. Вместо создания индекса на стороне SQL часто эффективнее оптимизировать сам запрос в Конфигураторе 1С, сделав его типизированным. Это позволит использовать уже существующие индексы по полю _RRRef.

Также обратим внимание на секцию INCLUDE в скрипте:

/* CREATE NONCLUSTERED INDEX [IX_Custom] ON [dbo].[_Document1493] ([_Marked]...) 
INCLUDE ([_Date_Time],[_Fld56416RRef]) */

Покрывающие индексы (с INCLUDE) позволяют СУБД получить все нужные данные прямо из индекса без обращения к основной таблице (операция Key Lookup). Важно помнить: средствами Конфигуратора 1С создать индекс с секцией INCLUDE невозможно. Если мы создадим индекс в Конфигураторе, указав эти поля как индексируемые, они попадут в «дерево» индекса, что сделает его более тяжелым и менее эффективным, чем предложил SQL Server.

Лицензионная чистота и выживаемость индексов

Разберем вопрос легальности и технической эксплуатации ручных индексов:

1. Лицензионное соглашение: Фирма 1С официально разрешает создание дополнительных индексов средствами СУБД для целей оптимизации. Это не является нарушением, если не меняется логика работы структуры данных.
2. Реструктуризация: При любом изменении структуры таблицы в Конфигураторе (например, добавлении нового реквизита в документ _Document1493), платформа 1С пересоздает таблицу в SQL. При этом все индексы, созданные вручную в SQL Management Studio, будут удалены.
3. Решение: Если мы решили, что индекс необходим, следует сохранить скрипт его создания. Опытные администраторы используют специальные триггеры на изменение схемы DDL Triggers или включают пересоздание таких индексов в регламент обновления информационной базы.

Итоговые рекомендации

Проанализировав ситуацию, выработаем алгоритм действий:

• Посмотрим на показатель Total Cost и User Seeks в отчете об отсутствующих индексах. Если значения исчисляются миллионами — индекс нужен.
• Проверим, нет ли в таблице похожих индексов. Возможно, стоит просто перестроить существующий индекс (хотя для 1С это потребует внесения изменений в метаданные).
• Попробуем сначала оптимизировать запрос в 1С (проверить использование индексов, типизацию составных полей, параметры виртуальных таблиц).
• Если оптимизация кода не помогает и нагрузка критична — создаем индекс в SQL Server, но обязательно документируем этот шаг для будущих обновлений конфигурации.

Использование SQL Server 2019 также дает преимущество в виде Intelligent Query Processing. Иногда простое обновление статистики (UPDATE STATISTICS) или очистка процедурного кэша позволяют системе выбрать более удачный план без создания новых индексов.