descrepency between sys.indexes and sys.dm_db_index_usage_stats

Question

Post reply

descrepency between sys.indexes and sys.dm_db_index_usage_stats

winston Smith

SSCoach

Points: 19500
More actions
July 16, 2008 at 6:00 am

#184880

ive ran a query to see all indexes associated with an object
select * from sys.indexes(nolock)
where object_id in
(
Object_id
)
for one table in particular, there are 5 indexes returned by the above query ( 1 clustered and 4 non clustered), but in the sys.dm_db_index_usage_stats there are only two rows returned for indexes on this table.
why are there not 5 rows returned for the object from sys.dm_db_index_usage_stats?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

HanShi SSC-Dedicated Points: 33507 More actions · Answer 1

Can you tell which of the indexes (which index_id) are missing?

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

Andras Belokosztolszki SSC-Insane Points: 22031 More actions · Answer 2

winston Smith (7/16/2008)
ive ran a query to see all indexes associated with an object
select * from sys.indexes(nolock)
where object_id in
(
Object_id
)
for one table in particular, there are 5 indexes returned by the above query ( 1 clustered and 4 non clustered), but in the sys.dm_db_index_usage_stats there are only two rows returned for indexes on this table.
why are there not 5 rows returned for the object from sys.dm_db_index_usage_stats?

The sys.dm_db_index_usage_stats includes indexes that are actually used. If an index is not used, it will not have an entry here. You can write a few queries that would use a particular index, and then have a look at this dynamic management view.

- Andras

Andras Belokosztolszki, MCPD, PhD
GoldenGate Software

winston Smith SSCoach Points: 19500 More actions · Answer 3

absolutely correct Andreas. I can see now there are a number of tables with indexes that are not regularly used ( although the server wsa rebooted only 3 days ago so they may be used in weekly reports or something.

thanks for the help.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Also sysindxes shows statistics, while sys.dm_db_index_usage_stats won't.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass