Как найти запросы с оператором Lazy Spool в планах выполнения MS SQL Server?

При оптимизации быстродействия информационных баз 1С на СУБД Microsoft SQL Server разработчики часто сталкиваются с деградацией производительности сложных запросов. Одной из частых причин резкого падения скорости выполнения является появление в плане запроса оператора Lazy Spool (ленивая катушка). Разберем, как обнаружить такие запросы непосредственно в кэше планов СУБД, выясним причины их появления и рассмотрим способы устранения проблемы на уровне платформы 1С.

Поиск запросов с Lazy Spool через системные представления кэша планов

Для поиска планов выполнения нет необходимости постоянно держать включенной тяжелую трассировку SQL Server Profiler. Все скомпилированные и находящиеся в оперативной памяти планы хранятся в системных динамических представлениях (DMV). Проанализируем, как извлечь нужную информацию с помощью системных функций СУБД.

В XML-структуре графического плана выполнения оператор Lazy Spool представлен комбинацией атрибутов PhysicalOp="Table Spool" и LogicalOp="Lazy Spool". Посмотрим на наиболее точный и эффективный T-SQL запрос, выполняющий разбор XML кэша:

SELECT 
    cp.plan_handle,
    q.text AS [QueryText],
    qp.query_plan AS [QueryPlan]
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS q
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
WHERE qp.query_plan.exist('//RelOp[@PhysicalOp="Table Spool" and @LogicalOp="Lazy Spool"]') = 1;

Разберем по шагам, как работает этот запрос:

1. Представление sys.dm_exec_cached_plans возвращает список дескрипторов всех скомпилированных планов запросов, находящихся в кэше памяти.
2. Функция sys.dm_exec_sql_text возвращает исходный текст SQL-запроса, переданного платформой 1С на сервер СУБД.
3. Функция sys.dm_exec_query_plan возвращает план выполнения в формате XML.
4. Метод exist() для XML-типа производит поиск узла RelOp с заданными физическим и логическим операторами. Такой подход значительно точнее обычного текстового поиска, так как исключает ложные срабатывания.

Если разбор XML на высоконагруженном сервере выполняется медленно, рассмотрим упрощенный вариант с преобразованием плана в обычную текстовую строку через sys.dm_exec_text_query_plan:

SELECT 
    cp.plan_handle,
    q.text AS [QueryText],
    p.query_plan AS [QueryPlanText]
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_text_query_plan(cp.plan_handle, 0, -1) AS p
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS q
WHERE p.query_plan LIKE '%Table Spool%'
  AND p.query_plan LIKE '%Lazy Spool%';

Обратите внимание: если кэш планов на сервере очищается слишком часто из-за динамической параметризации запросов 1С или нехватки памяти, эффективнее использовать механизм Extended Events (Расширенные события) с фильтрацией по высокому числу логических чтений и времени выполнения, что создает минимальную нагрузку на СУБД.

В чем опасность оператора Lazy Spool

Оператор Lazy Spool сохраняет промежуточные результаты работы поддерева плана во временную рабочую таблицу (worktable) в системной базе данных tempdb. Данные считываются и кэшируются только по мере фактической потребности («ленивое» вычисление). Рассмотрим ключевые риски использования этого оператора:

1. Колоссальная нагрузка на подсистему ввода-вывода (I/O): работа со Spool-операторами заставляет СУБД активно писать и читать данные из tempdb, что мгновенно перегружает диски.
2. Ожидания PAGELATCH: множественные параллельные сеансы пользователей, генерирующие Spool, начинают конкурировать за системные страницы распределения памяти базы tempdb (PFS, GAM, SGAM), приводя к блокировкам типа PAGELATCH_UP.
3. Непредсказуемость времени отклика: запрос может выполняться доли секунды на небольшом объеме данных, но при росте объема строк кэш Spool перестает помещаться в RAM, и запрос начинает выполняться десятками минут.

Причины появления Lazy Spool в запросах 1С

Выясним основные причины, вынуждающие оптимизатор MS SQL Server строить планы с промежуточными таблицами Spool:

1. Коррелированные подзапросы: размещение подзапросов в полях выборки секции ВЫБРАТЬ или внутри условий ГДЕ, когда подзапрос обращается к полям внешнего источника. Оптимизатор пытается кэшировать вычисления для одинаковых значений внешнего запроса.
2. Ошибки оценки кардинальности (Cardinality Estimation): устаревшая статистика СУБД приводит к тому, что оптимизатор рассчитывает получить несколько строк, выбирает соединение Nested Loops с Lazy Spool, а на практике обрабатывает миллионы записей.
3. Сложные условия соединений: использование логических операторов ИЛИ, конструкций ВЫБОР КОГДА и неэквисоединений (операторы больше, меньше, не равно) в секции соединения ПО.
4. Отсутствие подходящих индексов: отсутствие покрывающего индекса вынуждает СУБД создавать собственный индекс в рабочей таблице прямо во время выполнения запроса.

Способы оптимизации запросов на стороне 1С

В платформе 1С разработчик не имеет возможности передавать прямые директивы оптимизатору (Query Hints) вроде принудительного использования HASH JOIN. Поэтому влиять на план выполнения можно только правильным проектированием текста запроса 1С.

Рассмотрим правила устранения Lazy Spool:

1. Отказ от вложенных подзапросов в выборке: любые расчетные данные и итоги по подчиненным регистрам и табличным частям следует рассчитывать заранее в отдельных временных таблицах с последующим соединением по индексированным полям.
2. Декомпозиция с помощью временных таблиц: громоздкие запросы с десятками соединений необходимо разделять на последовательные шаги с выгрузкой во временные таблицы (ПОМЕСТИТЬ).
3. Обязательное индексирование временных таблиц: для временных таблиц всегда следует явно указывать индексацию (ИНДЕКСИРОВАТЬ ПО) по полям, участвующим в дальнейших связях. Это позволяет СУБД рассчитать корректную статистику и применить быстрые алгоритмы Hash Match или Merge Join вместо циклов со Spool.
4. Строгие условия в секции соединения: в конструкции ПО должны присутствовать исключительно строгие равенства по ключевым полям. Дополнительную фильтрацию строк необходимо выносить в блок ГДЕ.

Административная настройка СУБД

Если полностью избавиться от Spool-операторов в типовых решениях не удается, необходимо минимизировать негативное влияние на СУБД на уровне администрирования:

1. Разделить базу данных tempdb на несколько файлов одинакового размера в соответствии с количеством ядер процессора (рекомендуется от 4 до 8 файлов).
2. Перенести файлы tempdb на максимально быстрый NVMe-накопитель.
3. Настроить регулярные регламентные задания по обновлению статистик СУБД для исключения фатальных ошибок оптимизатора при построении планов.