Analyse du cache de plans – Scripts Center

Cette requête permet d’obtenir l’occupation du cache de plans pour les requêtes adhoc ou préparées, pour les plans utilisés une seule ou plusieurs fois.

/*-------------------------------------------------------------------
[SCRIPT] cached plans repartition  
[DATABASE] master
[DESCRIPTION] Plans used one / many times
[MAJ PAR] DATAFLY - Arian Papillon
-------------------------------------------------------------------*/
SELECT   cp.cacheobjtype
       , cp.objtype
       , CASE WHEN cp.usecounts = 1 THEN 'Only one'
              ELSE 'Many'
         END AS usecounts
       , SUM(CAST(cp.size_in_bytes AS BIGINT) / 1024) AS [Plan Size in KB]
       , COUNT(*) AS Number
FROM     sys.dm_exec_cached_plans AS cp WITH ( NOLOCK )
WHERE    cp.cacheobjtype = N'Compiled Plan'
         AND cp.objtype IN ( N'Adhoc', N'Prepared' )
GROUP BY cp.cacheobjtype
       , cp.objtype
       , CASE WHEN cp.usecounts = 1 THEN 'Only one'
              ELSE 'Many'
         END
ORDER BY CASE WHEN cp.usecounts = 1 THEN 'Only one'
              ELSE 'Many'
         END ASC
       , objtype
       , SUM(CAST(cp.size_in_bytes AS BIGINT) / 1024) DESC
OPTION ( RECOMPILE );

/*-------------------------------------------------------------------

[SCRIPT] cached plans repartition

[DATABASE] master

[DESCRIPTION] Plans used one / many times

[MAJ PAR] DATAFLY - Arian Papillon

-------------------------------------------------------------------*/

SELECT cp.cacheobjtype

, cp.objtype

, CASE WHEN cp.usecounts = 1 THEN 'Only one'

ELSE 'Many'

END AS usecounts

, SUM(CAST(cp.size_in_bytes AS BIGINT) / 1024) AS [Plan Size in KB]

, COUNT(*) AS Number

FROM sys.dm_exec_cached_plans AS cp WITH ( NOLOCK )

WHERE cp.cacheobjtype = N'Compiled Plan'

AND cp.objtype IN ( N'Adhoc', N'Prepared' )

GROUP BY cp.cacheobjtype

, cp.objtype

, CASE WHEN cp.usecounts = 1 THEN 'Only one'

ELSE 'Many'

END

ORDER BY CASE WHEN cp.usecounts = 1 THEN 'Only one'

ELSE 'Many'

END ASC

, objtype

, SUM(CAST(cp.size_in_bytes AS BIGINT) / 1024) DESC

OPTION ( RECOMPILE );