June 5, 2008 at 1:21 pm
I am doing the following query:
select i.name, s.user_seeks, s.user_scans,s.last_user_seek,s.last_user_scan
from sys.dm_db_index_usage_stats s
inner join sys.indexes i
on s.object_id = i.object_id
and s.index_id = i.index_id
order by i.name
This query is not showing information for all the indexes, for example this query is returning information for only 2 indexes for tableA although there are 6 indexes created on tableA.
How can I get similar information for other 4 indexes or why don't they show up in this query??
June 5, 2008 at 1:25 pm
Per Books Online, there is no data in that view for a given index till something is done to the index that would increment one of the counters.
If you change to a left outer join, instead of an inner join, from sys.indexes to this view, you'll get a complete list of indexes, and the ones that don't have data from this view, haven't had any updates, etc., done to them.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 5, 2008 at 1:39 pm
Thanks for the quick response!
Using Left Outer Join solved the problem.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply