Index Usage in Dynamic Management Views

By , May 15, 2012 21:37

This is just a short one to post a script for a procedure to return index usage either for all non-Microsoft indexes or just for a single non-Microsoft.

There are loads of variations on this on the net and this is just my version:
 

CREATE PROCEDURE up_indexusage
	@table_name sysname = null
AS
SELECT 
	OBJECT_NAME(i.[object_id]) AS 'table_name', 
	i.name AS 'index_name',
	i.type_desc, 
	s.user_seeks, 
	s.last_user_seek,
	s.user_scans,
	s.last_user_scan,
	s.user_lookups,
	s.last_user_lookup,
	s.user_updates,
	s.last_user_update
FROM sys.indexes i LEFT JOIN sys.dm_db_index_usage_stats s 
	ON s.object_id = i.object_id
	AND s.index_id = i.index_id
	AND (s.database_id = DB_ID() OR s.database_id IS NULL)
WHERE (OBJECTPROPERTYEX(i.[object_id], 'IsMsShipped') = 0)
AND	(OBJECT_NAME(i.[object_id]) = 
		CASE 
			WHEN @table_name IS NULL THEN OBJECT_NAME(i.[object_id])
			ELSE @table_name
		END
	)
AND (s.database_id = DB_ID() OR s.database_id IS NULL)
ORDER BY OBJECT_NAME(i.[object_id]), i.index_id
GO

 
One very important thing to note (which I didn’t know and only saw for the first time today): These index usage statistics are reset when a mirrored database fails over.
Books Online state “The counters are initialized to empty whenever the SQL Server (MSSQLSERVER) service is started. In addition, whenever a database is detached or is shut down (for example, because AUTO_CLOSE is set to ON), all rows associated with the database are removed.”
There is no explicit mention of mirroring (failover) there :-|.

So caution should be exercised when using a statement like this to identify unwanted indexes. It would be best to be sure the server has been running and no failover has taken place for an extended period of time.

Thanks for reading.

Leave a Reply

*

Panorama Theme by Themocracy