Решение проблем с большим кешем запросов SQL Server

Если у вас много непараметризированных запросов, то кеш запросов будет содержать много одноразовых планов запросов. 

Просмотреть какой в кеше % adhoc запросов:

SELECT T.*, cast(T.[Total Plans - USE Count 1]*1.0/nullif(t.[Total Plans],0) *100  AS decimal(5,2)) Percentage
FROM
(
       SELECT objtype AS [CacheType]
                    , count_big(*) AS [Total Plans]
                    , sum(cast(size_in_bytes as decimal(18,2)))/1024/1024 AS [Total MBs]
                    , sum(cast((CASE WHEN usecounts = 1 THEN size_in_bytes ELSE 0 END) as decimal(18,2)))/1024/1024 AS [Total MBs - USE Count 1]
                    , sum(CASE WHEN usecounts = 1 THEN 1 ELSE 0 END) AS [Total Plans - USE Count 1]
       FROM sys.dm_exec_cached_plans cp
       WHERE cp.cacheobjtype = N'Compiled Plan'
       AND cp.objtype IN (N'Adhoc', N'Prepared') -- adhoc (произвольный запрос), prepared (параметризованный)
       GROUP BY objtype
) T
ORDER BY [Total MBs - USE Count 1] DESC
OPTION (RECOMPILE);  --план не будет сохраняться, эту опцию желательно использовать для adhoc запросов

Просмотр основных запросов в кеше по размеру:

-- выдать 100 первых запросов раздувающих кэш по размеру
SELECT top 100 *
FROM sys.dm_exec_cached_plans AS cp WITH (NOLOCK)
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
WHERE cp.cacheobjtype = N'Compiled Plan'
AND cp.objtype = N'Adhoc'
AND cp.usecounts = 1
ORDER BY cp.size_in_bytes DESC OPTION (RECOMPILE);

Как решить проблему раздувания кеша 

USE master
EXEC master.dbo.sp_configure 'show advanced options', 1
RECONFIGURE
EXEC master.dbo.sp_configure 'optimize for ad hoc workloads', 1
RECONFIGURE WITH OVERRIDE
GO

Либо это сделать через Свойства сервера в Дополнительно Optimize for ad hoc workloads = true 

Как очистить весь процедурный кэш для экземпляра:

DBCC FREEPROCCACHE;

Дополнительно:

Насколько полезной была статья?

Что еще посмотреть по SQL Server

SQL Server. Оптимизация запросов SQL. MS SQL Медленно работают запросы SELECT

SQL Server сильно грузит процессор. Проблемы с CPU у процесса SQLServer. Оптимизация запросов SQL по CPU. Подробное руководство

Как найти медленные проблемные запросы SQL Server. Оптимизация быстродействия SQL Server

SQL Server. Работа с индексами через запросы SQL

Где ставить индексы SQL Server

SQL Server. Оптимизация SQL запросов, план запроса в SQL Server. Советы по оптимизация запросов SQL

Инструмент для анализа производительности запросов SQL Server Query Store

SQL Server Как найти запросы с проблемами (warning) в плане выполнения запроса (SQL Plan)

Выгода от использования Falcon Space

В 2-3 раза экономнее и быстрее, чем заказная разработка
Более гибкая, чем коробочные решения и облачные сервисы
Используйте готовые решения и изменяйте под свои потребности
Нужна бесплатная консультация?
Планируете делать веб-проект?
Сайт использует Cookie. Правила конфиденциальности OK