How to find stored procedure(s) that scan particular index
When it comes to performance issues, we can get a lot from querying
the cached plans of stored procedures, for example:
- we can find which stored procedures(SPs) scan an index
- in which SPs we have bookmark lookups/ missing indexes
- find SPs where different spool operators being used
- get SPs which contain parallel execution
- check if an index being used in some SP(s)
and more.
Let`s start with the basic. By using the query below you can get
an execution plan of a stored procedure, open it and analyze it.
-- Get execution query plan in XML format
SELECT OBJECT_NAME(st.objectid,st.dbid) AS ObjectName,cp.usecounts AS ExecutionCount,
qp.query_plan AS QueryPlan
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st
WHERE cp.objtype='Proc'
AND st.dbid=DB_ID(DB_NAME()) -- Current DB
AND OBJECT_NAME(st.objectid,st.dbid) = 'SPName'
By running the next query we can find out which stored procedures
scan particular index.
WITH XMLNAMESPACES('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS ns)
SELECT OBJECT_SCHEMA_NAME( qp.objectid ) AS [schema_name],
OBJECT_NAME(qp.objectid ) AS [object_name],
qp.query_plan
FROM sys.dm_exec_cached_plans as p
CROSS APPLY sys.dm_exec_query_plan( plan_handle ) as qp
CROSS APPLY qp.query_plan.nodes('//ns:IndexScan/ns:Object[@Database="[DBNAME]"]
[@Schema="[dbo]"][@Table="[TblName]"][@Index="[IndexName]"]' ) plan_xml(x)
Important note: The XML is case sensitive, so insert the table/index name
exactly as they defined, pay attention to capital letters.
The following query can help you to check if an index being used,
this can be helpful when you want to delete an index.
WITH XMLNAMESPACES(DEFAULT N'http://schemas.microsoft.com/sqlserver/2004/07/showplan'),
CachedPlansCTE (DatabaseName,SchemaName,TableName,IndexName,QueryText,QueryPlan,[object_name])
AS
(
SELECT ObjNode.op.value(N'@Database',N'varchar(128)') AS DatabaseName,
ObjNode.op.value(N'@Schema',N'varchar(128)') AS SchemaName,
ObjNode.op.value(N'@Table',N'varchar(128)') AS TableName,
ObjNode.op.value(N'@Index',N'varchar(128)') AS IndexName,
cp.[text] AS QueryText,
cp.query_plan AS QueryPlan,
cp.[object_name]
FROM (
SELECT query.[text],qp.query_plan,OBJECT_NAME(qp.objectid ) as [object_name]
FROM sys.dm_exec_cached_plans as p
OUTER APPLY sys.dm_exec_sql_text(plan_handle) as query
OUTER APPLY sys.dm_exec_query_plan(plan_handle) as qp
WHERE query.[text] NOT LIKE '%sys%'
AND p.cacheobjtype ='Compiled Plan'
AND p.objtype='Proc'
) cp
CROSS APPLY cp.query_plan.nodes(N'//Object') ObjNode (op)
)
SELECT [object_name],DatabaseName,SchemaName,TableName,IndexName,QueryPlan,QueryText
FROM CachedPlansCTE
WHERE (IndexName like '%IndexName%')
AND (QueryText not like '%insert%')
AND (QueryText not like '%update%')
OPTION (MAXDOP 1)
Source: http://sqlconcept.com/2011/07/12/how-to-query-the-cached-plans-xml/
http://blogs.sqlserver.org.au/blogs/greg_linwood/archive/2008/04/27/1229.aspx
the cached plans of stored procedures, for example:
- we can find which stored procedures(SPs) scan an index
- in which SPs we have bookmark lookups/ missing indexes
- find SPs where different spool operators being used
- get SPs which contain parallel execution
- check if an index being used in some SP(s)
and more.
Let`s start with the basic. By using the query below you can get
an execution plan of a stored procedure, open it and analyze it.
-- Get execution query plan in XML format
SELECT OBJECT_NAME(st.objectid,st.dbid) AS ObjectName,cp.usecounts AS ExecutionCount,
qp.query_plan AS QueryPlan
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st
WHERE cp.objtype='Proc'
AND st.dbid=DB_ID(DB_NAME()) -- Current DB
AND OBJECT_NAME(st.objectid,st.dbid) = 'SPName'
By running the next query we can find out which stored procedures
scan particular index.
WITH XMLNAMESPACES('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS ns)
SELECT OBJECT_SCHEMA_NAME( qp.objectid ) AS [schema_name],
OBJECT_NAME(qp.objectid ) AS [object_name],
qp.query_plan
FROM sys.dm_exec_cached_plans as p
CROSS APPLY sys.dm_exec_query_plan( plan_handle ) as qp
CROSS APPLY qp.query_plan.nodes('//ns:IndexScan/ns:Object[@Database="[DBNAME]"]
[@Schema="[dbo]"][@Table="[TblName]"][@Index="[IndexName]"]' ) plan_xml(x)
Important note: The XML is case sensitive, so insert the table/index name
exactly as they defined, pay attention to capital letters.
The following query can help you to check if an index being used,
this can be helpful when you want to delete an index.
WITH XMLNAMESPACES(DEFAULT N'http://schemas.microsoft.com/sqlserver/2004/07/showplan'),
CachedPlansCTE (DatabaseName,SchemaName,TableName,IndexName,QueryText,QueryPlan,[object_name])
AS
(
SELECT ObjNode.op.value(N'@Database',N'varchar(128)') AS DatabaseName,
ObjNode.op.value(N'@Schema',N'varchar(128)') AS SchemaName,
ObjNode.op.value(N'@Table',N'varchar(128)') AS TableName,
ObjNode.op.value(N'@Index',N'varchar(128)') AS IndexName,
cp.[text] AS QueryText,
cp.query_plan AS QueryPlan,
cp.[object_name]
FROM (
SELECT query.[text],qp.query_plan,OBJECT_NAME(qp.objectid ) as [object_name]
FROM sys.dm_exec_cached_plans as p
OUTER APPLY sys.dm_exec_sql_text(plan_handle) as query
OUTER APPLY sys.dm_exec_query_plan(plan_handle) as qp
WHERE query.[text] NOT LIKE '%sys%'
AND p.cacheobjtype ='Compiled Plan'
AND p.objtype='Proc'
) cp
CROSS APPLY cp.query_plan.nodes(N'//Object') ObjNode (op)
)
SELECT [object_name],DatabaseName,SchemaName,TableName,IndexName,QueryPlan,QueryText
FROM CachedPlansCTE
WHERE (IndexName like '%IndexName%')
AND (QueryText not like '%insert%')
AND (QueryText not like '%update%')
OPTION (MAXDOP 1)
Source: http://sqlconcept.com/2011/07/12/how-to-query-the-cached-plans-xml/
http://blogs.sqlserver.org.au/blogs/greg_linwood/archive/2008/04/27/1229.aspx