Решение проблем с большим кешем запросов 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;
Дополнительно:
Additional
SQL. Optimization of SQL queries. MS SQL queries are slow
Problems with the processor SQL Server CPU. CPU issues. Processor query Optimization
Procedure for tracking problematic SQL Server queries
SQL Server. Работа с индексами через запросы SQL
Где ставить индексы SQL Server
Оптимизация SQL запросов и план запроса SQL Server. Советы по оптимизация запросов SQL
Инструмент для анализа производительности запросов SQL Server Query Store
SQL Server Как найти запросы с проблемами (warning) в плане выполнения запроса (SQL Plan)
SQL-tool for creating personal accounts on the site

Falcon Space Platform
This is a reduction in the cost of ownership
at the expense of fewer people to support
This is a quick change
while using the program
This is a modern interface
full adaptation for mobile devices
MS SQL web applications. Affiliate program for developers and web studios
Platform documentation
