Выметаем ядовитые планы из кэша SQL Server

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

Вы исправили ad-hoc запросы, но ваш сервер всё ещё «колбасит»? В этой статье я расскажу, почему даже идеальная параметризация может привести к «токсичности повторного использования» (reuse toxicity) и массовым скачкам ЦП.

В двух словах

• Параметризованное засорение (Parameterized Pollution): Засорение кэша — это не только «слишком много планов», но и повторно используемый «неправильный план». 
• Токсичность повторного использования (Reuse Toxicity): План, оптимизированный для одной строки, принудительно применяется к набору из миллиона строк, убивая производительность.
• Нестабильность планов (Plan Instability): Резкие колебания между min_worker_time и max_worker_time указывают на войну, вызванную Sniffing'ом параметров.
• Решение: Используйте Query Store, идентификацию по query_hash и оптимизацию PSP в SQL Server 2022.

В предыдущей части этой серии мы очистили кэш от «мусорных» планов. Но горькая правда в том, что чистый код не гарантирует чистый кэш. Даже когда ваше приложение на 100% параметризовано, SQL Server всё ещё может страдать от другого вида засорения — логической токсичности. Это происходит, когда движок повторно использует неоптимальный план выполнения, потому что во время компиляции он «унюхал» (sniffed) непредставительный параметр.

Диагностика: скрытая война внутри кэша

Parameter Sniffing: Когда процедура или запрос выполняются в первый раз, SQL Server смотрит на значения параметров, чтобы построить наиболее эффективный план. Если это первое выполнение было для «редкого» значения, он создаёт план, который является ядовитым для «обычных» значений, используемых позже.

Токсичность повторного использования кэша (Cache Reuse Toxicity): Здесь проблема не в раздувании памяти, а в разогреве ЦП. У вас есть один план в кэше (это хорошо для памяти), но он неправильный план для 90% ваших пользователей.

• query_hash — идентифицирует логически идентичные запросы.
• query_plan_hash — идентифицирует точный физический план выполнения, который используется.

Если вы видите один query_hash, сопоставленный с высоким max_worker_time, в то время как min_worker_time низкое, вы наблюдаете нестабильность планов (Plan Instability).

Запрос: обнаружение токсичного повторного использования планов

Выполните этот запрос, чтобы найти запросы, которые работают непоследовательно. Большие расхождения между минимальным и максимальным временем ЦП — это явный признак Parameter Sniffing.

-- WOW-запрос: выявление нестабильности планов и токсичного повторного использования
SELECT TOP 20
    qs.query_hash,
    qs.execution_count,
    CAST(qs.min_worker_time / 1000.0 AS DECIMAL(10,2)) AS MinCPUMs,
    CAST(qs.max_worker_time / 1000.0 AS DECIMAL(10,2)) AS MaxCPUMs,
    CAST((qs.total_worker_time / qs.execution_count) / 1000.0 AS DECIMAL(10,2)) AS AvgCPUMs,
    st.text AS QueryText,
    qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
WHERE qs.execution_count > 5
AND (qs.max_worker_time / (qs.min_worker_time + 1)) > 10 -- Максимум в 10 раз превышает минимум
ORDER BY qs.max_worker_time DESC;

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

Исправление: стратегии стабилизации

Исправление токсичного повторного использования требует выхода за рамки простой «параметризации». Вам нужно принудительно обеспечить стабильность:

• Хранилище запросов (Query Store): Используйте его, чтобы найти «регрессировавшие запросы» и принудительно применить последний известный хороший план.
• Оптимизация PSP (Parameter Sensitive Plan Optimization): Если вы работаете на SQL Server 2022, движок теперь изначально обрабатывает несколько планов для разных размеров параметров.
• Подсказка RECOMPILE: Используйте OPTION (RECOMPILE) только для очень изменчивых запросов, где стоимость компиляции ниже, чем стоимость плохого плана.
• Руководства планов (Plan Guides): Принудительно задайте конкретное использование соединения или индекса, чтобы предотвратить «творческий подход» оптимизатора.

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

За 25 лет настройки производительности я понял, что Parameter Sniffing является причиной №1 аварийных скачков ЦП. Не просто очищайте кэш с помощью DBCC FREEPROCCACHE — это пластырь, который часто ухудшает ситуацию, поскольку вызывает шторм компиляций.

Вместо этого посмотрите на Хранилище запросов (Query Store). Если вы видите запрос с огромным скачком «продолжительности», но тем же «идентификатором плана», у вас токсичное повторное использование. Если вы видите несколько идентификаторов планов для одного идентификатора запроса, у вас нестабильность. Используйте принудительное применение планов (Forced Plans), чтобы стабилизировать производственную среду, пока вы переписываете логику.

Вывод: производительность — это согласованность

Высокое количество выполнений + высокий Max CPU = Токсичное повторное использование.

Цель состоит не только в том, чтобы сэкономить место в кэше планов, но и в том, чтобы гарантировать, что когда запрос выполняется, он выполняется с планом, который соответствует обрабатываемым данным. Если ваше максимальное время работы (max worker time) значительно превышает минимальное (min worker time), ваш кэш не просто загрязнён — он скомпрометирован.