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.

Republished from http://www.sqlserver-dba.com.