Search

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 o.name AS object_name, i.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, o.name, i.name

The above query returns the indexes which are never used.

No comments:

Post a Comment