Решение проблем с большим кешем запросов 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-tool for creating personal accounts on the site

The essence of the approach and the history of the creation of Falcon Space
Web platform for creating personal accounts

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

You can develop on your own or collaborate with us on Falcon Space web development using only SQL and HTML.
See examples with SQL code
Platform documentation
Working on MS SQL Server
Component demo stand
At the stand you can see various components in action - tables, forms, modal windows, diagrams, a map, etc.
Solution demo site
Basic solutions that can be flexibly adapted for yourself - change the appearance, business logic and even the structure of the database.
Discuss the project
Ask the initial questions about the project that concern you right now. We will advise you for free and recommend the best solution.