December 21, 2009 at 7:11 am
Does it sound right that anything I get back from the query below is an index that is never being used?
select
obj.name,
ix.name,
stats.*
from
sys.dm_db_index_usage_stats stats
inner join sys.objects obj
on obj.object_id = stats.object_id
and obj.type = 'U'
inner join sys.indexes ix
on ix.object_id = obj.object_id
and ix.index_id = stats.index_id
and ix.type <> 0
WHERE
stats.user_seeks = 0
and stats.user_scans = 0
and stats.user_lookups = 0
and stats.system_seeks = 0
and stats.system_scans = 0
and stats.system_lookups = 0
The Redneck DBA
December 21, 2009 at 7:31 am
What what will give you is all indexes that have had at least one update but no seeks, scans or lookup since the database was last started. It won't show you indexes that have had 0 seeks, scans, lookups and updates as they don't reflect in index usage at all. To get those the query should be FROM sys.indexes LEFT OUTER JOIN sys.dm_db_index_usage_stats.
Also note the point about 'since the database was last started'. That DMV is not persisted. It only contains info since the last time the DB came online. While this is useful information, you should not drop an index based on this unless you are very, very sure that the server has been up long enough to see all types of queries. Something that often happens is that someone checks this after the server's been up a couple weeks, sees some indexes that are 'unused' and drops them, then the month end process that runs a week later runs many times longer than usual because the indexes that it needed (but nothing else used) had been dropped.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply