Introduction
Indexes play an important role in SQL Server performance. Coins always have two sides, just like a well-designed index can improve query performance and an incorrect index can impact query performance. So it is important to find which indexes are not being used. It helps us to reduce storage and reduce the overhead of the database engine to maintain unused indexes.
Problem statement
How can we find indexes that are not being used?
Solution
The absence of an index can result in table or index scans that reduce performance in some case and also too many indexes require extra storage and extra effort to maintain the database and it might slow down insert / update operations. One of the approaches to improve the overall performance is keep used indexes but drop all unused indexes.
"dm_db_index_physical_stats" is a dynamic management view related to index statistics. This view gives information about indexes used or unused, it complete or missing some columns is irrelevant.
This dynamic view has many important columns like user_seeks (number of seeks by user queries), user_scans (number of scans by user queries), user_lookups (number of bookmark lookups by user queries) and a combination of these three columns provide us a total read count. The column user updates (number of updates by user queries) indicates the level of maintenance on the index caused by insert / update / delete operations on the table or view. A proper join among these DVM and system tables such as indexes, objects and schemas enable us to list all unused indexes for a single database.
The following query helps us to find unused indexes in our database.
- SELECT
- o.name AS TableName,
- i.name AS Indexname,
- i.is_primary_key AS PrimaryKey,
- s.user_seeks + s.user_scans + s.user_lookups AS NumOfReads,
- s.user_updates AS NumOfWrites,
- (SELECT SUM(p.rows) FROM sys.partitions p WHERE p.index_id = s.index_id AND s.object_id = p.object_id) AS TableRows,
- 'DROP INDEX ' + QUOTENAME(i.name) + ' ON ' + QUOTENAME(c.name) + '.' + QUOTENAME(OBJECT_NAME(s.object_id)) AS 'DropStatement'
- FROM sys.dm_db_index_usage_stats s
- INNER JOIN sys.indexes i ON i.index_id = s.index_id AND s.object_id = i.object_id
- INNER JOIN sys.objects o ON s.object_id = o.object_id
- INNER JOIN sys.schemAS c ON o.schema_id = c.schema_id
- WHERE OBJECTPROPERTY(s.object_id,'IsUserTable') = 1
- AND s.databASe_id = DB_ID()
- AND i.type_desc = 'NONCLUSTERED'
- AND i.is_primary_key = 0
- AND i.is_unique_constraint = 0
The preceding query includes the following helpful information.
- Table name
- Index name
- Primary key
- Number of read count
- Number of writes
- Total number of rows
- Drop statement
The following is the output of the preceding query:
![]()
After running the preceding query for the database it will list all the non-clustered indexes for all tables. Now we can determine the unused indexes by comparing the number of reads applied to an index with the number of writes. If we have a number of reads (NumOfReads column in the preceding query) then the indexes are not being used.
Base on the query result and application knowledge, we may decide which index needs to be dropped and the last column of the query contains a drop index statement.
Summary
This query is anticipated to serve as guidance. The drop index decisions are best made by the system owner with the knowledge about the data flow in the system.
I hope this helps!