Subscribe to RSS feed  Follow @jackvamvas - Twitter

*Use the Comments section for questions Links


SQL Server DBA Jobs

Top 5 SQL Server DMV for Index Analysis

27 May,2011 by Tom Collins

SQL Server DMV  and functionsare one of my favourite SQL Server  features when  tuning SQL Server Index performance.


I use the following 5 SQL Server DMVs for general overview


1. sys.dm_db_index_usage_stats


Maintains counts for the range of index activity and the last performed time. Also displays statistics ob how an index is used against a query.


select object_schema_name(indexes.object_id) + '.' + object_name(indexes.object_id) as objectName,

indexes.type_desc,ius.user_seeks, ius.user_scans,ius.user_lookups, ius.user_updates from sys.indexes

left outer join sys.dm_db_index_usage_stats ius

                        on indexes.object_id = ius.object_id

                             and indexes.index_id = ius.index_id

                             and ius.database_id = db_id()



2. sys.dm_db_missing_index_details 

Returns detailed information about each missing index on a table. Information is lost at SQL Server recycle.

 SELECT * FROM sys.dm_db_missing_index_details


3. sys.dm_db_missing_index_columns




4. sys.dm_exec_query_stats

Performance statistics for cached plans. The information is only available while the plan remains in the cache.

 select qs.sql_handle, qs.execution_count

     , qs.total_elapsed_time, qs.last_elapsed_time

     , qs.min_elapsed_time, qs.max_elapsed_time

     , qs.total_clr_time, qs.last_clr_time

     , qs.min_clr_time, qs.max_clr_time

  from sys.dm_exec_query_stats as qs



Returning IO , locking , latching and access activity. Useful for identifying index hotspots , waits for read\writes to a table. Will give information about insert,update, and delete

       I.[NAME] AS [INDEX NAME],
         ON I.[OBJECT_ID] = A.[OBJECT_ID]
            AND I.INDEX_ID = A.INDEX_ID

To assist in long term SQL Server index analysis record the output of the SQL Server DMVs , output the recordsets to a data depository . As the data is lost when SQL Server restarts

Returns database columns missing indexes. Information is lost at SQL Server recycle.

select * from sys.dm_db_missing_index_columns(1)

Normally, I use them as a general approach and then drill into a specific SQL Server performance problem.

Author: Tom Collins (


Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.


Post a comment on Top 5 SQL Server DMV for Index Analysis | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer