Dynamic Management Views and Dynamic Management Functions - Microsoft SQL Server 2012 Performance Tuning - page 149

There is more to execution-related DMVs and DMFs. The following is a list of some more DMVs
and DMFs that are frequently used in query tuning that you may like to explore:
F sys.dm_exec_cached_plans (DMV)
F sys.dm_exec_procedure_stats (DMV)
F sys.dm_exec_query_stats (DMV)
F sys.dm_exec_cached_plan_dependent_objects (DMF)
These DMVs and DMFs deal with providing caching details of the queries and objects, and are
helpful in query tuning as well.
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:
F Examining the index usage patterns
F Finding the missing indexes
F Finding the unused indexes
F Finding the fragmented indexes
F 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.
Getting ready
This example will show you how you can find the missing indexes using DMVs and DMFs.
Missing indexes are the indexes that are not present but can improve the performance of
the queries if created.
We will also see how to retrieve the index usage details and fragmentation details using
certain DMVs and DMFs so that you can easily perform the index-maintenance tasks on
your database.