Query Store - Part 4 - Find the plans and queries forced by Query Store

As most of you know, one of the most fundamental uses of query store is to force query plans. Unlike query hints, Query store doesn't demand code a change nor it is complex to implement like plan guides. One can force a plan without make a query / code change and it is pretty simple to achieve the same via user friendly GUI. To figure out how to force a plan, please refer here

But, one of the important things to do, after forcing plans using query store is to track the plans that are being forced. One can do that using the following query

SELECT
qt.query_sql_text,q.query_id,
CAST(query_plan AS XML) AS 'Execution Plan',
rs.first_execution_time, rs.last_execution_time,
rs.count_executions,
rs.avg_duration,
rs.avg_rowcount,
rs.last_logical_io_reads,
rs.avg_cpu_time,
rs.avg_query_max_used_memory, qp.force_failure_count
FROM sys.query_store_plan qp
INNER JOIN sys.query_store_query q
ON qp.query_id = q.query_id
INNER JOIN sys.query_store_query_text qt
ON q.query_text_id = qt.query_text_id
INNER JOIN sys.query_store_runtime_stats rs
ON qp.plan_id = rs.plan_id
WHERE qp.is_forced_plan = 1
Order by rs.last_execution_time

The reasons why one needs to track forced plans are interesting. That will be covered in the next post :)

Tweet