如何查找哪些存储过程正在使用哪些索引? [英] How to find what Stored Procedures are using what indexes?

问题描述

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

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?

澄清

正如 gbn 所指出的，存储过程不直接使用索引，它使用使用索引的表.下面是一个解释，我希望能澄清我在这里要问的问题.

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.

我是否可以确定导致使用上述索引的 SQL 是什么?例如，如果报告的其中一个索引有 10 个 User_Seeks，是否可以确定 exec sp_1 导致该使用 7 次而 exec sp_2 导致该使用 3 次?

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?

推荐答案

编辑(再次，问题更新后):

Edit (again, after question update):

没有现实的机会.您可以尝试分析器并捕获文本计划.我曾经看到过这个，但它杀死了一个服务器:要记录的文本很多.YMMV :-)

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 procs use tables (and indexed views) that then use indexes (or don't use as you've worked out above)

执行 SELECT col1, col2 FROM myTable WHERE col2 = 'foo' ORDER BY col1 无论是在存储过程、视图、用户定义函数中还是单独执行，都是一样的.

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.

我们的索引使用脚本，从某处下载...

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

这篇关于如何查找哪些存储过程正在使用哪些索引?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！