As you may know, index is a key to improve the query performance. Even if you have appropriate indexes on your tables, you need to perform index-maintenance tasks from time-to-time.
SQL Server has specialized DMVs and DMFs that provide useful index-related statistics which can be helpful in evaluating the performance metrics of existing indexes and usage patterns. By analyzing the statistics data returned by these DMVs and DMFs, you can do the following things:
Examining the index usage patterns
Finding the missing indexes
Finding the unused indexes
Finding the fragmented indexes
Analyzing the index page allocation details
In this recipe, we will use some of these DMVs and DMFs to determine the missing indexes in our database, number of seek and scan operations performed on indexes, and identify the fragmented indexes that may need to be reorganized or rebuilt.