Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Missing Index DMVs not sync Expand / Collapse
Author
Message
Posted Thursday, February 5, 2009 7:41 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, September 17, 2013 7:15 AM
Points: 7, Visits: 259
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!
Post #650866
Posted Thursday, February 5, 2009 8:02 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 9:58 AM
Points: 13,872, Visits: 9,600
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
Post #650888
Posted Thursday, February 5, 2009 8:24 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, September 17, 2013 7:15 AM
Points: 7, Visits: 259
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.
Post #650923
Posted Thursday, February 5, 2009 2:40 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 9:58 AM
Points: 13,872, Visits: 9,600
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
Post #651293
Posted Monday, September 27, 2010 8:55 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, December 15, 2014 1:02 PM
Points: 1,414, Visits: 4,545
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


https://plus.google.com/100125998302068852885/posts?hl=en
http://twitter.com/alent1234
x-box live gamertag: i am null
[url=http://live.xbox.com/en-US/MyXbox/Profile[/url]
Post #993715
Posted Tuesday, September 28, 2010 5:08 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, December 17, 2014 2:16 AM
Points: 2,840, Visits: 3,983
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
Post #994322
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse