Автоматическое исправление планов (Automatic Plan Correction) в SQL Server

Автор: theSQLSith, Query plan regressions got you down? Here's how can Automatic Plan Correction turns it around

Регрессии планов запросов донимают? Вот как автоматическое исправление планов (Automatic Plan Correction) может всё изменить.

Автоматическое исправление планов (Automatic Plan Correction, APC) — это одна из тех функций, о которой я довольно часто беседую с заказчиками, инженерами поддержки и широким сообществом SQL. Она входит в семейство автоматической настройки (Automatic Tuning) и незаметно выполняет свою работу, начиная с SQL Server 2017, обнаруживая регрессии планов запросов и автоматически принудительно применяя ранее известный хороший план для восстановления производительности. Но один из частых вопросов звучит так: как же APC на самом деле решает, произошла ли регрессия? И насколько оно уверено в этом решении?

Начиная с SQL Server 2022 CU4 и продолжая в SQL Server 2025, мы внесли значительные улучшения в статистическую модель, которую APC использует для обнаружения регрессий. В этой статье мы рассмотрим, что изменилось, почему это важно и как вы можете этим воспользоваться.

⚙️ Два способа обнаружения регрессии

APC поддерживает две статистические модели для определения того, произошла ли регрессия плана запроса. Обе анализируют статистику выполнения, собираемую Хранилищем запросов (Query Store) (например, время ЦП), чтобы сравнить производительность текущего плана с предыдущим заведомо хорошим планом.

Оригинальная модель: сравнение на основе сигмы

Оригинальная модель обнаружения регрессии использует сравнение на основе сигмы (обычно ассоциируемое с «правилом трёх сигм»). Она вычисляет, превышает ли разница в среднем времени ЦП между текущим планом и последним известным хорошим планом порог в 3 стандартных отклонения.

Этот подход хорошо работает, когда время выполнения следует нормальному распределению с постоянной дисперсией. Однако со временем мы обнаружили у этого подхода некоторые ограничения:

• Высокая дисперсия может маскировать регрессии. Если время ЦП для данного плана сильно варьируется (что часто встречается в реальных рабочих нагрузках), стандартное отклонение становится большим. Регрессия, при которой среднее время ЦП удваивается, всё ещё может попадать в полосу 3 сигм, из-за чего APC может её пропустить.
• Предполагается равная дисперсия. Модель рассматривает дисперсию как текущей, так и базовой совокупности планов как сопоставимую. Когда они значительно различаются (например, у регрессировавшего плана время выполнения сильно варьируется, а у хорошего плана было стабильным), точность сравнения падает.
• Требуется больше выполнений. Модели необходимо как минимум 15 выполнений текущего плана и 15 выполнений базового плана, прежде чем она примет решение.

🆕 Более новая модель: t-критерий Уэлча (Welch's t-test)

Более новая модель обнаружения регрессии использует t-критерий Уэлча — хорошо зарекомендовавший себя статистический гипотетический тест, разработанный специально для сравнения двух совокупностей, которые могут иметь неравные дисперсии и неравные размеры выборок. Если вы не знакомы с t-критерием Уэлча, представьте его как более надёжный способ ответить на вопрос: «действительно ли эти два набора чисел различаются, или разница — просто шум?»

Ключевые улучшения по сравнению с оригинальной моделью:

• Лучшая точность в рабочих нагрузках с высокой дисперсией. T-критерий Уэлча явно учитывает различные дисперсии между текущим планом и базовым планом. Его не обманет план, который иногда выполняется быстро, а иногда очень медленно. Он всё равно может обнаружить, что распределение сместилось в неблагоприятную сторону.
• Более быстрое время реакции. APC может начинать оценку плана уже после 3 выполнений нового плана, по сравнению с 15 в оригинальной модели. Когда разница в производительности статистически значима, APC может обнаружить и исправить регрессию гораздо быстрее.
• Адаптивный размер выборки. Если начальная выборка из 3 выполнений даёт «неопределённый» результат, APC не сдаётся. Он постепенно увеличивает количество выполнений, необходимых перед повторной проверкой, пока не будет накоплено достаточно статистики для уверенного решения.
• Многократные проверки перед окончательным решением. Модель t-критерия Уэлча выполняет более одной проверки в течение жизненного цикла потенциальной регрессии. Она переоценивает ситуацию по мере поступления новых данных, снижая вероятность как ложноположительных, так и ложноотрицательных срабатываний.

С моделью t-критерия Уэлча мы значительно улучшили как точность обнаружения регрессий, так и время реакции APC на исправление регрессии.

🧭 Включение модели t-критерия Уэлча

Модель t-критерия Уэлча является моделью обнаружения регрессий по умолчанию в:

• Azure SQL Database, SQL Database в Microsoft Fabric, Azure SQL Managed Instance (включено по умолчанию, никаких действий не требуется)
• SQL Server 2025 (включено по умолчанию, никаких действий не требуется)

Для SQL Server 2022 включите её с помощью флага трассировки 12618 (доступно начиная с CU4):

-- Включение глобально (стартовый флаг трассировки)
DBCC TRACEON (12618, -1);

-- Или добавьте как параметр запуска: -T12618

Оригинальная модель на основе сигмы остаётся запасной и используется, когда флаг трассировки 12618 не включён на SQL Server 2022 CU4+. Как всегда, мы настоятельно рекомендуем применять последние накопительные обновления (Cumulative Updates) для SQL Server.

🧪 Обработка долго выполняющихся запросов и запросов с истечением времени ожидания

Обе модели обнаружения регрессий имеют общее смещение, на которое я хочу указать: проверки APC запускаются после завершения определённого количества выполнений запроса. Это означает, что APC по своей природе сначала оценивает самые быстрые выполнения и может пропустить регрессии, симптомом которых является выполнение запросов в течение нескольких минут или полное истечение времени ожидания.

Давайте представим, как это выглядит. Представьте ситуацию, когда общая загрузка ЦП на сервере неожиданно возрастает со средних 10% до 100%, и вы обнаруживаете, что произошло изменение плана для определённого запроса. Расширенные события могут показывать, что запрос выполнялся несколько раз быстро, скажем, 15 выполнений, которые завершились за миллисекунды. Однако в ходе анализа вы замечаете, что было много других выполнений запроса, которые выполнялись дольше, возможно, несколько минут. Некоторые из этих долго выполняющихся запросов завершились успешно, но другие — с истечением времени ожидания. К тому моменту, когда эти медленные выполнения завершаются и их статистика попадает в Хранилище запросов, APC уже приняло решение о регрессии на основе быстрых выполнений и пришло к выводу, что регрессии не было.

Флаг трассировки 12656 решает эту проблему, включая отложенную повторную проверку на основе времени. Когда он включён, APC планирует дополнительную проверку через 5 минут после первого обнаружения изменения плана. Эта отложенная проверка повторно оценивает запрос, используя статистику выполнения, которая теперь включает медленные выполнения и выполнения с истечением времени, предоставляя более полную картину.

-- Включение глобально
DBCC TRACEON (12656, -1);

Как и флаг трассировки 12618, флаг трассировки 12656 также доступен начиная с SQL Server 2022 CU4.

Временная повторная проверка для отдельных запросов с помощью sp_configure_automatic_tuning

В то время как флаг трассировки 12656 применяет эту повторную проверку глобально, вы также можете включить её для отдельных запросов, используя хранимую процедуру sp_configure_automatic_tuning с параметром FORCE_LAST_GOOD_PLAN_EXTENDED_CHECK. Это особенно полезно в Azure SQL Database, SQL Database в Microsoft Fabric и Azure SQL Managed Instance, где глобальный флаг трассировки недоступен. Он обеспечивает ту же 5-минутную отложенную проверку, что и флаг трассировки 12656, но ограниченную только теми запросами, которые вы выбрали:

-- Включение временной повторной проверки для конкретного запроса
EXEC sp_configure_automatic_tuning
    @option = 'FORCE_LAST_GOOD_PLAN_EXTENDED_CHECK',
    @option_value = 'ON',
    @type = 'QUERY',
    @type_value = <query_id>;

-- Отключение временной повторной проверки для конкретного запроса
EXEC sp_configure_automatic_tuning
    @option = 'FORCE_LAST_GOOD_PLAN_EXTENDED_CHECK',
    @option_value = 'OFF',
    @type = 'QUERY',
    @type_value = <query_id>;

Это позволяет вам нацеливаться на конкретные запросы, которые, как известно, имеют долго выполняющиеся шаблоны или шаблоны с истечением времени ожидания, без наложения накладных расходов временных проверок на каждый запрос в базе данных. Далее показаны различия в области действия:

Сравнение методов временной повторной проверки

• Флаг трассировки 12656: Область действия — все запросы на экземпляре (глобально). Доступность — SQL Server 2022 CU4 и более поздние версии (включая SQL Server 2025 и более поздние).
• sp_configure_automatic_tuning FORCE_LAST_GOOD_PLAN_EXTENDED_CHECK: Область действия — отдельные запросы (для каждого запроса). Доступность — SQL Server 2022 CU4 и более поздние версии, SQL Server 2025, Azure SQL Database, SQL Database в Microsoft Fabric и Azure SQL Managed Instance.

🔍 Мониторинг решений APC

Иллюстрацию ниже можно использовать как мысленную карту всех фаз, которые проходит функция автоматического исправления планов и которые можно отслеживать.

sys.dm_db_tuning_recommendations

Динамическое административное представление sys.dm_db_tuning_recommendations можно использовать для просмотра любых действий, которые APC выполнило, если оно включено. Оно также будет заполнено, если функция отключена, показывая действие, которое было бы выполнено, если бы оно было включено. Результирующий набор представлен в формате JSON, поэтому для разбора вывода можно использовать запрос, подобный приведённому ниже:

SELECT
    JSON_VALUE([state], '$.currentValue') AS [state],
    script = JSON_VALUE(details, '$.implementationDetails.script'),
    planForceDetails.*
FROM sys.dm_db_tuning_recommendations
CROSS APPLY OPENJSON(Details, '$.planForceDetails')
    WITH (
        [query_id] int '$.queryId',
        regressedPlanId int '$.regressedPlanId',
        recommendedPlanId int '$.recommendedPlanId',
        regressedPlanErrorCount int,
        recommendedPlanErrorCount int,
        regressedPlanExecutionCount int,
        regressedPlanCpuTimeAverage float,
        recommendedPlanExecutionCount int,
        recommendedPlanCpuTimeAverage float
    ) AS planForceDetails;

⚠️ Примечание: Данные в sys.dm_db_tuning_recommendations не сохраняются при перезапуске компонента Database Engine.

📋 Расширенные события для более глубокого анализа

Также может быть полезно захватывать некоторые расширенные события APC для дополнительной информации и анализа. Для общей наблюдаемости APC ключевыми событиями являются:

• automatic_tuning_plan_regression_detection_check_completed – возникает, когда APC завершает оценку плана на предмет регрессии. Показывает, была ли обнаружена и исправлена регрессия.
• automatic_tuning_plan_regression_verification_check_completed – возникает, когда APC завершает проверку ранее принудительно применённого плана.
• automatic_tuning_check_abandoned – APC прекращает проверку (например, существует только один план).
• automatic_tuning_recommendation_expired – APC отказывается от принудительно применённого плана, который не помогает.
• automatic_tuning_diagnostics – периодическая сводка о состоянии с количеством проверок, обнаружений и исправлений.

Для анализа именно модели t-критерия Уэлча существует специальное событие (в настоящее время недоступно для Azure SQL Database и SQL Database в Microsoft Fabric):

• automatic_tuning_wtest_details – возникает во время оценки t-критерия Уэлча, предоставляя подробную статистическую информацию о тесте.

⚠️ Примечание: Событие automatic_tuning_wtest_details может генерировать больше сообщений, чем другие события. Это один из способов проверить эффекты включения флага трассировки 12618, если вы заинтересованы в сравнении результатов в рабочей нагрузке между старой и новой моделями обнаружения регрессий, но учтите накладные расходы, прежде чем включать его в производственной среде. Кроме того, это событие недоступно в сеансах на уровне базы данных в Azure SQL Database для SQL Database в Microsoft Fabric.

Вот пример скрипта для создания сеанса, который захватывает ключевые события:

CREATE EVENT SESSION [APC_Welch_Monitor] ON SERVER
ADD EVENT qds.automatic_tuning_plan_regression_detection_check_completed (
    ACTION (sqlserver.database_id, sqlserver.database_name)
),
ADD EVENT qds.automatic_tuning_plan_regression_verification_check_completed (
    ACTION (sqlserver.database_id, sqlserver.database_name)
),
ADD EVENT qds.automatic_tuning_wtest_details (
    ACTION (sqlserver.database_id, sqlserver.database_name)
),
ADD EVENT qds.automatic_tuning_diagnostics (
    ACTION (sqlserver.database_id, sqlserver.database_name)
)
ADD TARGET package0.event_file (
    SET filename = N'APC_Welch_Monitor.xel',
        max_file_size = 50,
        max_rollover_files = 5
)
WITH (
    MAX_MEMORY = 4096 KB,
    EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,
    MAX_DISPATCH_LATENCY = 5 SECONDS,
    STARTUP_STATE = OFF
);
GO

ALTER EVENT SESSION [APC_Welch_Monitor] ON SERVER STATE = START;

📊 Сводка

Ниже приведено краткое сравнение двух моделей обнаружения регрессий. Если вы работаете на SQL Server 2022, я призываю вас попробовать флаг трассировки 12618 и посмотреть, как он улучшает реакцию APC на ваши рабочие нагрузки. Если вы используете SQL Server 2025, Azure SQL Database, SQL Database в Microsoft Fabric или Azure SQL Managed Instance, модель t-критерия Уэлча уже активна по умолчанию.

Сравнение моделей обнаружения регрессий

Аспект	Оригинальная модель (на основе сигмы)	Модель t-критерия Уэлча
Статистический метод	Порог 3 сигмы на разницу среднего времени ЦП	t-критерий Уэлча (t-тест с неравными дисперсиями)
Обработка неравной дисперсии	Нет (предполагает одинаковую дисперсию)	Да (явно моделирует разные дисперсии)
Минимальное количество выполнений для оценки	15 (текущий план)	Всего 3 (текущий план), адаптивное экспоненциальное увеличение
Адаптивный размер выборки	Нет	Да (постепенно увеличивает требуемое количество выполнений при неопределённых результатах)
Многократные проверки	Однократная проверка	Многократные переоценки по мере накопления данных
По умолчанию в Azure SQL DB, SQL Database в Microsoft Fabric, Azure SQL Managed Instance	Нет (запасная модель)	Да
Включение на SQL Server	По умолчанию на SQL Server 2022	По умолчанию на SQL Server 2025; требуется флаг трассировки 12618 для SQL Server 2022 CU4+

Продолжая делать автоматическое исправление планов более надёжным и устойчивым, надеюсь, эта статья блога окажется для вас полезной. Мы всегда хотим слышать сообщество, поэтому, пожалуйста, продолжайте делиться своими отзывами и опытом по адресу https://aka.ms/sqlfeedback.

📚 Подробнее

• Автоматическая настройка (Automatic tuning)
• sys.dm_db_tuning_recogoog_701640856mmendations
• Обзор Хранилища запросов (Query Store)