I’ve shown before how to use the DMVs that read the plan cache as a way to connect the missing indexes suggestions with specific queries, but the other place to find missing index suggestions is the Query Store.
Pulling from the Query Store
The plans stored in the Query Store are exactly the same as the plans stored within the plan cache. This means that the XML is available and you can try to retrieve information from it directly, just as we did with the missing index queries against the DMVs. Here’s the query modified for the Query Store:
WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan') SELECT qsqt.query_sql_text, rts.plan_id, rts.NumExecutions, rts.MinDuration, rts.MaxDuration, rts.AvgDuration, rts.AvgReads, rts.AvgWrites, qsp.QueryPlan, qsp.QueryPlan.value(N'(//MissingIndex/@Table)', 'NVARCHAR(256)') AS TableName, qsp.QueryPlan.value(N'(//MissingIndex/@Schema)', 'NVARCHAR(256)') AS SchemaName, qsp.QueryPlan.value(N'(//MissingIndexGroup/@Impact)', 'DECIMAL(6,4)') AS ProjectedImpact, ColumnGroup.value('./@Usage', 'NVARCHAR(256)') AS ColumnGroupUsage, ColumnGroupColumn.value('./@Name', 'NVARCHAR(256)') AS ColumnName FROM sys.query_store_query AS qsq JOIN sys.query_store_query_text AS qsqt ON qsqt.query_text_id = qsq.query_text_id JOIN ( SELECT query_id, CAST(query_plan AS XML) AS QueryPlan, plan_id FROM sys.query_store_plan) AS qsp ON qsp.query_id = qsq.query_id JOIN ( SELECT qsrs.plan_id, SUM(qsrs.count_executions) AS NumExecutions, MIN(qsrs.min_duration) AS MinDuration, MAX(qsrs.max_duration) AS MaxDuration, AVG(qsrs.avg_duration) AS AvgDuration, AVG(qsrs.avg_logical_io_reads) AS AvgReads, AVG(qsrs.avg_logical_io_writes) AS AvgWrites FROM sys.query_store_runtime_stats AS qsrs GROUP BY qsrs.plan_id) AS rts ON rts.plan_id = qsp.plan_id CROSS APPLY qsp.QueryPlan.nodes('//MissingIndexes/MissingIndexGroup/MissingIndex/ColumnGroup') AS t1(ColumnGroup) CROSS APPLY t1.ColumnGroup.nodes('./Column') AS t2(ColumnGroupColumn);
A couple of notes on the query. I cast the query_plan as xml so that I can use the XQuery to pull out the information. It is possible that the plan might be so large that you get an error because of the limit on nesting levels within XML. Also, I aggregate the information from the sys.query_store_runttime_stats. You may want to modify this to only look at limited ranges. I’ll leave that to you as an exercise.
If you take that query as written and run it on a production database, it’s likely to cause some problems since there is no filtering in place. So, I strongly recommend before using this, you plan on filtering by query, object or text. However, once you do, this returns the query, the plan, and the missing indexes information.
If you want to learn more about how the Query Store works, I have an opportunity for you. I have a couple of upcoming all day seminars that include the Query Store as well as other tools built into SQL Server, all focused around making it easier for your to identify and tune queries: