Most of you would know that using query store one can force query plans. Is there a need to check what plans are forced? Why?
Fundamentally, two important reasons for checking forced plans.
1) To check if the query is yielding the desired performance?. The performance needs to be obviously better than the traditional query performance in most of the cases.
2) To look for query store failures while forcing plans.
This post will deal with point 2, query store failures while forcing plans.
What are query store failures?
Query store failures are events where query store is unable to force the plan it is supposed to.
For example, - Let's say you have set the query store to force the index "NCIX_dt_present" for a particular query, and assume you have dropped the index, then query store does the following
1) Ignores the plan to be forced and picks up the next best query plan of optimizer's choice
2) Marks the failure to force plan on query store on the table "sys.query_store_plan" in the column "force_failure_count"
Fundamentally, two important reasons for checking forced plans.
1) To check if the query is yielding the desired performance?. The performance needs to be obviously better than the traditional query performance in most of the cases.
2) To look for query store failures while forcing plans.
This post will deal with point 2, query store failures while forcing plans.
What are query store failures?
Query store failures are events where query store is unable to force the plan it is supposed to.
For example, - Let's say you have set the query store to force the index "NCIX_dt_present" for a particular query, and assume you have dropped the index, then query store does the following
1) Ignores the plan to be forced and picks up the next best query plan of optimizer's choice
2) Marks the failure to force plan on query store on the table "sys.query_store_plan" in the column "force_failure_count"
For example,
Consider the following query
Select session_id,cpu_time,dt,logical_reads
from [dbo].[Process_monitor]
WHERE dt between '20150715' and '20150715 00:15'
Refer to the screenshot from query store
Plan 99 uses "NCIX_dt_present"( a non clustered Index ) for the query
To ensure that the query always uses the Index "NCIX_dt_present", lets force the plan 99 on query 92 as shown below.
Run the select query few times and verify if plan is getting forced. Now lets drop the index
DROP INDEX Process_monitor.NCIX_dt_present
GO
Re run the select query again few times
Select session_id,cpu_time,dt,logical_reads
from [dbo].[Process_monitor]
WHERE dt between '20150715' and '20150715 00:15'
List of query store failures can be found from the query below
SELECT
qt.query_sql_text,q.query_id,
CAST(query_plan AS XML) AS 'Execution Plan',
qp.force_failure_count,qp.last_force_failure_reason_desc,
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
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
AND qp.force_failure_count > 0
Order by rs.last_execution_time
qp.force_failure_count indicates the number of failures of query store to force plan. qp.last_force_failure_reason_desc" gives the reason why plan couldn't be forced.
It is strongly recommended to track query store failures and forced plans to avoid unexpected performance results.
