Управление кешем планов SQL Server: диагностика и решение проблем

Кеш планов SQL Server — это критически важный компонент, который хранит планы выполнения запросов для повторного использования. Однако неправильное использование этого механизма может привести к серьезным проблемам производительности. В этой статье мы рассмотрим:

• Как работают корзины (buckets) в кеше планов
• Как диагностировать проблемы с неравномерным распределением планов
• Как выявлять проблемные запросы и процедуры
• Стратегии решения проблем без использования регламентированных запросов

Часть 1: Архитектура кеша планов SQL Server

1.1 Как устроен кеш планов

Кеш планов SQL Server организован как хеш-таблица, где каждый план хранится в определенной корзине (bucket). Хеш вычисляется на основе:

• Текста запроса (для Ad-hoc и Prepared планов)
• Object ID (для планов хранимых процедур, функций, триггеров)

Согласно документации Microsoft sys.dm_exec_cached_plans, колонка bucketid представляет собой идентификатор хеш-корзины, в которой кешируется запись. Значение указывает диапазон от 0 до размера хеш-таблицы для типа кеша. Для кешей SQL Plans и Object Plans размер хеш-таблицы может достигать 10,007 в 32-разрядных системах и до 40,009 в 64-разрядных системах.

Основные компоненты кеша планов:

Компонент	Описание
Корзина (Bucket)	Контейнер для хранения планов, вычисляется по хешу
Кэш-объект (Cache Object)	Собственно план выполнения запроса
Кэш-стора (Cache Store)	Хранилище кеша (например, SQL Plans, Object Plans)

1.2 Количество корзин и управление ими

По умолчанию количество корзин зависит от версии SQL Server:

Версия SQL Server	Количество корзин
SQL Server 2008-2016	~40,009 (на 64-bit)
SQL Server 2017+	~40,009
TF 174	~160,001 (на 64-bit)

Флаг трассировки TF 174 увеличивает количество корзин с ~40,009 до ~160,001, что позволяет хранить больше планов до начала вытеснения. Как отмечается в документации Microsoft по устранению проблем с высокой загрузкой CPU, при возникновении тяжелого спинлока SOS_CACHESTORE или при частом удалении планов запросов из кеша рекомендуется включить флаг трассировки T174.

Часть 2: Диагностика состояния кеша планов

2.1 Общая статистика кеша

Первый шаг — понять общее состояние кеша планов:

SELECT 
    [name] AS CacheStoreName,
    [type] AS CacheType,
    [buckets_count] AS TotalBuckets,
    [buckets_in_use_count] AS BucketsInUse,
    CAST([buckets_in_use_count] AS FLOAT) / [buckets_count] * 100 AS UtilizationPercent,
    [buckets_avg_length] AS AvgEntriesPerBucket,
    [buckets_max_length] AS MaxEntriesInBucket,
    [hits_count] AS Hits,
    [misses_count] AS Misses,
    CASE 
        WHEN [hits_count] + [misses_count] = 0 THEN 0
        ELSE CAST([hits_count] AS FLOAT) / ([hits_count] + [misses_count]) * 100 
    END AS HitRatio
FROM sys.dm_os_memory_cache_hash_tables
WHERE [name] IN (N'SQL Plans', N'Object Plans', N'Bound Trees')
ORDER BY [name];

2.2 Распределение планов по корзинам

Этот запрос показывает, насколько равномерно планы распределены по корзинам:

WITH BucketStats AS (
    SELECT 
        bucketid,
        COUNT(*) AS PlanCount,
        SUM(usecounts) AS TotalUses,
        SUM(CAST(size_in_bytes AS BIGINT)) / 1024 AS SizeKB
    FROM sys.dm_exec_cached_plans
    GROUP BY bucketid
)
SELECT 
    bucketid,
    PlanCount,
    TotalUses,
    SizeKB,
    CASE 
        WHEN PlanCount > 100 THEN 'OVERLOADED'
        WHEN PlanCount > 20 THEN 'BALANCED'
        ELSE 'LOW'
    END AS BucketStatus
FROM BucketStats
ORDER BY PlanCount DESC;

2.3 Определение перегруженных корзин

Найдите корзины с аномальным количеством планов:

WITH BucketStats AS (
    SELECT 
        bucketid,
        COUNT(*) AS PlanCount,
        SUM(usecounts) AS TotalUses,
        SUM(CAST(size_in_bytes AS BIGINT)) / 1024 / 1024 AS SizeMB
    FROM sys.dm_exec_cached_plans
    GROUP BY bucketid
),
Stats AS (
    SELECT 
        AVG(PlanCount) AS AvgPlans,
        STDEV(PlanCount) AS StdDevPlans
    FROM BucketStats
)
SELECT 
    bs.bucketid,
    bs.PlanCount,
    bs.TotalUses,
    bs.SizeMB,
    CASE 
        WHEN bs.PlanCount > s.AvgPlans + s.StdDevPlans * 2 THEN 'CRITICAL OVERLOAD'
        WHEN bs.PlanCount > s.AvgPlans + s.StdDevPlans THEN 'OVERLOADED'
        ELSE 'NORMAL'
    END AS Status
FROM BucketStats bs
CROSS JOIN Stats s
WHERE bs.PlanCount > s.AvgPlans + s.StdDevPlans
ORDER BY bs.PlanCount DESC;

Почему пороговые значения в запросе указывают на перегрузку

Критерии OVERLOADED и CRITICAL OVERLOAD в запросе основаны на статистических принципах и внутреннем устройстве кеша. Вот ключевые причины: 

1. Кеш планов — это хеш-таблица с фиксированным количеством корзин. Кеш планов SQL Server организован как хеш-таблица для быстрого поиска. Количество корзин фиксировано (например, до 40,009 на 64-битных системах). Это значит, что все планы распределяются по этому ограниченному числу "ячеек". 
2. Неэффективный поиск при переполнении. В идеале планы должны распределяться по корзинам равномерно. Если одна или несколько корзин содержат аномально много планов, это нарушает равномерное распределение. В результате при поиске плана SQL Server вынужден просматривать длинный список в рамках одной корзины, что замедляет процесс [citation:2][citation:6]. Как отмечается в технических блогах, это «может привести к снижению эффективности поиска в кеше планов». 
3. Статистический подход к выявлению аномалий. Использование стандартного отклонения — это стандартный статистический метод для обнаружения выбросов. Корзина, значение в которой выходит за пределы AvgPlans + StdDevPlans, является статистической аномалией. Удвоенное стандартное отклонение (* 2) сигнализирует о критическом дисбалансе, который почти наверняка влияет на производительность. 
4. Существуют архитектурные ограничения на количество планов. Хотя общее количество планов в кеше может быть значительным, существуют внутренние ограничения. Например, для одного типа кеша (SQL Plans или Object Plans) лимит может составлять около 160,000 записей, независимо от доступной памяти. Локальная перегрузка корзины может стать "узким горлышком" еще до достижения этого общего лимита, что приводит к преждевременному вытеснению планов и дополнительной нагрузке на компиляцию. 

Таким образом, корзины с аномально высоким значением PlanCount — это индикатор потенциального "узкого места" производительности, которое требует дальнейшего анализа и оптимизации.

Часть 3: Диагностика проблемных запросов

3.1 Поиск планов в перегруженных корзинах

Когда вы нашли перегруженную корзину (например, bucketid = 54445), посмотрите, какие запросы там находятся:

SELECT 
    cp.bucketid,
    cp.objtype,
    cp.cacheobjtype,
    cp.usecounts,
    cp.size_in_bytes / 1024 AS SizeKB,
    cp.plan_handle,
    LEFT(qt.text, 500) AS QueryText_Preview
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) qt
WHERE cp.bucketid = 54445
ORDER BY cp.usecounts DESC;

3.2 Группировка проблемных запросов

Выявите закономерности: может быть, это одна процедура или множество похожих запросов:

SELECT 
    cp.objtype,
    COUNT(*) AS PlanCount,
    SUM(cp.usecounts) AS TotalUses,
    SUM(CAST(cp.size_in_bytes AS BIGINT)) / 1024 / 1024 AS TotalSizeMB,
    CASE 
        WHEN cp.objtype = 'Proc' THEN 
            (SELECT TOP 1 LEFT(qt.text, 100) 
             FROM sys.dm_exec_cached_plans cp2
             CROSS APPLY sys.dm_exec_sql_text(cp2.plan_handle) qt
             WHERE cp2.objtype = cp.objtype 
               AND cp2.bucketid = 54445)
        WHEN cp.objtype IN ('Adhoc', 'Prepared') THEN 
            (SELECT TOP 1 LEFT(qt.text, 100) 
             FROM sys.dm_exec_cached_plans cp2
             CROSS APPLY sys.dm_exec_sql_text(cp2.plan_handle) qt
             WHERE cp2.objtype = cp.objtype 
               AND cp2.bucketid = 54445)
        ELSE 'Mixed'
    END AS SampleQuery
FROM sys.dm_exec_cached_plans cp
WHERE cp.bucketid = 54445
GROUP BY cp.objtype
ORDER BY PlanCount DESC;

3.3 Анализ рекурсивных процедур

Одна из самых частых причин перегрузки корзин — рекурсивные вызовы хранимых процедур:

SELECT 
    cp.objtype,
    cp.usecounts,
    cp.size_in_bytes / 1024 AS SizeKB,
    qt.text AS QueryText
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) qt
WHERE qt.text LIKE '%EXEC%[_]%'
  AND qt.text NOT LIKE '%sys.%'
  AND qt.text NOT LIKE '%sp_%'
  AND cp.objtype = 'Proc'
  AND cp.bucketid IN (
      SELECT bucketid
      FROM sys.dm_exec_cached_plans
      GROUP BY bucketid
      HAVING COUNT(*) > 50 --<----
  )
ORDER BY cp.size_in_bytes DESC;

Часть 4: Причины неравномерного заполнения корзин

4.1 Проблемы, характерные для ORM-систем

Проблемы с неравномерным заполнением корзин особенно часто встречаются в системах, использующих объектно-реляционное отображение (ORM), таких как:

• Microsoft Dynamics AX (Axapta) — генерирует большое количество Ad-hoc запросов с различными параметрами
• 1C:Предприятие — использует динамическое формирование запросов с литеральными значениями
• CRM-системы (Salesforce, Microsoft Dynamics CRM, и др.) — создают множество уникальных запросов из-за кастомизаций

ORM-фреймворки по умолчанию часто генерируют запросы с литеральными значениями вместо параметров. Это приводит к тому, что каждый уникальный запрос получает свой собственный план в кеше. В результате:

• Кеш планов заполняется одноразовыми планами (usecounts = 1)
• Память расходуется неэффективно
• Возникает неравномерное распределение по корзинам
• Страдает производительность из-за частых компиляций

Как отмечается в статье о Ad-hoc запросах, в средах, где Ad-hoc запросы представляют значительный процент от общей рабочей нагрузки, количество компиляций может стать проблемой, независимо от размера сервера. Причина проста: SQL Server по умолчанию создает разные планы выполнения для каждого Ad-hoc запроса, который не имеет одинакового определения (включая значения).

4.2 Основные причины

Причина	Описание	Признаки
Рекурсивные EXEC	Процедура вызывает себя внутри цикла с разными параметрами	Много планов одной процедуры в одной корзине, разные usecounts
Непараметризованные Ad-hoc запросы	Похожие запросы с разными литералами	Много планов с usecounts = 1
Проблемы с типами данных	Несоответствие типов параметров	Похожие запросы, но разные планы
Разная длина строковых параметров	NVARCHAR(10) vs NVARCHAR(20)	Много планов одного запроса с разным размером

4.3 Запрос для выявления проблемных паттернов (работает долго)

WITH QueryPatterns AS (
    SELECT 
        cp.plan_handle,
        cp.bucketid,
        cp.usecounts,
        cp.size_in_bytes / 1024 AS SizeKB,
        qt.text AS QueryText,
        REPLACE(REPLACE(REPLACE(
            LEFT(qt.text, 200),
            ' [0-9]', ' [N]'),
            ' [0-9][0-9]', ' [N]'),
            ' [0-9][0-9][0-9]', ' [N]') AS NormalizedText
    FROM sys.dm_exec_cached_plans cp
    CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) qt
    WHERE cp.objtype = 'Prepared'
       OR cp.objtype = 'Adhoc'
)
SELECT 
    NormalizedText,
    COUNT(*) AS PlanCount,
    SUM(usecounts) AS TotalUses,
    SUM(SizeKB) AS TotalSizeKB,
    COUNT(DISTINCT bucketid) AS BucketsUsed
FROM QueryPatterns
GROUP BY NormalizedText
HAVING COUNT(*) > 5
ORDER BY PlanCount DESC;

4.4 Временные таблицы и одноразовые планы

Использование временных таблиц также может приводить к созданию множества одноразовых планов, но механизм здесь отличается от классических Ad-hoc запросов с литеральными значениями.

Как временные таблицы создают "одноразовые" планы

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

Ключевой диагностический признак — колонка optional_spid в представлении sys.dm_exec_plan_attributes. Если она содержит ненулевое значение, это указывает на то, что план привязан к конкретной сессии (SPID) и не будет переиспользоваться другими сессиями.

Пример проблемы:

-- Внешняя процедура создает #t
CREATE PROCEDURE dbo.OuterProc AS
BEGIN
    CREATE TABLE #t (id INT);
    EXEC dbo.InnerProc;  -- Внутренняя процедура тоже создает #t с тем же именем
END;

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

Что не является причиной "одноразовости"

Само по себе создание временной таблицы в процедуре не гарантирует создание одноразового плана. При соблюдении определенных условий планы могут кешироваться и переиспользоваться:

• Создание таблицы и все операции с ней выполняются в рамках одной процедуры
• Нет DDL-операций (CREATE INDEX, CREATE STATISTICS) после создания таблицы
• Таблица не создается через динамический SQL (sp_executesql)
• Нет конфликтов имен с временными таблицами в вызываемых процедурах

-- Этот план будет переиспользоваться
CREATE PROCEDURE dbo.WorkingProc AS
BEGIN
    CREATE TABLE #t (id INT);
    INSERT INTO #t VALUES (1);
    SELECT * FROM #t;  -- Все операции внутри одной процедуры
END;

Рекомендации по устранению проблемы

1. Присваивайте временным таблицам уникальные имена — избегайте простых имен вроде #t, #tmp, используйте описательные названия
2. Избегайте создания временных таблиц во вложенных процедурах — если возможно, создавайте все временные объекты в одной процедуре
3. Используйте табличные переменные вместо временных таблиц там, где допустимо — они не создают статистику и не вызывают эту проблему
4. Проверяйте наличие optional_spid в кеше планов для выявления проблемных процедур

-- Поиск планов, привязанных к конкретной сессии
SELECT 
    decp.plan_handle,
    decp.usecounts,
    pa.attribute,
    pa.value AS spid,
    qt.text
FROM sys.dm_exec_cached_plans decp
CROSS APPLY sys.dm_exec_plan_attributes(decp.plan_handle) pa
CROSS APPLY sys.dm_exec_sql_text(decp.plan_handle) qt
WHERE pa.attribute = 'optional_spid' 
  AND pa.value > 0;

Часть 5: Стратегии решения проблем

5.1 Увеличение количества корзин (TF 174)

Трассировочный флаг 174 увеличивает количество корзин с ~40,009 до ~160,001.

Проверка статуса:

DBCC TRACESTATUS(174, -1);

Включение:

DBCC TRACEON(174, -1);
-- Или добавьте -T174 как параметр запуска SQL Server

Влияние на количество корзин:

SELECT 
    [name],
    [buckets_count]
FROM sys.dm_os_memory_cache_hash_tables
WHERE [name] IN (N'SQL Plans', N'Object Plans');

5.2 Оптимизация параметризации

Когда включать Optimize for Ad Hoc Workloads

Опция optimize for ad hoc workloads рекомендуется к включению в следующих случаях:

• Большое количество Ad-hoc запросов с однократным использованием (usecounts = 1)
• Значительный процент памяти занят одноразовыми планами (более 50%)
• Наблюдается высокое потребление Stolen Memory и снижение Page Life Expectancy

Как указано в документации Alibaba Cloud, при включении этой опции для впервые выполняемых Ad-hoc SQL-запросов кешируется только легковесный заглушка (Stub), и только при втором выполнении сохраняется полный план.

Включение:

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'optimize for ad hoc workloads', 1;
RECONFIGURE;

Когда включать Forced Parameterization

Принудительная параметризация рекомендуется в следующих случаях:

• Невозможно изменить код приложения (особенно актуально для закрытых ORM-систем)
• Большое количество похожих запросов с разными литеральными значениями
• Высокая нагрузка на CPU из-за постоянных компиляций

"Если все пойдет не так, отключить эту опцию легко".

Включение:

ALTER DATABASE [YourDatabase] SET PARAMETERIZATION FORCED;

По умолчанию (простая параметризация) SQL Server часто создает отдельный план для каждого уникального запроса с разными значениями (например, WHERE ProductID = 1 и WHERE ProductID = 2). Это приводит к загрязнению кеша планов множеством одноразовых планов и высокой нагрузке на CPU из-за постоянных компиляций. 

Принудительная параметризация преобразует эти запросы в параметризованную форму (WHERE ProductID = @0). Один план может использоваться для множества разных значений, что резко снижает количество планов в кеше и частоту их компиляции. Это особенно полезно для баз данных с большим количеством ad-hoc запросов, например, от ORM-систем, которые сложно изменить на уровне приложения.

Риск Sniffing параметров и неоптимальных планов - это самый важный нюанс. Если один параметризованный план используется для всех значений, он может быть оптимален для одних и крайне неэффективен для других.

5.3 Оптимизация запросов

Как отмечается в руководстве по архитектуре обработки запросов, использование параметров, включая маркеры параметров в приложениях ADO, OLE DB и ODBC, может увеличить повторное использование планов выполнения. Использование параметров или маркеров параметров для хранения значений, вводимых конечными пользователями, безопаснее, чем конкатенация значений в строку, которая выполняется с помощью метода API доступа к данным, инструкции EXECUTE или хранимой процедуры sp_executesql.

Использование sp_executesql вместо динамического SQL:

❌ Плохо:

DECLARE @sql NVARCHAR(MAX) = 'SELECT * FROM Users WHERE Id = ' + CAST(@UserId AS VARCHAR);
EXEC sp_executesql @sql;

✅ Хорошо:

DECLARE @sql NVARCHAR(MAX) = 'SELECT * FROM Users WHERE Id = @UserId';
EXEC sp_executesql @sql, N'@UserId INT', @UserId = @UserId;

Использование OPTION (RECOMPILE) как временное решение:

EXEC [dbo].[ProblematicProcedure] @Param1, @Param2 OPTION (RECOMPILE);

5.4 План-гайды для фиксации планов

Для критических запросов можно зафиксировать план:

EXEC sp_create_plan_guide 
    @name = N'Guide_ProcedureName',
    @stmt = N'EXEC [dbo].[ProcedureName] @Param1, @Param2',
    @type = N'OBJECT',
    @module_or_batch = N'[dbo].[ProcedureName]',
    @params = NULL,
    @hints = N'OPTION (OPTIMIZE FOR UNKNOWN)';

Фиксация плана для Ad-hoc запроса по его идентификатору в кеше

В отличие от хранимых процедур, для разовых (Ad-hoc) запросов удобнее фиксировать план, уже находящийся в кеше. Это делается через системную процедуру sys.sp_create_plan_guide_from_handle, которая использует plan_handle и смещение оператора для создания плана-гайда.

Шаг 1. Найти plan_handle запроса

SELECT
    qs.plan_handle,
    qs.statement_start_offset,
    st.text AS query_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
WHERE st.text LIKE N'%ваш_запрос%';

Шаг 2. Создать план-гайд из plan_handle

DECLARE @plan_handle VARBINARY(64);
DECLARE @offset INT;

-- Получаем plan_handle и смещение
SELECT
    @plan_handle = qs.plan_handle,
    @offset = qs.statement_start_offset
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
WHERE st.text LIKE N'%ваш_запрос%';

-- Создаем план-гайд
EXECUTE sys.sp_create_plan_guide_from_handle
    @name = N'Guide_AdHoc_Query',
    @plan_handle = @plan_handle,
    @statement_start_offset = @offset;

Валидация и управление

-- Проверка корректности плана-гайда
SELECT plan_guide_id, msgnum, severity, state, message
FROM sys.plan_guides
CROSS APPLY fn_validate_plan_guide(plan_guide_id);

-- Удаление плана-гайда
EXEC sp_control_plan_guide @operation = N'DROP', @name = N'Guide_AdHoc_Query';

Важно: Если пакет содержит несколько операторов, для каждого из них нужно создавать отдельный план-гайд с указанием смещения statement_start_offset.

Часть 6: Роль статистик в управлении кешем планов

6.1 Почему важно поддерживать статистики в актуальном состоянии

Актуальные статистики — это фундамент для построения эффективных планов выполнения. Когда статистики устаревают:

• Оптимизатор запросов принимает неверные решения о кардинальности
• Создаются неоптимальные планы, которые затем кешируются
• Это может привести к множеству различных планов для одного запроса
• Кеш планов загрязняется неэффективными планами

Согласно документации Microsoft, когда опция базы данных AUTO_UPDATE_STATISTICS установлена в ON, запросы перекомпилируются, когда они обращаются к таблицам или индексированным представлениям, статистики по которым были обновлены или чья кардинальность значительно изменилась с момента последнего выполнения.

6.2 Рекомендации по обслуживанию статистик

• Держите AUTO_UPDATE_STATISTICS включенной (по умолчанию)
• Если таблицы большие, настройте адаптивное обновление статистики
• Для больших таблиц настройте порог обновления статистик с помощью трассировочного флага 2371
• Регулярно обновляйте статистики в периоды обслуживания
• Используйте sys.dm_db_stats_properties для мониторинга состояния статистик

SELECT 
    OBJECT_NAME(s.object_id) AS TableName,
    s.name AS StatisticsName,
    sp.last_updated,
    sp.rows,
    sp.rows_sampled,
    sp.modification_counter
FROM sys.stats s
CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) sp
WHERE sp.last_updated IS NOT NULL
ORDER BY sp.last_updated;

Часть 7: Очистка кеша планов

7.1 Очистка конкретного плана

DBCC FREEPROCCACHE (plan_handle);

Очистка кеша планов применяется в следующих случаях:

• Устранение последствий Sniffing параметров — удаление неоптимального плана, чтобы при следующем выполнении запрос скомпилировался заново с актуальными параметрами
• Освобождение памяти — удаление "мусорных" планов (usecounts = 1), которые занимают место в кеше и вытесняют полезные планы
• После изменения статистик или индексов — чтобы планы были перекомпилированы с учетом новых данных
• При перегрузке корзин — когда аномальное количество планов в одной корзине замедляет поиск
• После изменения схемы БД — удаление устаревших планов, ссылающихся на измененные объекты

Важно: Очистка кеша приводит к временному увеличению CPU-нагрузки из-за перекомпиляции всех запросов .

7.2 Очистка всех планов конкретной процедуры

DECLARE @plan_handle VARBINARY(64);
DECLARE @count INT = 0;

DECLARE cur CURSOR FOR
SELECT DISTINCT cp.plan_handle
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) qt
WHERE qt.objectid = OBJECT_ID('[dbo].[YourProcedureName]');

OPEN cur;
FETCH NEXT FROM cur INTO @plan_handle;

WHILE @@FETCH_STATUS = 0
BEGIN
    DBCC FREEPROCCACHE (@plan_handle);
    SET @count = @count + 1;
    FETCH NEXT FROM cur INTO @plan_handle;
END;

CLOSE cur;
DEALLOCATE cur;

PRINT N'Удалено планов: ' + CAST(@count AS NVARCHAR(10));

7.3 Очистка всех планов в конкретной корзине

DECLARE @plan_handle VARBINARY(64);
DECLARE @count INT = 0;
DECLARE @bucketid INT = 54445;

DECLARE cur CURSOR FOR
SELECT cp.plan_handle
FROM sys.dm_exec_cached_plans cp
WHERE cp.bucketid = @bucketid;

OPEN cur;
FETCH NEXT FROM cur INTO @plan_handle;

WHILE @@FETCH_STATUS = 0
BEGIN
    DBCC FREEPROCCACHE (@plan_handle);
    SET @count = @count + 1;
    FETCH NEXT FROM cur INTO @plan_handle;
END;

CLOSE cur;
DEALLOCATE cur;

PRINT N'Удалено планов: ' + CAST(@count AS NVARCHAR(10));

7.4 Полная очистка кеша планов (крайняя мера)

-- Удаляет все планы из кеша SQL Plans
DBCC FREESYSTEMCACHE ('SQL Plans');

-- Удаляет все планы из кеша Object Plans
DBCC FREESYSTEMCACHE ('Object Plans');

-- Удаляет все планы из кеша (всех типов)
DBCC FREEPROCCACHE;

Важно: Очистка кеша планов приводит к временному увеличению CPU-нагрузки из-за необходимости перекомпиляции всех запросов. Как отмечается в документации Microsoft, после очистки кеша планов все последующие запросы должны быть перекомпилированы. Пик перекомпиляций временно увеличивает использование CPU и снижает пропускную способность запросов до тех пор, пока кеш планов не заполнится снова.

Часть 8: Мониторинг и превентивные меры

8.1 Мониторинг накопления планов

SELECT 
    GETDATE() AS CheckTime,
    cp.objtype,
    COUNT(*) AS PlanCount,
    SUM(CAST(cp.size_in_bytes AS BIGINT)) / 1024 / 1024 AS TotalSizeMB,
    MIN(cp.usecounts) AS MinUses,
    MAX(cp.usecounts) AS MaxUses,
    AVG(cp.usecounts) AS AvgUses
FROM sys.dm_exec_cached_plans cp
WHERE cp.objtype IN ('Proc', 'Adhoc', 'Prepared')
GROUP BY cp.objtype
ORDER BY cp.objtype;

8.2 Мониторинг использования корзин

SELECT 
    GETDATE() AS CheckTime,
    COUNT(DISTINCT bucketid) AS BucketsUsed,
    COUNT(*) AS TotalPlans,
    AVG(PlanCount) AS AvgPlansPerBucket,
    MAX(PlanCount) AS MaxPlansInBucket
FROM (
    SELECT bucketid, COUNT(*) AS PlanCount
    FROM sys.dm_exec_cached_plans
    GROUP BY bucketid
) AS BucketStats;

8.3 Оповещение при перегрузке корзин

IF EXISTS (
    SELECT 1
    FROM sys.dm_exec_cached_plans
    GROUP BY bucketid
    HAVING COUNT(*) > 100
)
BEGIN
    RAISERROR('WARNING: Overloaded buckets detected in plan cache!', 16, 1);
END;

Выводы

1. Диагностируйте перед решением — сначала определите проблему, а затем выбирайте решение
2. Правильная параметризация — основа здорового кеша планов
3. Рекурсивные EXEC — частая причина перегрузки корзин
4. TF 174 — эффективное решение при недостатке корзин
5. Автоматизация — настройте мониторинг для превентивного выявления проблем
6. Актуальные статистики — фундамент для качественных планов выполнения

Рекомендации по настройке

Настройка	Рекомендация	Примечание
optimize for ad hoc workloads	Включить	Снижает нагрузку на кеш от разовых запросов
forced parameterization	Включить (осторожно)	Улучшает повторное использование планов
TF 174	Включить	Увеличивает количество корзин с 40K до 160K
max server memory	Мониторить	Убедитесь, что кеш планов не голодает
Query Store	Включить	Позволяет легко фиксировать планы
AUTO_UPDATE_STATISTICS	Включить	Обеспечивает актуальность статистик

---