Blog ala Vidar

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?

SELECT
  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.