Проверка кэша SQL Server на засорение одноразовыми планами

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

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

В двух словах

• Загрязнение кэша планов (Plan Cache Pollution) происходит, когда непараметризованные запросы создают тысячи бесполезных одноразовых планов выполнения. 
• Вымывание памяти (Memory Starvation): Эти планы крадут пространство у буферного пула, вытесняя данные из памяти и увеличивая ввод-вывод. 
• Исправление: Используйте параметризацию, sp_executesql или включите настройку «Optimize for Ad Hoc Workloads». 
• SQL-запрос «Wow!»: Определите, какие именно ad-hoc запросы прямо сейчас засоряют ваш кэш. 

За 25 лет настройки производительности я видел, как миллионы долларов, вложенных в оборудование, оказывались бесполезными из-за плохих привычек кодирования. Один из самых распространённых молчаливых убийц — засорение кэша планов. Это технический эквивалент заполнения библиотеки идентичными книгами, в которых меняется только одна страница. Каждый раз, когда ваше приложение отправляет запрос вроде WHERE Id = 1, а затем WHERE Id = 2 без параметризации, SQL Server обрабатывает их как совершенно новую логику.

Диагностика: что убивает вашу оперативную память?

Кэш планов (Plan Cache): Это выделенная область в памяти SQL Server (часть memory clerk CACHESTORE_SQLCP), предназначенная для хранения планов выполнения. Её цель — экономить такты ЦП за счёт повторного использования планов для часто выполняемых запросов.

Одноразовые планы (Single-Use Plans): Когда ORM или разработчик конкатенируют строки для построения запросов, SQL Server генерирует уникальный ad-hoc план для каждого отдельного литерального значения.

SELECT * FROM Users WHERE Id = 1 → План A скомпилирован.
SELECT * FROM Users WHERE Id = 2 → План B скомпилирован.

Эти планы остаются в памяти, но никогда не будут использованы снова. Это приводит к раздуванию кэша (Cache Bloat): тысячи планов размером 16–128 КБ потребляют гигабайты оперативной памяти, которая должна использоваться для кэширования реальных страниц данных (буферный пул).

Запрос: определите загрязнителей

Выполните этот «Wow!»-запрос, чтобы увидеть топ-50 ad-hoc одноразовых планов, которые прямо сейчас занимают место в вашем кэше.

-- Диагностика загрязнения кэша планов SQL Server
SELECT TOP 50
    cp.usecounts,
    cp.size_in_bytes / 1024 AS SizeKB,
    st.text
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
WHERE cp.objtype = 'Adhoc'
AND cp.usecounts = 1
ORDER BY cp.size_in_bytes DESC;

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

Исправление: возвращаем буферный пул

Чтобы решить эту проблему, мы должны перейти от ad-hoc рабочих нагрузок к параметризованным. Вот ваше основное оружие:

• Параметризация (Parameterization): Убедитесь, что ваше приложение использует @Parameter вместо литеральных значений.
• sp_executesql: Замените EXEC(@sql) на sp_executesql, чтобы способствовать повторному использованию планов.
• Optimize for Ad Hoc Workloads: Эта настройка на уровне сервера указывает SQL Server при первом появлении запроса сохранять только небольшой «заглушку плана» (plan stub), экономя память до тех пор, пока запрос действительно не будет использован повторно.

-- Включение Optimize for Ad Hoc Workloads
EXEC sys.sp_configure N'show advanced options', N'1';
RECONFIGURE;
EXEC sys.sp_configure N'optimize for ad hoc workloads', N'1';
RECONFIGURE;

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

По моему опыту, принудительная параметризация на уровне базы данных может быть палкой о двух концах. Хотя она агрессивно останавливает загрязнение, она может вызвать массовые проблемы со sniffing'ом параметров.

Моё правило: Если ваши одноразовые планы превышают 30% от общего размера кэша, сначала включите «Optimize for Ad Hoc Workloads». Это низкорискованный шаг с высоким вознаграждением, который немедленно снимает нагрузку на память без изменения поведения планов запросов.

Вывод: миллисекунды имеют значение

Ваш буферный пул голодает, потому что бесполезные планы воруют память.

Каждый скомпилированный и никогда не использованный повторно план выполнения — это тройная потеря: потраченные впустую такты ЦП на компиляцию, потраченная впустую память на хранение и потраченный впустую ввод-вывод, потому что страницы данных были вытеснены из буферного пула, чтобы освободить место для этого мусора. Остановите утечку сегодня.