_01_СЕРВЕР_КЕШ_планов_1_1

-- _01_СЕРВЕР_КЕШ_планов_1_1

-- http://skproj.ru/pochemu-dbcc-freeproccache-pomogaet/?doing_wp_cron=1618856089.8024010658264160156250
-- Почему dbcc freeproccache помогает?
SELECT text, cp.objtype, cp.size_in_bytes
    FROM sys.dm_exec_cached_plans AS cp
    CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
    WHERE cp.cacheobjtype = N'Compiled Plan'
    AND cp.objtype IN(N'Adhoc', N'Prepared')
    AND cp.usecounts = 1
    ORDER BY cp.size_in_bytes DESC
    OPTION (RECOMPILE);

-- Adhoc Значительно больше Prepared

-- Раздуло кэш SQL? Лечимся народными средствами.
-- http://eazybi.blogspot.com/2015/07/sql.html

--Чтобы понять есть ли проблема с 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 раз очень высокий 76.51%
--CacheType Total Plans Total MBs   Total MBs - USE Count 1 Total Plans - USE Count 1   Percentage
--Adhoc     562         65.914062   40.703125               430                         76.51
--Prepared  55          23.656250   7.687500                20                          36.36



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






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