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