sys.dm_db_index_usage_stats

21 November,2007 by Jack Vamvas

sys.dm_db_index_usage_stats is a SQL Server DMV . The recordset Returns information about  different types of index operations counts and last time operations occured.

select * from sys.dm_db_index_usage_stats


--will return a recordset , but we need to make slightly more useful.
--The following sql statement ,assumes the sql is executed on the db

DECLARE @dbid INT;

SELECT @dbid = db_id();

SELECT  objectName=object_name(s.object_id), s.object_id, indexName=i.name,indexType=i.type_desc
, user_seeks, user_scans, user_lookups, user_updates

FROM sys.dm_db_index_usage_stats s,

            sys.indexes i

WHERE database_id = @dbid AND objectproperty(s.object_id,'IsUserTable') = 1

AND i.object_id = s.object_id

AND i.index_id = s.index_id

ORDER BY (user_seeks + user_scans + user_lookups + user_updates) ASC;

 Related Posts

Top 5 SQL Server DMV for Index Analysis

Find all DMV and DMF


Author: Jack Vamvas (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 sys.dm_db_index_usage_stats


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