CODEWITHSUNDEEP
sqlsql-serversql-server-2012
whytheq
whytheq

Reputation: 35557

Can I find which indexes are current / in use

I have the following:

SELECT name AS index_name,
       STATS_DATE(OBJECT_ID, index_id) AS StatsUpdated
FROM   sys.indexes

Is it possible to add some information so I have an idea if each index is current and in use?

Also I'd like to return in the SELECT clause which table the index is on?

Upvotes: 0

Views: 150

Answers (1)

dferidarov
dferidarov

Reputation: 602

I am using the following query to find if an index is is use - if seeks,scans and look-ups are zero then it's not used.

SELECT TOP 25
o.name AS ObjectName
, i.name AS IndexName
, i.index_id AS IndexID  
, dm_ius.user_seeks AS UserSeek
, dm_ius.user_scans AS UserScans
, dm_ius.user_lookups AS UserLookups
, dm_ius.user_updates AS UserUpdates
, p.TableRows 
, is_disabled
,STATS_DATE(i.[OBJECT_ID], i.index_id) AS StatsUpdated
FROM sys.dm_db_index_usage_stats dm_ius  
INNER JOIN sys.indexes i ON i.index_id = dm_ius.index_id AND dm_ius.object_id = i.object_id   
INNER JOIN sys.objects o on dm_ius.object_id = o.object_id
INNER JOIN sys.schemas s on o.schema_id = s.schema_id
INNER JOIN (SELECT SUM(p.rows) TableRows, p.index_id, p.object_id 
                FROM sys.partitions p GROUP BY p.index_id, p.object_id) p 
        ON p.index_id = dm_ius.index_id AND dm_ius.object_id = p.object_id
WHERE OBJECTPROPERTY(dm_ius.object_id,'IsUserTable') = 1
AND dm_ius.database_id = DB_ID()   
AND i.type_desc = 'nonclustered'
AND i.is_primary_key = 0
AND i.is_unique_constraint = 0
ORDER BY (dm_ius.user_seeks + dm_ius.user_scans + dm_ius.user_lookups) ASC
GO

Upvotes: 2

Related Questions