Top 5 SQL Server DMV for Index Analysis

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

5.sys.dm_db_index_operational_stats

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

 SELECT OBJECT_NAME(A.[OBJECT_ID]) AS [OBJECT NAME],
       I.[NAME] AS [INDEX NAME],
       A.LEAF_INSERT_COUNT,
       A.LEAF_UPDATE_COUNT,
       A.LEAF_DELETE_COUNT
FROM   SYS.DM_DB_INDEX_OPERATIONAL_STATS (NULL,NULL,NULL,NULL ) A
       INNER JOIN SYS.INDEXES AS I
         ON I.[OBJECT_ID] = A.[OBJECT_ID]
            AND I.INDEX_ID = A.INDEX_ID
WHERE  OBJECTPROPERTY(A.[OBJECT_ID],'IsUserTable') = 1

 
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.