Monitoring index performance
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, ...
Get Microsoft SQL Server 2012 Performance Tuning Cookbook now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.
