How to find what Stored Procedures are using what indexes?

Question

I am trying to determine what indexes are no longer used in my Database. I have had great luck using the following query:

SELECT   OBJECT_NAME(S.[OBJECT_ID]) AS [OBJECT NAME],
         I.[NAME] AS [INDEX NAME],
         i.Type_Desc as [Index Type],
         USER_SEEKS,
         USER_SCANS,
         USER_LOOKUPS,
         USER_UPDATES
FROM     SYS.DM_DB_INDEX_USAGE_STATS AS S
         INNER JOIN SYS.INDEXES AS I
           ON I.[OBJECT_ID] = S.[OBJECT_ID]
              AND I.INDEX_ID = S.INDEX_ID 
WHERE i.name is not null
AND 
    (   OBJECT_NAME(S.[OBJECT_ID]) = 'Table1'
        OR
        OBJECT_NAME(S.[OBJECT_ID]) = 'Table2'
        OR
        OBJECT_NAME(S.[OBJECT_ID]) = 'Table3'
    )
ORder by S.[OBJECT_ID], user_Seeks desc , user_scans desc

What I would like to find now is what Stored Procedures are causing the Seeks, scans and lookups that The above query reports on. Is this information stored in the system views/tables?

CLARIFICATION

As gbn has pointed out a Stored Procedure does not directly use an index, it uses a table that uses an index. Below is an explanation that I hope will clarify what I am trying to ask here.

Is it possible for me to determine what SQL was run that caused the above indexes to be used? For example if one of the indexes reported on has 10 User_Seeks would it be possible to determine that exec sp_1 caused that usage 7 times and exec sp_2 caused that usage 3 times?

Answer 1

You have the number of executions for all statements in sys.dm_exec_query_stats, and you can extract the plan XML using sys.dm_exec_query_plan. The plan contains details like scan operators used, so between these two you can make up a lot of information from what you ask. For example the following query will show you the IndexScan operators in the frequently run statements from the cached plans that are causing many logical reads:

with xmlnamespaces ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' as sp)
select top(100) 
  q.total_logical_reads, q.execution_count
  , x.value(N'@Database', N'sysname') as [Database]
  , x.value(N'@Schema', N'sysname') as [Schema]
  , x.value(N'@Table', N'sysname') as [Table]
  , x.value(N'@Index', N'sysname') as [Index]
  , substring(t.text, q.statement_start_offset/2,   
  case when 0 < q.statement_end_offset then (q.statement_end_offset - q.statement_start_offset)/2
  else len(t.text) - q.statement_start_offset/2 end) as [Statement]
from sys.dm_exec_query_stats q
cross apply sys.dm_exec_query_plan(plan_handle)
cross apply sys.dm_exec_sql_text(sql_handle) as t
cross apply query_plan.nodes(N'//sp:IndexScan/sp:Object') s(x)
where execution_count > 100
order by total_logical_reads desc;

Answer 2

Edit (again, after question update):

No realistic chance. You could try profiler and capture the textplan. I saw this once and it killed a server though: it's a lot of text to record. YMMV :-)

Stored procedures do not use indexes.

Stored procs use tables (and indexed views) that then use indexes (or don't use as you've worked out above)

Doing SELECT col1, col2 FROM myTable WHERE col2 = 'foo' ORDER BY col1 is the same whether it's in a stored procedure, view, user defined function or by itself.

Edit: Our index usage script, downloaded from somewhere...

SELECT
    o.name AS [object_name], 
    i.name AS index_name, 
    i.type_desc, 
    u.user_seeks, u.user_scans, 
    u.user_lookups, u.user_updates,
    o.type
FROM
    sys.indexes i
    JOIN
    sys.objects o ON i.[object_id] = o.[object_id]
    LEFT JOIN 
    sys.dm_db_index_usage_stats u ON i.[object_id] = u.[object_id] AND 
                                    i.index_id = u.index_id AND 
                                    u.database_id = DB_ID()
WHERE
    o.type IN ('U', 'V') AND
    i.name IS NOT NULL
ORDER BY 
    u.user_seeks + u.user_scans + u.user_lookups, u.user_updates