问题描述
我正在尝试确定我的数据库中不再使用哪些索引.我使用以下查询很幸运:
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
这篇关于如何查找哪些存储过程正在使用哪些索引?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!