[SQL Server] Afficher à partir du cache le plan d'exécution et les statistiques d'exécution d'une procédure stockée
SELECT T1.*,
T4.query_plan,
T2.usecounts,
T2.cacheobjtype,
T2.size_in_bytes,
T2.usecounts,
T2.objtype
FROM sys.dm_exec_procedure_stats T1
JOIN sys.dm_exec_cached_plans AS T2 ON T2.plan_handle=T1.plan_handle
CROSS APPLY sys.dm_exec_sql_text( T2.plan_handle) AS T3
CROSS APPLY sys.dm_exec_query_plan( T2.plan_handle)AS T4
WHERE objtype = 'Proc' and T2.cacheobjtype = 'Compiled Plan'
AND T3.text LIKE '%MaProcedureStockee%'
SELECT
T3.TEXT QueryName,
T1.execution_count AS ExecutionCount,
T1.max_elapsed_time AS MaxElapsedTime,
ISNULL(T1.total_elapsed_time / 1000 / NULLIF(T1.execution_count, 0), 0) AS AvgElapsedTime,
T1.creation_time,
ISNULL(T1.execution_count / 1000 / NULLIF(DATEDIFF(s, T1.creation_time, GETDATE()), 0), 0) AS FrequencyPerSec
,query_plan
FROM sys.dm_exec_query_stats T1
CROSS APPLY sys.dm_exec_query_plan( T1.plan_handle ) T2
CROSS APPLY sys.dm_exec_sql_text( T1.plan_handle ) T3
WHERE T3.TEXT LIKE '%MaProcedureStockee%'
ORDER BY creation_time