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

SVA 116 Reputation points
2022-07-24T10:36:23.683+00:00

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?

SQL Server | Other
0 comments No comments
{count} votes

Accepted answer
  1. Seeya Xi-MSFT 16,586 Reputation points
    2022-07-25T07:53:23.367+00:00

    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.

    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. Erland Sommarskog 121.7K Reputation points MVP Volunteer Moderator
    2022-07-24T22:09:07.64+00:00

    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.

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.