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()

Sys.dm_db_index_usage_stats 

 

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

Sys.dm_db_missing_index_details 

3. sys.dm_db_missing_index_columns

 

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

Dm_exec_query_stats 

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

DM_DB_INDEX_OPERATIONAL_STATS 
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 (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

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

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

Working...

Post a comment on Top 5 SQL Server DMV for Index Analysis


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer