Почему повторный расчет и пакетный запрос в 1С на MS SQL зависают на час после первого успешного выполнения?

Нередко разработчики 1С сталкиваются со специфическим поведением системы: при первом запуске алгоритма расчет и запись данных выполняются за пару минут, но при повторном запуске той же команды с идентичными параметрами тот же самый запрос зависает на десятки минут или даже часов. Проанализируем природу этой проблемы, выясним причины деградации плана выполнения на стороне СУБД MS SQL Server и разберем практические способы устранения «залипания» запросов.

1. Симптомы проблемы и механизм деградации плана

Рассмотрим типовой сценарий, описанный в обсуждении: внешняя обработка считывает данные из независимого непериодического регистра сведений, рассчитывает зависимые показатели и выполняет перезапись набора записей обратно в регистр. Первый расчет занимает около минуты, повторный — зависает на этапе выполнения пакетного запроса. При этом монитор ресурсов операционной системы показывает наличие свободной оперативной памяти и отсутствие физических блокировок со стороны других пользователей.

Причина такого поведения кроется в особенностях работы оптимизатора запросов MS SQL Server и кэширования планов выполнения (механизм Parameter Sniffing):

1. При первом запуске запроса СУБД компилирует план выполнения под исходное состояние таблицы и переданные параметры. Выбирается определенная стратегия соединения (например, Hash Match или Merge Join), и расчет завершается быстро.
2. После завершения расчета алгоритм выполняет массовую запись или перезапись набора записей в регистр сведений. Распределение данных, селективность и объем строк в страницах данных изменяются.
3. При повторном вызове команды СУБД берет из кэша ранее скомпилированный план выполнения. Однако из-за изменившихся данных и особенностей оценки кардинальности оптимизатор ошибается в оценке количества строк и скатывается в крайне медленные циклы Nested Loops (вложенные циклы) с многократным сканированием таблиц (Index Scan или Table Scan).
4. В консоли запросов план может быть другим, поскольку сессионные параметры (такие как ARITHABORT или ANSI_NULLS) в контексте внешнего соединения или фонового вызова могут отличаться от интерактивной консоли, заставляя сервер компилировать отдельный план.

2. Ошибки проектирования временных таблиц в пакетных запросах

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

Разберем типичные ошибки и правила их исправления:

1. Отсутствие индексов на полях соединения: когда данные помещаются во временную таблицу без явного указания индексов, СУБД часто предполагает, что в таблице содержится всего 1 или 100 строк. При последующем соединении такой таблицы с миллионным регистром выбирается Nested Loops. Если строк во временной таблице оказалось больше ожидаемого, время выполнения возрастает в тысячи раз. Всегда используйте предложение ИНДЕКСИРОВАТЬ ПО для полей, участвующих в условиях ПО и ГДЕ:

ВЫБРАТЬ
    ИсходныеДанные.Идентификатор КАК Идентификатор,
    ИсходныеДанные.Показатель КАК Показатель
ПОМЕСТИТЬ ВТ_ДанныеДляРасчета
ИЗ
    РегистрСведений.ЗначенияПоказателей КАК ИсходныеДанные
ИНДЕКСИРОВАТЬ ПО
    Идентификатор;

2. Слишком длинные пакеты: если пакет включает десятки промежуточных таблиц, разделите логику на несколько независимых запросов. Используйте объект МенеджерВременныхТаблиц, выполняя промежуточную выборку, а затем освобождая ресурсы явным методом МенеджерВременныхТаблиц.Закрыть(). Это позволит СУБД переоценивать объемы данных между вызовами.

3. Неактуальная статистика после перезаписи набора записей

В СУБД MS SQL включен автоматический пересчет статистики (AUTO_UPDATE_STATISTICS), но он срабатывает только при изменении определенного процента строк таблицы (по умолчанию — около 20% плюс 500 строк). Если расчет изменяет значимый, но недостаточный для триггера объем данных, распределение индексов кардинально меняется, а статистика считается «актуальной».

Для стабилизации повторных расчетов рассмотрим следующие шаги:

1. Если расчет выполняется в рамках регламентной процедуры, после массовой записи набора полезно запускать обновление статистики по целевой таблице регистра средствами регламентных процедур СУБД или регламентного задания 1С.
2. В конфигурациях на MS SQL Server 2016 и новее можно скорректировать параметры уровня базы данных (Scoped Configurations), отключив чувствительность к параметрам: ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SNIFFING = OFF;.
3. Если используется версия MS SQL 2014+, проверьте модель оценки кардинальности. Новый оптимизатор часто ошибается на структурах 1С; переключение на устаревшую модель (LEGACY_CARDINALITY_ESTIMATION = ON) в ряде случаев сразу устраняет неадекватные планы.

4. Структура индексов непериодического регистра сведений

Физически таблица непериодического независимого регистра сведений в MS SQL имеет кластерный индекс, порядок полей которого строго повторяет последовательность измерений, заданных в дереве метаданных конфигуратора.

Проанализируем правила работы с индексами регистра:

1. Правило левого префикса: кластерный индекс работает эффективно только тогда, когда в секциях ГДЕ или в соединениях отбор идет по первому измерению регистра, затем по второму и так далее. Если в запросе накладывается условие по второму измерению, а первое не участвует в фильтре, СУБД выполняет полное сканирование таблицы (Index Scan) вместо точечного поиска (Index Seek).
2. Дополнительное индексирование: если алгоритм связывает данные по полям, которые стоят в регистре не первыми, для соответствующих измерений или реквизитов в конфигураторе 1С необходимо обязательно установить свойство «Индексировать». Это создаст дополнительный некластерный индекс в SQL и исключит перебор всей таблицы.

5. Настройки сервера СУБД (MAXDOP и TempDB)

Для исключения неоптимального распараллеливания тяжелых запросов проверим ключевые параметры инстанса MS SQL Server:

1. Ограничение параллелизма (MAXDOP): по рекомендациям фирмы «1С», для баз с высокой транзакционной нагрузкой параметр Max Degree of Parallelism должен быть равен 1 (параллелизм отключен), либо значение не должно превышать 2-4 с увеличением Cost Threshold for Parallelism до 30-50. Параллельные планы на плохой статистике способны приводить к колоссальным потерям времени на ожидании CXPACKET.
2. Конфигурация TempDB: поскольку пакетные запросы активно используют временные таблицы, база tempdb должна быть разбита на несколько файлов данных равного размера (соответственно числу физических ядер сервера, но не более 8), чтобы исключить очереди за страницами распределения пространства памяти.