DIR Return Create A Forum - Home
       ---------------------------------------------------------
       ExcelSoft Database Professionals
  HTML 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
       *****************************************************