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.