SQL Server 2019: dm_db_missing_index_group_stats_query DMV

[erikdarlingdata]

Is your feature request related to a problem? Please describe.
SQL Server 2019 introduced a new DMV, called dm_db_missing_index_group_stats_query that includes some interesting columns. It's not documented so far, but it provides some information that can be used to tie missing index requests to queries.

Describe the solution you'd like
I'd like to get this added, but I'm not sure which script it makes more sense in, because it involves a crossover.

Either:

• BlitzCache would have to peep in missing index DMVs, or
• BlitzIndex would have to peep at the plan cache for query bits.

Describe alternatives you've considered

• This isn't a replacement for dm_db_missing_index_group_stats because it clears out when query plans go away
• Adding a query_more_info column to BlitzIndex is simple but maybe feels incomplete
• Tying it to how BlitzCache parses plan XML to produce missing indexes would be ugly
• Extending the @Mode = 3 output of BlitzIndex to run a simple plan cache query would probably be doable, but it'd have to either be entirely dynamic, or dump into a #temp table with a dynamic update

Are you ready to build the code for the feature?
Yes, when there's a decision on approach.

[erikdarlingdata]

Any feedback on this from out there in the world?

[EmanueleMeazzo]

Better late than ever, but I would add it to BlitzIndex in order to quickly get an idea about the queries that boop the index

[BrentOzar]

Erik - do you still wanna do this? If not, I'll close it.

[erikdarlingdata]

Yeah, totally. I was hoping for some more feedback on it from the community, but I'll figure something out.

[BrentOzar]

I added a first step on it. The #MissingIndexes table now has a sample_query_plan column. I'm taking the:

• Top 1 row from sys.dm_db_missing_index_group_stats_query order by (q.user_seeks + q.user_scans) desc
• Top 1 row from sys.dm_exec_query_stats order by total_logical_reads desc

This particular PLAN may not actually be the one that's producing the missing index request - but the QUERY should be the same. If you've got multiple queries producing the plan, this isn't going to give you all of them either, just one as a starting point.

If you call sp_Blitz @TableName = 'Users', the missing index section has this sample plan at the far right.

If you call sp_Blitz @mode = 3, this is a new column on the far right.

Things left that someone else can do if they want:

• Add it to Mode 0 & 4
• Add an sp_BlitzCache call with the query plan hashes (rather than just getting the one top plan)

[BrentOzar]

Just making a note in here in case anybody tries to test this - the current state is a disaster, and it almost never shows the right query. I have fixing to do in there.

[BrentOzar]

Just FYI for readers - Erik asked Microsoft to document the DMV: https://github.com/MicrosoftDocs/sql-docs/issues/5862

[erikdarlingdata]

It's going really well, too 😔

[BrentOzar]

Woohoo! This went live: https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-db-missing-index-group-stats-query-transact-sql?view=sql-server-ver15

[erikdarlingdata]

I was reviewing this and think I found the issue.

Everything was okay, except where the outer correlation is:

If I change it to this, it seems to bring back the correct query plan:

But I'm willing to concede that it has been a long Sunday and I'm missing something obvious.

[erikdarlingdata]

oh, wait, the inner correlation is correct. if we just get rid of the outer one all together it's fine.

[BrentOzar]

Eureka! That's it. Nice work, sir. Merging into the dev branch, will be in the April release with credit to you in the release notes.