How to identify and monitor dead indexes in SQL Server (2023)

SQL Server indexes are essentially copies of the data already in the table and are sorted and filtered in various ways to improve the performance of the queries that are executed. The search, browse, and query operators are used to access SQL Server indexes.

search operators– theSeekoperator takes advantage of SQL Server's ability to seek row indexes of clustered or non-clustered indexes, and the seek can be a physical or logical operator. Index retrieval deals only with the qualifying rows and the pages that contain those qualifying rows, and therefore the search cost is lower. In simple words, it tries to get only the selected rows from the table.

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

search operators– The search operator is used to retrieve the non-key data from the result set retrieved from the nonclustered index. Once the nonclustered index rows are retrieved, lookup operations are used to retrieve column information for those rows.

While proper use of SQL Server indexes can ensure better performance of executed queries, and therefore of SQL Server in general, misconfiguring or missing on-demand configuration can significantly degrade query performance. executed. Unnecessary indexes not used by queries can also be problematic.

SQL Server indexes are a great tool to improve the performance of SELECT queries, but at the same time, SQL Server indexes have a negative impact on data updates. The INSERT, UPDATE, and DELETE operations cause the index to be updated, thereby duplicating the data already in the table. This increases transaction and query execution times and can often result in very frequent hangs, hangs, deadlocks, and execution timeouts. For large databases or tables, storage space is also affected by redundant indexes. A key goal of any SQL Server DBA is index management, including creating needed indexes but dropping unused indexes.

Find Unused Indexes

SQL Server exposes a significant amount of index information through Dynamic Management Views (DMVs). The dm_db_index_usage_stats DMV displays important information about index usage and can be a useful tool for identifying unused SQL Server indexes. When an index is used for the first time, a new row is created in the DMV dm_db_index_usage_stats and then updated each time an index is used. However, as with all DMVs, the data is in thedm_db_index_usage_statscontain only the data since the last restart of the SQL Server service (a restart of the SQL Server service resets the data in the DMV). Therefore, it is important that sufficient time has elapsed since the last SQL Server restart so that it can correctly determine which indexes are good candidates for deletion.

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

The above query returns all unused indices of all types. This query is often found on the internet, but it is not an ideal/complete option. Using this query to find and clean up unused indexes can cause unexpected behavior because this query does not honor primary key and unique key constraints when collecting unused index data. Primary and unique key constraint indexes can be "unused", but removing these indexes can be problematic. To avoid this scenario, the above query should be refined by adding two lines of code after WHERE to exclude the collection's primary keys and unique keys as unused and possibly deleted.

The above query lists all unused queries that are not primary and unique keys, but also lists all unused indexes that SQL Server has not worked with. The user_updates column in the dm_db_index_usage_stats DMV indicates where the index was updated because the application loaded some data changes, so the index was updated. For thatdm_db_index_usage_stats.user_updates <> 0Conditions must be added to the above script.

So after identifying and listing the unused SQL Server indexes, one can determine which indexes are safe to drop, but again, great care must be taken.

What unused indexes should not be removed?

unique constraints

An example of other reasons for caution is that the index may show up as unused but may impose a uniqueness constraint and the query optimizer may need that index. The query optimizer can use a uniqueness guarantee to determine which logical transformations and physical operations to use to get accurate results. The query optimizer considers a uniqueness guarantee to perform certain operations, but this is not reflected in the index usage statistics without physically accessing the index in the final execution plan. With this in mind, any single index or constraint removal should be done with extreme caution.

use statistics

Another point to consider is the possibility of the query optimizer using statistics associated with this index, even in situations where the final execution plan does not use access to this index. Estimating cardinality, loading candidate statistics, and finally creating a complete query execution plan are completely separate actions.

Finally, removing the index can also remove the attached index statistics. This can affect the quality of the query execution plan when the statement is recompiled. This is because the query execution plan can use the index statistics, even if the index does not physically exist in the final execution plan, to calculate the cardinality estimate on which the final execution plan significantly depends.

These are just some of the potential issues that can occur when deleting the index, and therefore such action should be planned with a proper test and recovery plan in case something goes wrong. Also, a few unused SQL Server indexes do not necessarily indicate a problem, but if the number of unused indexes grows at a more or less constant rate over time, or if the growth is sudden, this should be checked and considered. drawn. cases

Deletion of the indices

The following script creates a drop script for all unused indexes. It is based on the above script, which is more secure, but is provided as a guide and deletion of indexes is at the discretion of the user. The purpose ID of the script to help identify the indexes.Candidatesto delete so don't decide this in a bubble

Helpful Resources:

• SQL: how to find unused index details
• Find missing indices
• About the missing indices feature
• Functional constraints with missing indices