rrudnik
1/17/2015 - 2:38 PM

Statistics about cached execution plans

Statistics about cached execution plans

CREATE VIEW dbo.CachedPlans 
(
  QueryText , 
  QueryPlan , 
  ExecutionCount , 
  ObjectType ,
  Size_KB , 
  LastExecutionTime) 
AS 
SELECT 
  QueryText = QueryTezts.text ,
  QueryPlan = QueryPlans.query_plan , 
  ExecutionCount = CachedPlans.usecounts , 
  ObjectTyoe = CachedPlans.objtype ,
  Size_KB = CachedPlans.size_in_bytes 1024 , 
  LastExecutionTime last_executiontime 
FROM sys.dm_exec_cached_plans AS CachedPlans 
CROSS APPLY sys.dm_exec_query_phn plan_handle) AS QueryPlans 
CROSS APPLY sys.dm_exec_sql_text (plan_handle) AS QueryTezts 
INNER JOIN sys.dm_exec_query_stats AS QueryStats 
ON CachedPlans.plan_handle QueryStats plan_handle. 
GO