December 9, 2019 at 10:42 am
is there relation between Impact from sys.dm_db_missing_index_group_stats and Impact from cache plan?
Thanks for all.
December 9, 2019 at 12:53 pm
The information put into the missing index DMVs is taken from the information put into execution plans. So, yes. they are directly correlated.
I strongly recommend you abandon the missing index DMVs. They have zero correlation with queries. You can't tell which query it's suggesting the missing index for (and they are very much suggestions, don't simply assume they're correct and implement them without testing). Without that correlation, you may be adding an index, and all the associated management and storage overhead, for a query that was run one time and will never be run again.
If you want to use the missing index information, I suggest querying the plans in cache or the plans in Query Store to find missing index data there. The reason for this is you can correlate the missing index to a query.
Also, side note, the impact statement is just a calculation based on the statistics available to the optimizer. It's not an accurate measurement based on your query, your data and your machine. I'm not saying don't use it. Do. Just understand what it's telling you.
"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
December 9, 2019 at 1:11 pm
Hello Grant, thank you very much for your answer, it was a doubt that I had.
December 9, 2019 at 1:12 pm
Not a problem. We're all here to help out when and where we can.
"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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply