SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


DMV sys.dm_db_missing_index_group_stats returns no rows


DMV sys.dm_db_missing_index_group_stats returns no rows

Author
Message
sqlservercentral-1034611
sqlservercentral-1034611
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 34
Hi All,

I am using a script to review missing indexes on various SQL Servers.

Most of the time, it works just fine.

Sometimes, however, certain DMVs that are part of the script, just don't return any rows.

Usually, the main issue is sys.dm_db_missing_index_group_stats.

1. I know that restarting the service clears DMVs - that's not it :-)
2. I know I need the VIEW SERVER STATE permission - that's not it either :-)
3. I know I need actual missing indexes:

select * from sys.dm_db_missing_index_details

returns 573 rows, yet select TOP 1 * from sys.dm_db_missing_index_group_stats
returns 0 rows.

This renders all the various missing index scripts useless, because the JOIN doesn't work, and so the script doesn't return any results.

I keep running into servers with this issue, and so far, only restarting the service has resolved it. Data collection starts from scratch, and within just a few minutes, I get data in the sys.dm_db_missing_index_group_stats.

Check back a few days, weeks or sometimes months later, and the DMV again returns no results - same user, same DB, etc.

I have Googled the heck out of this issue and can't find a solution. It is frustrating, since missing indexes are often a major issue of the application I am troubleshooting on many different SQL Servers.

Any help would be greatly appreciated :-)
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)

Group: General Forum Members
Points: 99703 Visits: 33014
It's not something I've run into before. Are you up to date on the service packs? Have you looked to install any of the cumulative updates? I'd start there.

Just a question. How do you know what queries you're helping using the missing index information?

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
flagamba
flagamba
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 Visits: 71
I have the same problem, but no solution ...
sqlGDBA
sqlGDBA
Mr or Mrs. 500
Mr or Mrs. 500 (523 reputation)Mr or Mrs. 500 (523 reputation)Mr or Mrs. 500 (523 reputation)Mr or Mrs. 500 (523 reputation)Mr or Mrs. 500 (523 reputation)Mr or Mrs. 500 (523 reputation)Mr or Mrs. 500 (523 reputation)Mr or Mrs. 500 (523 reputation)

Group: General Forum Members
Points: 523 Visits: 315
Same problem - no solution. I have servers where they do return data, and some on which they seem to be empty. I wish I could get a reply without having to burn a support ticket with MS...;-)
sqlGDBA
sqlGDBA
Mr or Mrs. 500
Mr or Mrs. 500 (523 reputation)Mr or Mrs. 500 (523 reputation)Mr or Mrs. 500 (523 reputation)Mr or Mrs. 500 (523 reputation)Mr or Mrs. 500 (523 reputation)Mr or Mrs. 500 (523 reputation)Mr or Mrs. 500 (523 reputation)Mr or Mrs. 500 (523 reputation)

Group: General Forum Members
Points: 523 Visits: 315
Update : I did burn a ticket with MS support for this. As usual, something as abstrtact as this got escalated, and it turns out that the escalation engineer also had a few suprises from this. Bottomline is that the group stats DMV will hold rows only for queries that actually have a cost associated with it(as opposed to a query run with show plan on only, which would result in a compilation/production of a plan, but no actual execution of the plan). The more interesting and real reason for the absence of data would be that the other DMV's(sys.dm_db_missing_index_details & sys.dm_db_missing_index_groups) would be collecting data even for those queries which were just compiled(and not actually run), and the kicker is that there is a hardcoded limit of 600 rows in the other two DMV's, and they never get purged(Which is a bug - fixed in SQL 2012 but no plan to fix in SQL 2008 R2). So the associated scans/seeks info which should have been collected for all legitimate associated rows in the other two DMV's just doent get recorded since the other two are still full of rows for those "compiled only" queries.

All in all, rather convoluted(it would seem) and somewhat explanatory. One question that the MS reps didnt have an answer for me on is how those "Compiled only" queries were getting collected here. I know we dont run queries on our servers that "just compile only". I would have done that if I was debugging a query(to just show estimated plans, etc.) but not 600 such queries !!

Anyway, the bottomline is that the escalation engineer agreed that as they stand, these DMV's cannot be dependably used for tracking missing index stats and that there are no plans to address this in anything earlier than SQL 2012.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search