October 15, 2010
Posted by on
Two weeks ago, I held a session called “Hardcore SQL” session at AppEd where I (among other things) said that you should always start with removing unused indexes, before adding missing indexes. You can find my blog post about missing indexes here. So, how do you find unused indexes?
O.name AS ObjectName, I.name AS IndexName,
user_seeks + user_scans + user_lookups AS Reads,
user_updates AS Writes, P.rows AS RowsNum
FROM sys.dm_db_index_usage_stats AS IUS
INNER JOIN sys.indexes AS I ON I.index_id = IUS.index_id AND IUS.object_id = I.object_id
INNER JOIN sys.partitions AS P ON P.index_id = IUS.index_id AND IUS.object_id = P.object_id
INNER JOIN sys.objects AS O ON IUS.object_id = O.object_id
WHERE O.type = 'U'
AND IUS.database_id = DB_ID()
AND I.type_desc = 'nonclustered'
AND I.is_primary_key = 0
AND I.is_unique_constraint = 0
AND P.rows > 10000
ORDER BY Reads, RowsNum DESC
Btw, I’ve got a Live Mesh folder with SQL scripts. If anyone wants access to it (and maybe wants to add scripts) you know how to contact me.