如何识别和监视SQL Server中未使用的索引

SQL Server indexes are essentially copies of the data that already exist in the table, ordered and filtered in different ways to improve the performance of executed queries. Seeks, scans and lookups operators are used to access SQL Server indexes.

SQL Server索引实质上是表中已经存在的数据的副本，以不同的方式对其进行排序和过滤，以提高执行的查询的性能。 搜索，扫描和查找运算符用于访问SQL Server索引。

Seeks operators – the Seek operator uses the ability of SQL Server to search indexes to get rows from a clustered or nonclustered indexes, and the seek can be a physical as well as a logical operator. The index seeks only deals with qualified rows and with pages that comprises those qualified rows, and therefore the cost of the seek is less expensive. In simple words, seeks to retrieve just selected rows from the table.

Seeks运算符 – Seek运算符使用SQL Server的功能来搜索索引，以从聚簇或非聚簇索引中获取行，并且搜索可以是物理运算符，也可以是逻辑运算符。 索引查找仅处理合格的行以及包含这些合格行的页面，因此查找的成本较低。 简单来说，就是试图从表中检索选定的行。

Scans operators – the Scans operator scans the clustered index, and it is designed to deal with every row in the scanned table regardless of whether the row is qualified or not. A scan operator can be effective for small tables or in a situation where most of the rows are qualified. In simple terms, scans retrieve all the rows from the table.

扫描运算符 –扫描运算符扫描聚集索引，它设计为处理扫描表中的每一行，而不管该行是否合格。 扫描运算符对于小型表或大多数行都合格的情况可能有效。 简而言之，扫描从表中检索所有行。

Lookups operators – the lookup operator, is used for retrieving the non-key data from the results set retrieved from the nonclustered index. After the rows are retrieved from the nonclustered index, lookups are used for retrieving column information from these rows.

查找运算符 –查找运算符，用于从非聚簇索引检索的结果集中检索非关键数据。 从非聚簇索引中检索行之后，将使用查找从这些行中检索列信息。

While proper use of SQL Server indexes can grant improved performance of executed queries and thus the SQL Server in general, setting them up improperly or not setting them where needed, can have the significantly degraded the performance of the executed queries. Moreover, having unnecessary indexes that are not used by queries can be problematic as well.

尽管正确使用SQL Server索引可以提高已执行查询的性能，因此，一般来说SQL Server设置不当或在需要时不进行设置会大大降低已执行查询的性能。 此外，拥有查询未使用的不必要索引也会引起问题。

SQL Server indexes are an excellent tool for improving the performance of SELECT queries, but at the same time, SQL Server indexes have negative effects on data updates. INSERT, UPDATE, and DELETE operations cause index updating and thus duplicating the data that already exists in the table. As a result, this increases the duration of transactions and the query execution and often can result in locking, blocking, deadlocking and quite frequent execution timeouts. For large databases or tables, the storage space is also affected by redundant indexes. A critical goal, of any SQL Server DBA, is to maintain indexes including creating required indexes but at the same time removing the ones that are not used

SQL Server索引是提高SELECT查询性能的出色工具，但同时，SQL Server索引对数据更新有负面影响。 INSERT，UPDATE和DELETE操作导致索引更新，从而复制表中已经存在的数据。 结果，这增加了事务和查询执行的持续时间，并且经常会导致锁定，阻塞，死锁和相当频繁的执行超时。 对于大型数据库或表，存储空间也会受到冗余索引的影响。 任何SQL Server DBA的一个关键目标是维护索引，包括创建所需的索引，但同时删除未使用的索引。

查找未使用的索引 ( Finding unused indexes )

SQL Server provides a significant amount of index information via Dynamic Management Views (DMVs). The dm_db_index_usage_stats DMV displays essential information about index usage, and it can be a useful tool in identifying unused SQL Server indexes. When an index is used for the first time, a new row gets created in the dm_db_index_usage_stats DMV and subsequently updated every time an index is used. However, as with every DMV, the data present in dm_db_index_usage_stats contain only the data since the last SQL Server service restart (SQL Server service restart resets the data in the DMV). Therefore, it is critical that there is a sufficient time since the last SQL Server restart that allows correctly determining which indexes are good candidates to be dropped

SQL Server通过动态管理视图（DMV）提供了大量索引信息。 dm_db_index_usage_stats DMV显示有关索引使用情况的基本信息，它可以用作识别未使用SQL Server索引的有用工具。 首次使用索引时，将在dm_db_index_usage_stats DMV中创建一个新行，并在每次使用索引时进行更新。 但是，与每个DMV一样， dm_db_index_usage_stats中存在的数据仅包含自上次SQL Server服务重新启动以来的数据（SQL Server服务重新启动会重置DMV中的数据）。 因此，至关重要的是，自上次SQL Server重新启动以来，必须有足够的时间来正确确定哪些索引是要删除的良好候选对象。

A simple query that can be used to get the list of unused indexes in SQL Server (updated indexes not used in any seeks, scan or lookup operations) is as follows:

可以用来获取SQL Server中未使用索引的列表（在任何查找，扫描或查找操作中未使用的更新索引）的简单查询如下：

SELECT
    objects.name AS Table_name,
    indexes.name AS Index_name,
    dm_db_index_usage_stats.user_seeks,
    dm_db_index_usage_stats.user_scans,
    dm_db_index_usage_stats.user_updates
FROM
    sys.dm_db_index_usage_stats
    INNER JOIN sys.objects ON dm_db_index_usage_stats.OBJECT_ID = objects.OBJECT_ID
    INNER JOIN sys.indexes ON indexes.index_id = dm_db_index_usage_stats.index_id AND dm_db_index_usage_stats.OBJECT_ID = indexes.OBJECT_ID
WHERE
    AND
    dm_db_index_usage_stats.user_lookups = 0
    AND
    dm_db_index_usage_stats.user_seeks = 0
    AND
    dm_db_index_usage_stats.user_scans = 0
ORDER BY
    dm_db_index_usage_stats.user_updates DESC

The above query returns all unused indexes of all types. This query can frequently be found on the internet but isn’t an ideal/complete option. By using such a query to find and clean unused indexes may lead to unexpected behavior because this query does not take into account primary key and unique key constraints when collecting the unused index data. Both, primary and unique key constraints indexes could be “unused,” but deleting those indexes could be problematic. To prevent that scenario, the query above must be refined by adding two lines of code after the WHERE to exclude the primary and unique keys from being listed as unused and potentially deleted

上面的查询返回所有类型的所有未使用的索引。 该查询经常可以在Internet上找到，但不是理想/完整的选择。 通过使用此类查询查找和清除未使用的索引可能会导致意外行为，因为此查询在收集未使用的索引数据时未考虑主键和唯一键约束。 主键约束索引和唯一键约束索引都可以“不使用”，但是删除这些索引可能会有问题。 为了避免这种情况，必须通过在WHERE之后添加两行代码来完善上面的查询，以排除主键和唯一键被列为未使用和可能被删除的情况。

SELECT
    objects.name AS Table_name,
    indexes.name AS Index_name,
    dm_db_index_usage_stats.user_seeks,
    dm_db_index_usage_stats.user_scans,
    dm_db_index_usage_stats.user_updates
FROM
    sys.dm_db_index_usage_stats
    INNER JOIN sys.objects ON dm_db_index_usage_stats.OBJECT_ID = objects.OBJECT_ID
    INNER JOIN sys.indexes ON indexes.index_id = dm_db_index_usage_stats.index_id AND dm_db_index_usage_stats.OBJECT_ID = indexes.OBJECT_ID
WHERE
    indexes.is_primary_key = 0 -- This condition excludes primary key constarint
    AND
    indexes. is_unique = 0 -- This condition excludes unique key constarint
    AND
    dm_db_index_usage_stats. user_lookups = 0
    AND
    dm_db_index_usage_stats.user_seeks = 0
    AND
    dm_db_index_usage_stats.user_scans = 0
ORDER BY
    dm_db_index_usage_stats.user_updates DESC

The above query lists all unused queries that are not primary and unique keys, but it also lists all unused indexes that SQL Server has not worked with. The user_updates column in the dm_db_index_usage_stats DMV is counting where the index was updated as the application has carried some changes to data, so the index was updated. To do that the dm_db_index_usage_stats.user_updates <> 0 conditions should be added to the previous script

上面的查询列出了所有不是主键和唯一键的未使用查询，但它还列出了SQL Server尚未使用的所有未使用索引。 dm_db_index_usage_stats DMV中的user_updates列正在计算索引的更新位置，因为应用程序对数据进行了一些更改，因此索引已更新。 为此， 应将dm_db_index_usage_stats.user_updates <> 0条件添加到先前的脚本中

SELECT
    objects.name AS Table_name,
    indexes.name AS Index_name,
    dm_db_index_usage_stats.user_seeks,
    dm_db_index_usage_stats.user_scans,
    dm_db_index_usage_stats.user_updates
FROM
    sys.dm_db_index_usage_stats
    INNER JOIN sys.objects ON dm_db_index_usage_stats.OBJECT_ID = objects.OBJECT_ID
    INNER JOIN sys.indexes ON indexes.index_id = dm_db_index_usage_stats.index_id AND dm_db_index_usage_stats.OBJECT_ID = indexes.OBJECT_ID
WHERE
    indexes.is_primary_key = 0 --This line excludes primary key constarint
    AND
    indexes. is_unique = 0 --This line excludes unique key constarint
    AND 
    dm_db_index_usage_stats.user_updates <> 0 -- This line excludes indexes SQL Server hasn’t done any work with
    AND
    dm_db_index_usage_stats. user_lookups = 0
    AND
    dm_db_index_usage_stats.user_seeks = 0
    AND
    dm_db_index_usage_stats.user_scans = 0
ORDER BY
    dm_db_index_usage_stats.user_updates DESC

So now that unused SQL Server indexes are identified and listed, it can be determined which indexes can be dropped safely, but again that has to be done very carefully.

因此，既然已经标识并列出了未使用SQL Server索引，就可以确定可以安全地删除哪些索引，但是再次必须非常小心地完成。

哪些未使用的索引不应删除？ ( Which unused indexes should not be removed? )

Unique constraints

独特的约束

An example of additional reasons for caution is that the index might be listed as unused, but it might be enforcing a unique constraint, and it is likely that the query optimizer might need this index. The query optimizer might use a guarantee of uniqueness in determining what logical transformations and physical operations should be used for obtaining accurate results. The query optimizer takes into account a uniqueness guarantee to perform certain operations, but this is not echoed in index usage statistics without accessing the index physically in the final execution plan. Having that in mind any removal of unique index or constraint must be taken with the utmost precaution

引起警告的其他原因的一个示例是该索引可能被列为未使用的索引，但是可能正在强制执行唯一约束，并且查询优化器可能需要该索引。 查询优化器在确定应使用哪些逻辑转换和物理操作以获得准确结果时，可以使用唯一性保证。 查询优化器考虑了执行某些操作的唯一性保证，但是如果不在最终执行计划中实际访问索引，则索引使用情况统计信息中不会回显此保证。 考虑到这一点，必须采取最大的预防措施来删除唯一索引或约束。

Use statistics

使用统计

Another thing to be careful with is the possibility that the query optimizer use statistic that is associated to that index even in situations where the final execution plan does not use any access to that index. The cardinality estimates, loading of candidates for statistics and finally creating a completed query execution plan are entirely independent actions

要注意的另一件事是，即使在最终执行计划不使用对该索引的任何访问的情况下，查询优化器也可能使用与该索引关联的统计信息。 基数估计，加载统计候选数据以及最终创建完整的查询执行计划都是完全独立的操作

Finally, removing the index could remove the accompanying index statistics as well. That can impact query execution plan quality when the statement is recompiled. it is because the query execution plan might use the index statistics, even when the index is not physically present in the final execution plan, for calculating cardinality estimation, which is something that the final execution plan significantly relies on

最后，删除索引也可以删除伴随的索引统计信息。 重新编译该语句时，这可能会影响查询执行计划的质量。 这是因为查询执行计划可能会使用索引统计信息（即使最终执行计划中没有实际存在的索引）来计算基数估计，这也是最终执行计划所依赖的

Those are just some of the potential problems that could be encountered when dropping the index, and therefore such an action has to be planned by performing the adequate testing and with the plan for recovery if something goes wrong. On top of that, having some unused SQL Server indexes do not necessarily indicate a problem, but if the number of unused indexes grows over the time at some more or less constant rate or when there is a sudden growth, this is something that must be inspected and, in most cases, tested

这些只是删除索引时可能会遇到的一些潜在问题，因此，必须通过执行适当的测试来计划此类操作，并在出现问题时制定恢复计划。 最重要的是，拥有一些未使用SQL Server索引不一定表示存在问题，但是，如果未使用的索引的数量随着时间的推移以某种恒定的速率增长或者在突然增加的情况下增长，则必须做到这一点。检查并在大多数情况下进行测试

Dropping the indexes

删除索引

The following script creates a drop script for all unused indexes. It is based on the previous script that is safer, but it is provided as a guide, and any deletion of indexes is on users own discretion. The script purpose id to help identify indexes that are candidates to remove, so don’t decide on that in a bubble

以下脚本为所有未使用的索引创建一个放置脚本。 它基于更安全的以前的脚本，但仅作为指南提供，用户自行决定是否删除索引。 脚本的目的ID有助于识别要删除的候选索引，因此请不要在气泡中决定

SELECT 'DROP INDEX '+OBJECT_NAME(dm_db_index_usage_stats.object_id)+'.'+indexes.name AS Drop_Index, user_seeks, user_scans, user_lookups, user_updates
 
FROM
    sys.dm_db_index_usage_stats
    INNER JOIN sys.objects ON dm_db_index_usage_stats.OBJECT_ID = objects.OBJECT_ID
    INNER JOIN sys.indexes ON indexes.index_id = dm_db_index_usage_stats.index_id AND dm_db_index_usage_stats.OBJECT_ID = indexes.OBJECT_ID
WHERE
    indexes.is_primary_key = 0 --This line excludes primary key constarint
    AND
    indexes. is_unique = 0 --This line excludes unique key constarint
    AND 
    dm_db_index_usage_stats.user_updates <> 0 -- This line excludes indexes SQL Server hasn’t done any work with
    AND
    dm_db_index_usage_stats. user_lookups = 0
    AND
    dm_db_index_usage_stats.user_seeks = 0
    AND
    dm_db_index_usage_stats.user_scans = 0
ORDER BY
    dm_db_index_usage_stats.user_updates DESC

有用的资源： (Useful resources:)

• SQL : How to Find Unused Indexes detailsSQL：如何查找未使用的索引详细信息
• Finding Missing Indexes寻找缺失的索引
• About the Missing Indexes Feature关于缺失索引功能
• Limitations of the Missing Indexes Feature缺失索引功能的局限性

翻译自: https://www.sqlshack.com/how-to-identify-and-monitor-unused-indexes-in-sql-server/