Query Store- Part 5: Checking forced plans and Query Store Failures

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" 

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.