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 O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.