Почему MS SQL Server ошибается в оценке Hash Match при соединении таблиц 1С и как это исправить

Проанализируем интересную и довольно распространенную ситуацию, когда тяжелый запрос в 1С выполняется бесконечно долго. При этом анализ плана выполнения на стороне MS SQL Server показывает, что оптимизатор изначально правильно оценивает поиск в индексах, но затем оператор Hash Match фатально ошибается, прогнозируя всего одну строку на выходе. Выясним причину такого поведения и разберем по шагам, как эффективно решить эту задачу.

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

Рассмотрим ситуацию подробнее. В платформе 1С выполняется запрос, который использует соединение с виртуальной таблицей среза последних (в терминах языка SQL это транслируется в глубоко вложенные подзапросы). Обновление статистики с полным сканированием (WITH FULLSCAN) и очистка процессорного кэша не приносят результата. Оптимизатор все равно выдает оценку в 1 строку.

Посмотрим на сокращенный пример SQL-запроса, который генерирует платформа для получения среза последних:

SELECT T1.Fld48271RRef 
FROM (
    SELECT T4._Fld48270RRef AS Fld48270RRef, T4._Fld48284RRef AS Fld48284RRef, T4._Period AS Period_, 
           T4._Fld48268 AS Fld48268_, T4._Fld48271RRef AS Fld48271RRef 
    FROM (
        SELECT T3._Fld48268 AS Fld48268_, T3._Fld48269RRef AS Fld48269RRef, T3._Fld48270RRef AS Fld48270RRef, 
               T3._Fld48283 AS Fld48283_, T3._Fld48284RRef AS Fld48284RRef, MAX(T3._Period) AS MAXPERIOD_ 
        FROM dbo._InfoRg48267 T3 WITH(NOLOCK) 
        GROUP BY T3._Fld48268, T3._Fld48269RRef, T3._Fld48270RRef, T3._Fld48283, T3._Fld48284RRef
    ) T2 
    INNER JOIN dbo._InfoRg48267 T4 WITH(NOLOCK) 
    ON T2.Fld48268_ = T4._Fld48268 AND T2.Fld48269RRef = T4._Fld48269RRef AND T2.Fld48270RRef = T4._Fld48270RRef 
    AND T2.Fld48283_ = T4._Fld48283 AND T2.Fld48284RRef = T4._Fld48284RRef AND T2.MAXPERIOD_ = T4._Period
) T1 ...

Здесь мы видим соединение сразу по 6 полям. Именно это, в сочетании со сложной архитектурой подзапросов, создает огромную проблему для механизма оценки кардинальности SQL Server.

Почему возникает оценка ровно в 1 строку?

Разберем механику работы Cardinality Estimator (CE) — компонента, отвечающего за прогнозирование количества строк. В старых версиях системы (например, SQL Server 2008 с уровнем совместимости 100), когда оптимизатор видит соединение по множеству полей (что типично для регистров с несколькими измерениями в 1С), он исходит из предположения, что данные в этих колонках абсолютно независимы друг от друга.

Математически он перемножает селективность (вероятность совпадения) для каждого из полей. Перемножение множества мелких дробей для 6 колонок дает в результате значение, стремящееся к нулю. Но поскольку вернуть 0 строк физически нельзя (а иногда оптимизатор просто считает, что вернется 0 и искусственно завышает), он округляет итоговое значение до минимума — ставит оценку в 1 строку.

Как неверная оценка приводит к зависанию (Hash Spill)

Проанализируем последствия этой ошибки. Ожидая на выходе от оператора Hash Match всего 1 строку, оптимизатор SQL Server выделяет для выполнения операции минимально возможный объем оперативной памяти — так называемый Memory Grant.

На самом же деле из таблиц базы данных поступают сотни тысяч записей. Крошечная выделенная память мгновенно переполняется, и сервер вынужден на лету сбрасывать промежуточные хеш-таблицы на жесткий диск — в системную базу tempdb. Этот аварийный процесс называется Hash Spill (или Hash Warning). Поскольку операции чтения и записи на диск медленнее работы с оперативной памятью на порядки, выполнение запроса деградирует и визуально выглядит как бесконечное зависание системы.

Варианты решения проблемы

Рассмотрим, как правильно выйти из сложившейся ситуации. Исправить проблему можно как на уровне кода 1С, так и за счет настроек самой базы данных.

Способ 1. Оптимизация на уровне кода 1С (Рекомендуемый подход)

Самый надежный и правильный способ помочь оптимизатору СУБД — разбить монолитный сложный запрос 1С, избавившись от глубокой вложенности.

1. Вынесем логику получения виртуальной таблицы среза последних в отдельный запрос.
2. Поместим результаты этого запроса во временную таблицу (с помощью менеджера временных таблиц в 1С).
3. Сделаем соединение основной выборки данных уже с готовой временной таблицей.

Почему это работает: При создании физической временной таблицы SQL Server материализует данные (таблица получает префикс #temp). Система автоматически рассчитывает и создает актуальную статистику по этой новой таблице еще до того, как начнет выполнять следующий шаг соединения. В итоге к моменту финального соединения Hash Match оптимизатор точно знает реальное количество записей, запрашивает адекватный Memory Grant, и запрос отрабатывает исключительно в оперативной памяти за доли секунды.

Способ 2. Изменение настроек MS SQL Server

Если нет возможности оперативно переписать код в конфигурации 1С, рассмотрим варианты оптимизации на стороне СУБД:

1. Повышение уровня совместимости (Compatibility Level). Начиная с SQL Server 2014 (уровень 120), Microsoft полностью переработала алгоритмы Cardinality Estimator. Новый механизм использует алгоритм "экспоненциального затухания" (exponential backoff). Он больше не считает множественные условия полностью независимыми, допуская корреляцию данных. Это позволяет давать намного более реалистичную оценку при соединении по 6 полям. Переключение уровня совместимости — обратимая операция, которую стоит протестировать на копии.
2. Использование флагов трассировки (Trace Flags). Для старых баз можно попробовать включить флаг 4199, который активирует все исправления оптимизатора, выпущенные в обновлениях. А для глубокой диагностики того, как именно CE перемножает вероятности в вашей конкретной базе, можно использовать диагностический флаг 2363 (или 3604).

Подводя итоги, важно отметить, что переписывание запроса с использованием временных таблиц — это стандартная и наиболее эффективная практика разработки в 1С. Она гарантированно спасает оптимизатор запросов SQL Server от "слепых зон" и обеспечивает стабильно высокую производительность и масштабируемость системы при любых объемах данных.