July 12, 2022 at 7:52 pm
ScottPletcher wrote:Wingenious wrote:The missing index suggestions are not connected to specific queries in versions prior to SQL Server 2019. SQL Server 2019 added the sys.dm_db_missing_index_group_stats_query DMV. This DMV tells you which queries contributed to missing index suggestions.
As mentioned by Grant, you can also connect missing index suggestions to specific queries by finding the suggestions in the plan cache. There's an easy way to do it, with the IndexNeedsPlus tool in the free SQLFacts toolkit. The toolkit was discussed in a series of articles here on SQLServerCentral. The most relevant article for the topic at hand is here.
I will say, again, that most often is wasted effort. You typically don't really need to know the underlying queries. Aside from the "TOP (nn)" bad I/O queries to find any truly awful queries, you don't generally need to go down to the query level.
Moreover, if as a DBA you're responsible for working across: hundreds of dbs; in half-a-dozen or so instances; with dozen(s)+ of developers; you won't have time to look at each and every query and its plan(s) anyway.
I did not say the query information was absolutely essential. I simply said it's available in SQL Server 2019 (or using SQLFacts), which previous posts did not mention.
I do not find the missing index suggestions to be useless, but nor do I think connecting them to specific queries is wasted effort. It's a great way to verify/quantify the impact of a new (or modified) index.
OK, so I have 200 queries using to a given index. How many of those can I review the plans for? And is that % of plans really useful?
Of course you will likely have some "monster" queries, and you will review query plans on those as needed. But for most routine queries, I don't think it's really all that valuable. And it is very time consuming.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing post 16 (of 15 total)
You must be logged in to reply to this topic. Login to reply