(DIR) Return Create A Forum - Home --------------------------------------------------------- ExcelSoft Database Professionals (HTM) https://esdbp.createaforum.com --------------------------------------------------------- ***************************************************** (DIR) Return to: Scripts ***************************************************** #Post#: 119-------------------------------------------------- Plan Cache analysis By: srinivasma_exceldbp Date: December 25, 2014, 4:10 am --------------------------------------------------------- -- Create a stored procedure for Plan Cache analysis -- Usage : Any object can be given EXEC PlanCacheAnalysis '%SalesOrderHeader%' ( table name) -- EXEC PlanCacheAnalysis '%Orders%' ( Orders is table name) CREATE PROCEDURE PlanCacheAnalysis ( @ObjectName SYSNAME ) AS BEGIN SELECT st.text, qs.execution_count, cp.cacheobjtype, cp.objtype, cp.*, qs.*, p.* FROM sys.dm_exec_cached_plans cp CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) p CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st LEFT JOIN sys.dm_exec_query_stats qs ON qs.plan_handle = cp.plan_handle WHERE st.text LIKE @ObjectName AND st.text NOT LIKE '%syscache%' AND st.text NOT LIKE '%dm[_]exec[_]%' END GO *****************************************************