Monday, December 26, 2016

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.

1 comment:

jessica said...

Wow amazing i saw the article with execution models you had posted. It was such informative. Really its a wonderful article. Thank you for sharing and please keep update like this type of article because i want to learn more relevant to this topic.moved here school signs uk