SQL Server - 如何确定是否未使用索引？ [英] SQL Server - how to determine if indexes aren't being used?

问题描述

我有一个高需求的交易数据库，我认为是过度索引。最初，它没有任何索引，所以添加一些共同的过程，产生了巨大的区别。然而，随着时间的推移，我们已经创建了索引来加速单个查询，一些最流行的表有10-15个不同的索引，在某些情况下，索引只是稍有不同，或者是

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.

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

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.

推荐答案

查看 sys.dm_db_index_usage_stats 。这些统计信息在服务器启动时重置，因此您必须在服务器启动后检查并在足够长的时间内运行相关负载。

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.

这篇关于SQL Server - 如何确定是否未使用索引？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！