Решение проблем с большим кешем запросов 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

Это снижение стоимости владения

за счет меньшего количества людей для поддержки и узкого стека разработки. Про снижение стоимости владения продуктом

Это быстрое внесение изменений

по ходу эксплуатации программы. Как создается функционал на платформе

Это простой удобный интерфейс

адаптация под мобильные устройства. Про юзабилити платформы

Нужна бесплатная консультация?
Получить оценку проекта
Создайте концепцию проекта на основе нашего шаблона и получите оценку проекта в виде КП.
Демо-сайт решений
Базисные решения, которые можно гибко адаптировать под себя: менять внешний вид, бизнес-логику и даже структуру базы данных.