21 November,2007 by Tom Collins
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;
Top 5 SQL Server DMV for Index Analysis
This is only a preview. Your comment has not yet been posted.
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.
Posted by: |