sql-server - 如何查找哪些存储过程正在使用哪些索引？

Question

我正在尝试确定数据库中不再使用哪些索引。我使用以下查询非常幸运:

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

我现在想找到的是存储过程导致上述查询报告的查找、扫描和查找的原因。此信息是否存储在系统 View /表中？

澄清

正如 gbn 所指出的，存储过程不直接使用索引，而是使用使用索引的表。以下是我希望能够澄清我在这里想问的问题的解释。

我是否可以确定运行的 SQL 导致使用上述索引？例如，如果报告的索引之一有 10 个 User_Seek，是否可以确定 exec sp_1 导致该使用 7 次，exec sp_2 导致该使用 3 次？

Answer 1

您拥有 sys.dm_exec_query_stats 中所有语句的执行次数，您可以使用 sys.dm_exec_query_plan 提取计划 XML 。该计划包含诸如所使用的扫描运算符(operator)之类的详细信息，因此在这两者之间，您可以根据您的要求组成大量信息。例如，以下查询将向您显示缓存计划中频繁运行的语句中导致大量逻辑读取的 IndexScan 运算符:

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;