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