Introduction
Introduction Statistics Contact Development Disclaimer Help
Return Create A Forum - Home
---------------------------------------------------------
ExcelSoft Database Professionals
https://esdbp.createaforum.com
---------------------------------------------------------
*****************************************************
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
*****************************************************
You are viewing proxied material from gopher.createaforum.com. The copyright of proxied material belongs to its original authors. Any comments or complaints in relation to proxied material should be directed to the original authors of the content concerned. Please see the disclaimer for more details.