Обходим шторм компиляций стороной

Автор: Luca Biondi, Check SQL Server Plan Cache Pollution (III) in 45 Seconds

Вы когда-нибудь задумывались, почему ваш ЦП достигает 100% при низком объёме запросов? В этой статье я разберу механизм «Штормов компиляции» (Compilation Storms) и покажу, как обнаружить узкие места типа SOS_CACHESTORE менее чем за минуту.

В двух словах

• Стоимость компиляции (Compilation Cost): Такты ЦП тратятся впустую, когда SQL Server выполняет «Полную оптимизацию» (Full Optimization) многократно.
• Тривиальные планы (Trivial Plans): Быстрый путь к выполнению, который пропускает оптимизацию на основе стоимости для простых запросов.
• Триггеры перекомпиляции (Recompilation Triggers): Плохая статистика и изменения схемы (DDL) являются основными подозреваемыми в нестабильности кэша.
• Предупреждение о спинблокировке (Spinlock Warning): Высокое время ожидания SOS_CACHESTORE означает интенсивную конкуренцию за кэш планов.

Управление высококонкурентной средой SQL Server — это игра на миллиметры. Мы часто говорим о «быстрых запросах», но редко говорим о налоге, который движок платит до того, как запрос вообще начнётся: о компиляции. Когда ваш кэш планов нестабилен, SQL Server входит в «Шторм компиляции», превращая ваши высококлассные ЦП в дорогие обогреватели. Давайте заглянем под капот.

Диагностика: компиляция против перекомпиляции

Что это на самом деле: Компиляция — это интенсивный процесс на ЦП, в ходе которого оптимизатор создаёт план выполнения. Он происходит в несколько этапов:

• Тривиальные планы (Trivial Plans): Если запрос достаточно прост (например, простой SELECT по первичному ключу), SQL Server обходит оптимизатор на основе стоимости, чтобы сэкономить ЦП. Это «Полоса экспресс-доставки».
• Полная оптимизация (Full Optimization): Для сложных соединений SQL Server перебирает несколько «Поисковых пространств» (Search Works). Здесь и кроется настоящая стоимость ЦП.
• Перекомпиляция (Recompilation): Вызывается изменениями схемы (ALTER TABLE), обновлениями статистики (превышение порога RT — Recompilation Threshold) или явными подсказками.

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

Запрос: обнаружение штормов компиляции

Используйте этот диагностический запрос, чтобы мгновенно определить, тратит ли ваш сервер слишком много времени на «размышления» и недостаточно — на выполнение.

-- Диагностический запрос ЦП: мониторинг скорости компиляции и перекомпиляции
SELECT 
    [Counter] = counter_name,
    [Value] = cntr_value,
    [Status] = CASE 
        WHEN counter_name = 'SQL Compilations/sec' AND cntr_value > 100 THEN 'Высокая скорость компиляции'
        WHEN counter_name = 'SQL Re-Compilations/sec' AND cntr_value > 20 THEN 'Критический шторм перекомпиляции'
        ELSE 'Здорово'
    END
FROM sys.dm_os_performance_counters
WHERE counter_name IN ('SQL Compilations/sec', 'SQL Re-Compilations/sec')
AND object_name LIKE '%SQL Statistics%';

Источник: sys.dm_os_performance_counters (Microsoft Learn)

Исправление: стабилизация оптимизатора

Если вы обнаружили, что частота перекомпиляций резко возрастает, примите следующие незамедлительные меры:

• Optimize for Ad Hoc: Включите эту настройку на уровне сервера, чтобы уменьшить раздувание кэша от одноразовых запросов.
• Стратегия обновления статистики: Избегайте AUTO_UPDATE_STATISTICS_ASYNC, если вы не можете позволить себе внезапные изменения планов в часы пик.
• Замораживание планов (Plan Freezing): Используйте Хранилище запросов (Query Store), чтобы принудительно применять планы для критически важных запросов, минуя необходимость в переоптимизации.

Моя работающая стратегия

За 25 лет настройки производительности я видел, как «изменения схемы» (Schema Changes) приводили к падению корпоративных систем. Даже маленький ALTER TABLE... ADD COLUMN может сделать недействительными тысячи планов за миллисекунды.

Стратегия: Всегда выполняйте изменения DDL в периоды низкой нагрузки и используйте подсказку KEEPFIXED PLAN для запросов, чувствительных к изменчивости статистики. Если вы видите высокие ожидания SOS_CACHESTORE в sys.dm_os_wait_stats, вы смотрите не на проблему диска; вы смотрите на проблему конкурентности и компиляции.

Вывод

• Отслеживайте соотношение между компиляциями и перекомпиляциями ежедневно.
• Тривиальные планы — ваши друзья; оставляйте простые запросы простыми.
• Используйте Хранилище запросов, чтобы остановить цикл «оптимизации» для тяжёлых запросов.