Раздуло кэш SQL? Лечимся народными средствами.

Если при разработке приложений не пользоваться хранимыми процедурами и параметризованными запросами, чем грешат большинство разработчиков, посылая произвольные (Adhoc) запросы серверу

То рано или поздно можно столкнуться с проблемой раздутого процедурного кэша.

Подробнее о механизмах кэширования тут

1.Проверяем есть ли проблемы с процедурным кэшем.

Чтобы понять есть ли проблема с 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 запросов

Из результатов запроса понятно что процент  Adhoc  запросов  которые выполнялись только 1 раз очень высокий 82%

Ниже разница  в планах  adhoc и prepared планах

-- выдать 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

Использование этого параметра не очищает процедурный кэш, его очистку необходимо выполнить вручную, есть несколько вариантов:
1.
-- очистить весь процедурный кэш для экземпляра
DBCC FREEPROCCACHE;

2. очистить для базы
--Идентификатор базы
DECLARE @intDBID INTEGER
SET @intDBID = (SELECT dbid FROM master.dbo.sysdatabases WHERE name = 'IntegrationDB')

--select @intDBID
--очищаем процедурный кэш для базы
DBCC FLUSHPROCINDB (@intDBID)

3. удалить конкретные планы вручную
-- получаем указатель на план
SELECT cp.plan_handle, st.[text]
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st
WHERE [text] LIKE N'%/* GetOnlineSearchResultsMonday %';

-- удаляем план из кэша
DBCC FREEPROCCACHE (0x05000800F7BA926C40C15055070000000000000000000000);

Параметр optimize for ad hoc workloads используется для повышения эффективности кэширования планов рабочих нагрузок, содержащих много отдельных нерегламентированных пакетов. Если этот параметр имеет значение 1, компонент Database Engine при первой компиляции пакета, сохраняет в кэше планов небольшую скомпилированную заглушку плана Compiled Plan Stub, а не полный откомпилированный план. Это несколько снижает требования к памяти, так как кэш планов не заполняется скомпилированными, не используемыми повторно планами.

Скомпилированная заглушка плана принадлежит к объектам cacheobjtypes, которые можно просмотреть в представлении каталога sys.dm_exec_cached_plans. У каждой заглушки есть уникальный дескриптор SQL и дескриптор плана. Со скомпилированной заглушкой плана не связан план выполнения. Запрос по дескриптору плана не вернет XML-код Showplan

Для чего это надо ?

Каждая инструкция (T-SQL, SP и другие) сохраняет свой план выполнения в кэше, для дальнейшего использования. По этой причине, большое число планов хранится в системе и множество из них могут использоваться только 1 раз. Это в пустую тратит ресурсы сервера.

Чтобы не сохранялись в кэше планы запросов которые выолняются 1 раз придумали этот параметр сервера.

В загруженной системе легко может быть более миллиона разных инструкций, представьте сколько ресурсов вы тратите в пустую. Благодаря параметру optimize for ad hoc workloads  можно избежать хранения ненужных планов.

Если посмотреть на результаты  внизу после включения опции optimize for ad hoc workloads можно увидеть эти самые заглушки их размер size_in_bytes минимальный.

--выдать 100 первых закешированных планов

SELECT top 100 *

FROM sys.dm_exec_cached_plans AS cp WITH (NOLOCK)

CROSS APPLY sys.dm_exec_sql_text(plan_handle)

ORDER BY cp.size_in_bytes DESC OPTION (RECOMPILE)

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

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

SELECT  * FROM [Таблица] WHERE [Поле1] = 'aaaa'

Подробно  о простой параметризации тут  

Для включения принудительной параметризации нужно следовать рекомендациям MS

Рекомендации по использованию принудительной параметризации

Устанавливая для параметра PARAMETERIZATION значение FORCED, примите во внимание следующие сведения.

• Принудительная параметризация, в сущности, преобразует литеральные константы в запросе в параметры при компиляции запроса. Следовательно, оптимизатор запросов может выбирать не самые оптимальные планы для запросов. В частности, уменьшается вероятность того, что оптимизатор запросов сопоставит запрос с индексированным представлением или индексом по вычисляемому столбцу. Он может также выбирать не самые оптимальные планы для запросов, ориентированных на секционированные таблицы или распределенные секционированные представления. Принудительная параметризация не должна использоваться в средах, в значительной степени опирающихся на индексированные представления и индексы по вычисляемым столбцам. Параметр PARAMETERIZATION FORCED должен использоваться только опытными администраторами баз данных и лишь после того, как будет определено, что такое использование не повредит производительности.
• Распределенные запросы, ссылающиеся на более чем одну базу данных, пригодны для принудительной параметризации, если параметр PARAMETERIZATION установлен на FORCED в базе данных, в контексте которой выполняется запрос.
• Установка параметра PARAMETERIZATION на FORCED производит очистку всех планов запросов из кэша планов в базе данных за исключением тех, которые компилируются, перекомпилируются или выполняются в настоящий момент. Планы для запросов, которые компилируются или выполняются в момент изменения настроек, параметризуются при следующем выполнении запроса.
• Настройка параметра PARAMETERIZATION выполняется в оперативном режиме и не требует монопольных блокировок на уровне базы данных.
• Принудительная параметризация отключается (устанавливается в SIMPLE), если уровень совместимости базы данных SQL Server установлен в 80 или если база данных на экземпляре более ранней версии присоединена к экземпляру SQL Server 2005 или более поздней версии.
• Текущая настройка параметра PARAMETERIZATION сохраняется при повторном присоединении или восстановлении базы данных.

P/S На самом деле для серверов с большим объемом оперативной памяти проблема adhoc не очень актуальна (см ниже), 

т.к  размер процедурного кэша ограничен обычно ограничен некоторым значением приблизительно 10% от общего объем памяти. И сервер сам удаляет  из кэша более дешевые планы заполняя их новыми. 

Поэтому особо не стоит напрягаться по этому поводу.
А вот принудительная параметризация иногда работает как кнопка турбо, но прежде чем ее использовать необходимо проверить ее работу в тестовом окружении.

Однако  повторюсь лучшей практикой остается использование хранимых процедур и параметризованных запросов.