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

DMV sys.dm_db_missing_index_group_stats returns no rows Expand / Collapse
Author
Message
Posted Wednesday, October 5, 2011 5:31 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, May 30, 2014 2:21 PM
Points: 1, 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
Post #1186269
Posted Thursday, October 6, 2011 4:17 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 6:53 AM
Points: 13,890, Visits: 28,285
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
SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1186400
Posted Friday, January 6, 2012 3:25 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 7:47 AM
Points: 2, Visits: 61
I have the same problem, but no solution ...
Post #1231763
Posted Tuesday, June 11, 2013 1:07 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, November 22, 2013 10:13 AM
Points: 79, Visits: 200
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...
Post #1462338
Posted Friday, June 28, 2013 9:09 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, November 22, 2013 10:13 AM
Points: 79, Visits: 200
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.
Post #1468585
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse