SQL Server - 如何确定索引是否未被使用？

I have a high-demand transactional database that I think is over-indexed. Originally, it didn't have any indexes at all, so adding some for common processes made a huge difference. However, over time, we've created indexes to speed up individual queries, and some of the most popular tables have 10-15 different indexes on them, and in some cases, the indexes are only slightly different from each other, or are the same columns in a different order.

我有一个高需求的事务数据库，我认为是过度索引。最初，它根本没有任何索引，因此为常见过程添加一些索引会产生巨大的差异。但是，随着时间的推移，我们已经创建了索引来加速单个查询，一些最流行的表上有10-15个不同的索引，在某些情况下，索引只是略有不同，或者是相同的列以不同的顺序排列。

Is there a straightforward way to watch database activity and tell if any indexes are not hit anymore, or what their usage percentage is? I'm concerned that indexes were created to speed up either a single daily/weekly query, or even a query that's not being run anymore, but the index still has to be kept up to date every time the data changes.

有没有一种直接的方法来观察数据库活动并告诉我们是否有任何索引不再被命中，或者它们的使用百分比是多少？我担心创建索引是为了加快单个每日/每周查询，甚至是不再运行的查询，但每次数据更改时索引仍然必须保持最新。

In the case of the high-traffic tables, that's a dozen times/second, and I want to eliminate indexes that are weighing down data updates while providing only marginal improvement.

对于高流量表，这是十几次/秒，我想要消除压缩数据更新的索引，同时只提供微小的改进。

4 个解决方案

#1

Look at the number of user seeks/scans/lookups and last user seek/scan/lookup in sys.dm_db_index_usage_stats. These stats are reset at server start up, so you'd have to check after the server was up and running a relevant load for enought time.

查看sys.dm_db_index_usage_stats中的用户搜索/扫描/查找次数和上次用户搜索/扫描/查找次数。这些统计信息在服务器启动时重置，因此您必须在服务器启动后检查并在运行时运行相关负载。

#2

This script will look at the DMV's (dynamic management views) and find those indices that haven't been used.

该脚本将查看DMV（动态管理视图）并查找尚未使用的索引。

DECLARE  @dbid INT

SELECT @dbid = DB_ID(DB_NAME())

SELECT   
    OBJECTNAME = OBJECT_NAME(I.OBJECT_ID),
    INDEXNAME = I.NAME,
    I.INDEX_ID
FROM     
    SYS.INDEXES I
JOIN 
    SYS.OBJECTS O ON I.OBJECT_ID = O.OBJECT_ID
WHERE    
    OBJECTPROPERTY(O.OBJECT_ID, 'IsUserTable') = 1
    AND I.INDEX_ID NOT IN 
       (SELECT S.INDEX_ID
        FROM SYS.DM_DB_INDEX_USAGE_STATS S
        WHERE S.OBJECT_ID = I.OBJECT_ID
        AND I.INDEX_ID = S.INDEX_ID
        AND DATABASE_ID = @dbid)
ORDER BY 
    OBJECTNAME, I.INDEX_ID, INDEXNAME ASC

Mind you - the DMV are dynamic - e.g. they get reset to "nothing" every time you restart your SQL Server services. Don't check those if your server has been up for only a few minutes! Almost all indices will show up in your result set......

请注意 - DMV是动态的 - 例如每次重新启动SQL Server服务时，它们都会重置为“无”。如果你的服务器已经启动了几分钟，请不要检查它们！几乎所有指数都会出现在你的结果集中......

But if you can monitor the result set of this query over time, you should definitely get a feel for which indices aren't being used ever. Very handy indeed !

但是如果你可以随着时间的推移监视这个查询的结果集，你肯定应该感觉到哪些索引没有被使用过。非常方便！

#3

If you're in SQL Server 2005/2008, you should use the Database Tuning advisor.

如果您使用的是SQL Server 2005/2008，则应使用Database Tuning顾问程序。

You can specify it to run for a given length of time, and it will collect statistics on what is being used and what isn't. At the end of the run, it will come up with some very useful observations on what to do to optimize your indexing strategy.

您可以指定它运行一段给定的时间，它将收集有关正在使用的内容和不使用内容的统计信息。在运行结束时，它将提供一些非常有用的观察结果，说明如何优化索引策略。

#4

The Database Tuning Wizard will be helpful here. Use the Profiler to record a standard set of queries over the course of a couple of hours, and use that trace file in the Tuning Wizard to identify possible redundant indexes.

数据库调整向导在这里会很有用。使用Profiler在几个小时内记录一组标准查询，并使用Tuning Wizard中的跟踪文件来识别可能的冗余索引。

#1

#2