O'Reilly logo

Microsoft SQL Server 2012 Performance Tuning Cookbook by Bihag Thaker, Ritesh Shah

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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, ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required