février
2011
Un article de zinzineti
Quelques requêtes pour auditer le cache de procédures
———————————————————————-
–> Requêtes dont les plans d’exécutions ne sont pas réutilisés
———————————————————————-
Ces requêtes qui ne réutilisent pas leurs plans d’exécution vont à chaque exécution
créer un nouveau plan, ce qui aura pour conséquence d’encombrer le cache de procédure et de dégrader les performances. il faut donc réécrire ces requêtes.
SELECT TOP(50) [text]
, size_in_bytes/1024 AS [Taille_Ko]
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
WHERE cacheobjtype = 'Compiled Plan'
AND objtype = 'Adhoc' AND usecounts = 1
ORDER BY [Taille_Ko] DESC
———————————————————————-
–> Taille mémoire allouée aux objets du cache
———————————————————————-
— Afficher le nombre total de plans dans le cache et la taille de mémoire mobilisée
SELECT COUNT(*) AS [Nb_Total_Plans]
,SUM(c.size_in_bytes)/(1024*1024) AS [Total_Cache_Size_Mo]
FROM sys.dm_exec_cached_plans c
— Afficher le nombre total de plans non réutilisés dans le cache et la taille de mémoire mobilisée
SELECT COUNT(*) AS [Nb_Total_Plans_Non_Rutiliss]
,SUM(c.size_in_bytes)/(1024*1024) AS [Plans_Non_Rutiliss_Size_Mo]
FROM sys.dm_exec_cached_plans c
WHERE c.usecounts = 1
— Afficher le nombre total de plans des procédures stockées dans le cache et la mémoire mobilisée
SELECT COUNT(*) AS [Nb_Total_Proc]
,SUM(c.size_in_bytes)/(1024*1024) AS Proc_Size_Mo
FROM sys.dm_exec_cached_plans c
WHERE c.objtype = 'Proc'
— Afficher le nombre total de plans des Vues dans le cache et la mémoire mobilisée
SELECT COUNT(*) AS [Nb_Total_View]
,SUM(c.size_in_bytes)/(1024*1024) AS View_Size_Mo
FROM sys.dm_exec_cached_plans c
WHERE c.objtype = 'View'
— Afficher le nombre total de plans des Triggers dans le cache et la mémoire mobilisée
SELECT COUNT(*) AS [Nb_Total_Trigger]
,SUM(c.size_in_bytes)/(1024*1024) AS Trigger_Size_Mo
FROM sys.dm_exec_cached_plans c
WHERE c.objtype = 'Trigger'
— Afficher le nombre des requêtes Adhoc (requête contenant soit : SELECT,INSERT,UPDATE ou DELETE ) dans le cache et la mémoire mobilisée
SELECT COUNT(*) AS [Nb_Total_RequeteAdhoc]
,SUM(c.size_in_bytes)/(1024*1024) AS Adhoc_Size_Mo
FROM sys.dm_exec_cached_plans c
WHERE c.objtype = 'Adhoc'
— Afficher les requêtes Adhoc ayant des plans non réutilisés dans le cache et la taille de mémoire mobilisée
SELECT COUNT(*) AS [Nb_Total_RequeteAdhoc]
,SUM(c.size_in_bytes)/(1024*1024) AS Adhoc_Size_Mo
FROM sys.dm_exec_cached_plans c
WHERE c.objtype = 'Adhoc'
———————————————————————-
–> Afficher les requêtes utilisateurs en doublon dans le cache
———————————————————————-
WITH CTE_CasseDoublon (nb_cte,cacheobjtype_cte,objtype_cte,text_cte) AS
(
SELECT count(*) AS Nb
,c.cacheobjtype
,c.objtype
,LOWER(s.text)
FROM sys.dm_exec_cached_plans c
CROSS APPLY sys.dm_exec_sql_text(plan_handle) s
GROUP BY
c.cacheobjtype
,c.objtype
,s.text
HAVING COUNT(*) > 1
)
SELECT c.usecounts
,c.cacheobjtype
,c.objtype
,s.text
FROM sys.dm_exec_cached_plans c INNER JOIN CTE_CasseDoublon cte ON c.cacheobjtype = cte.cacheobjtype_cte AND c.objtype = cte.objtype_cte
CROSS APPLY sys.dm_exec_sql_text(plan_handle) s
WHERE LOWER (s.text ) = text_cte
AND c.objtype ='Adhoc'
ORDER BY s.text