SQL: Index Usage Seek/Scan
Author: jason
Date: 2014-01-22 10:47:53
Category: Musing

Track the amount of seeks and scans for indexes with this script. It should help identify heavily used indexes.






Code

SELECT object_schema_name(indexes.object_id) + '.' + object_name(indexes.object_id) AS objectName,
indexes.Name, CASE WHEN is_unique = 1 THEN 'UNIQUE ' ELSE '' END + indexes.type_desc AS [Type],
(ddius.user_seeks + ddius.user_scans + ddius.user_lookups + ddius.user_updates) AS Total,
ddius.user_seeks, ddius.user_scans, ddius.user_lookups, ddius.user_updates
FROM sys.indexes
LEFT OUTER JOIN sys.dm_db_index_usage_stats ddius
ON indexes.object_id = ddius.object_id
AND indexes.index_id = ddius.index_id
AND ddius.database_id = db_id()
ORDER BY ddius.user_seeks + ddius.user_scans + ddius.user_lookups + ddius.user_updates DESC




jason @ jasonthomasfrance.com - www.masterstationlog.com - copyright 2009