Missing Index DMVs not sync

Question

Missing Index DMVs not sync

Mark Salvador

SSC Journeyman

Points: 77
More actions
February 5, 2009 at 7:41 am

#206284

Hi guys!
I tried looking for missing index and I used this query:
select *
from sys.dm_db_missing_index_group_stats as migs
inner join sys.dm_db_missing_index_groups as mig on migs.group_handle = mig.index_group_handle
inner join sys.dm_db_missing_index_details as mid on mig.index_handle = mid.index_handle
I got no result after I run it.
Though I made a separate SELECT statement for each DMVs but I've got results. It seems that sys.dm_db_missing_index_group_stats and sys.dm_db_missing_index_groups were not sync because I run this query with no result:
select *
from sys.dm_db_missing_index_group_stats as migs
inner join sys.dm_db_missing_index_groups as mig on migs.group_handle = mig.index_group_handle
Does the stats needs to flush or something to make it sync? Appreciate your help in advance.
Thanks!

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

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

GSquared SSC Guru Points: 260824 More actions · Answer 1

Try replacing your inner joins with left outer joins and see if you get what you need that way.

- 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

Mark Salvador SSC Journeyman Points: 77 More actions · Answer 2

When I left join them like this:

select *

from sys.dm_db_missing_index_group_stats AS migs

left JOIN sys.dm_db_missing_index_groups AS mig ON migs.group_handle = mig.index_group_handle

left JOIN sys.dm_db_missing_index_details AS mid ON mig.index_handle = mid.index_handle

I got data coming from dm_db_missing_index_group_stats but got NULL result in sys.dm_db_missing_index_groups and sys.dm_db_missing_index_details.

I have result though when I make a join between sys.dm_db_missing_index_groups and sys.dm_db_missing_index_details.

I am suspecting that dm_db_missing_index_group_stats could be corrupted or something.

GSquared SSC Guru Points: 260824 More actions · Answer 3

Have you read the MSDN article on using that query? It's entirely possible it simply doesn't have the data in it that you are looking for.

- 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

alen teplitsky SSC-Dedicated Points: 30014 More actions · Answer 4

Mark Salvador (2/5/2009)
When I left join them like this:
select *
from sys.dm_db_missing_index_group_stats AS migs
left JOIN sys.dm_db_missing_index_groups AS mig ON migs.group_handle = mig.index_group_handle
left JOIN sys.dm_db_missing_index_details AS mid ON mig.index_handle = mid.index_handle
I got data coming from dm_db_missing_index_group_stats but got NULL result in sys.dm_db_missing_index_groups and sys.dm_db_missing_index_details.
I have result though when I make a join between sys.dm_db_missing_index_groups and sys.dm_db_missing_index_details.
I am suspecting that dm_db_missing_index_group_stats could be corrupted or something.

is the null on the equality column? i've read that it means the query is using the between operator

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 5

Mark Salvador (2/5/2009)
I tried looking for missing index and I used this query:

but why are you looking for indexes in this DMV. ? did you get any bad performance from any script/SP ? or just to learning prospect?

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)