How to find the unused index which can be dropped also to find out the required index to create

Question

Hi,

How can I identify which index can be dropped. Also to find out the required index to create in a database.

Whether user_seek, user_scans and user_lookups are 0 and user_updates not equal to 0 then can I drop that index?

Answer 1

Hi @SVA ,

Welcome to Microsoft Q&A!
In SQL Server, the DMV view sys.dm_db_index_usage_stats is provided.
With respect to this view,
user_scans: The number of scans performed by the user query.
user_seeks: The number of searches performed by the user query.
user_lookups: The number of bookmark lookups performed by the user query.
user_updates: The number of updates performed by the user query. This represents the number of inserts, deletes, and updates rather than the actual number of rows affected. For example, if you delete 1000 rows in a statement, this count is incremented by 1.
A useless index is an index that is not used by the user in a search, scan, or lookup. Note that the user_updates in the view refer to the number of times the index is maintained for updates due to insertions, deletions, updates, etc. of the table. If user_seeks, user_scans, user_lookups are all 0 and user_updates is greater than 0, it means that the index is not only unhelpful to performance, but also consumes extra performance to maintain the index, and such indexes are best disabled or even deleted (if you are sure they are really not being used, of course).
For more information, see:
https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-db-index-usage-stats-transact-sql?view=sql-server-ver16
https://www.sqlshack.com/how-to-identify-and-monitor-unused-indexes-in-sql-server/

Best regards,
Seeya

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Whether user_seek, user_scans and user_lookups are 0 and user_updates not equal to 0 then can I drop that index?

Maybe. Keep in mind that sys.dm_db_index_usage_stats is cleared when SQL Server is restarted. I also think it can be cleared with DBCC SQLPERF, but maybe we can ignore that possibility. So if you restarted your SQL Server yesterday, it's probably not a good idea to drop indexes with all zeroes. But if the instance have been up for a year - go for it! And for anything in between? You need to know something about your system. Maybe there are indexes to support end-of-the-quarter reporting, so it could take a while until you find out why you shouldn't have dropped that index.