October 5, 2011 at 5:31 pm
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 🙂
October 6, 2011 at 4:17 am
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 6, 2012 at 3:25 pm
I have the same problem, but no solution ...
June 11, 2013 at 1:07 pm
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...;-)
June 28, 2013 at 9:09 am
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.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply