How To: Finding Index Usage Stats With DMV
DBA’s utilize indexes as one way of optimizing the performance of their queries. They either create or drop indexes according to the current needs of their system. Using the Dynamic Management Views (DMV) and the other related catalog views, DBA’s are able to analyze the statistics for indexes.
In the following figure, we use TSQL to find the stats for the user_seeks, user_scans, user_lookups, and the related objects tied to the index:
The query above will give us the following (selected only the top 5 for the purpose of the post)