Wednesday, November 21, 2012

Indexes that are not used

One of the main task of a database administrator is proper handling of Indexes. He will have check for the right indexes from time to time. Below query checks the database and returns the unused indexes.

SELECT AS object_name, AS index_name
   , i.type_desc, u.user_seeks, u.user_scans, u.user_lookups, u.user_updates
FROM sys.indexes i
JOIN sys.objects o ON  i.object_id = o.object_id
LEFT JOIN  sys.dm_db_index_usage_stats u ON i.object_id = u.object_id
          AND    i.index_id = u.index_id
          AND    u.database_id = DB_ID()
WHERE    o.type <> 'S'    
and isnull(u.user_updates,0) > 0
and i.type_desc <> 'HEAP'
ORDER BY    (convert(decimal(19,4),ISNULL(u.user_seeks, 0)) + ISNULL(u.user_scans, 0) 
+ ISNULL(u.user_lookups, 0))/ISNULL(u.user_updates, 0) asc,,

The above query returns the indexes which are never used.

No comments:

Post a Comment