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.

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 :)

Query Store - Part 3 - How it works?

Query Store collects 2 important kinds of data. They are

1) Compile time statistics - Query Plan, Estimated Costs, Estimated number of rows etc..

2) Runtime statistics - Cost of the query, row count, IO cost, memory, Degree of parallelism etc.

How it actually stores is, when plan is loaded to plan cache, its compile time data like query plan, estimated costs are loaded into plan store. Subsequently, as the plan gets reused, the runtime stats like actual cost, memory used, and actual row count are aggregated and stored. Runtime stats are aggregated at a frequency or time period defined by "Statistics Collection Interval" setting explained here. Meaning, if one sets "Statistics Collection Interval" as 10 minutes, then Avg/Min/Max/Std Dev values of runtime stats are grouped for 10 minute intervals and stored in query store. For ex - For each query plan avg running duration, IO, memory for 9:40 to 9:50, 9:50 to 10:00, 10:00 to 10:10 and so on is stored

Runtime stats are recorded asynchronously. Query plans meta data views can be used to view compile time stat and run time stats. They are

Compile time data  / Plan store:

1) Sys.query_store_query_text
2) Sys.query_store_query
3) Sys.query_store_context_settings
4) Sys. query_store_plan

Run time data:

1) query_store_runtime_stats
2) query_store_runtime_stats_interval

How to use these effectively, is coming up in next post

Query Store – Part 2 - Configuration Settings

Introduction to Query Store covered here.
Now for the second part on “How to Configure?”

Query Store is configured at the database level. Right Click on the Database-> Properties ->QueryStore

* Operation Mode: To turn the query store on set "Operation Mode" to "Read Write". At "Read Write" mode, query store starts to record the query plans

* Query Store Retention: One can set a max size to the data collected by query store. Size consumed includes the query  plans recorded size and all the other statistics size recorded   by query store. Size  specified in MB. 

* Stale Query Threshold: Purges the plans older than the specified date. Helps in query store not using up the total storage limit set by "Query Store  Retention"

* Size Based Cleanup: One can set to "Auto" / "Off". Recommended to set to "Auto". Once set to "Auto", query store automatically triggers  the cleanup process if the query store's storage usage is close to its limit set by "Query Store Retention". If set to "off", once the query store's usage reaches its limit, query store stops recording the data and slips into "Read Only" Operational mode.

* Statistics Collection Interval: Indicates the frequency at which query store runtime statistics are collected. Statistics collected are run time statistics of query plans like number of executions, cost per executions, row count etc.  Shorter Statistics collection interval provided   will help the statistics collection to be lot more granular but at the cost of additional storage.  Shorter Collection intervals doesn't have adverse impact  on performance and will have only additional storage cost. 

* Data Flush Interval: Indicates the frequency at which the query store statistics and plans are flushed to disk. 

Query Store Capture Mode: "All" captures all the query plans generated

We will cover how query store collects and stores the data in the next post

SQL Server 2016 Query Store - Gentle Introduction - 1

Query Store is perhaps one of the path breaking features to release in SQL Server 2016. This post will give a quick introduction to the same.

What is this query store?

Short answer: Records / Stores every query plan generated along with their run time statistics like Execution Count, Row count, Execution cost, IO and memory usage on to the user database.

What are the key benefits?

1) One can track the query run time performance easily at any point in time
2) Track change of a query plan or change in performance of a particular query over a period of time
3) Track regression - Find which queries started performing badly 
4) Query plans will be part of user database backup
5) Ability to force plans via user graphical 
interface ( unlike plan guides which demanded some scripting)

What else we get?

4 wonderful reports

1) Regression report -> To track queries that have performed badly over a period of time. One gets to compare historical performance against recent  performance 

2) Top consuming queries -> To find the poor performing queries by CPU / duration /IO last one hour etc

3) Overall performance report-> Reports average response times, total queries executed etc

4) Tracked queries -> useful to see the trend in performance of a particular query

How to configure?

Right Click on the Database -> Properties -> Query Store -> Set Operation mode to read write.
Rest can be left at default to get it started or you may use the script below

USE [master]

GO

ALTER DATABASE [DBNAME] SET QUERY_STORE = ON

GO

ALTER DATABASE [DBNAME]

SET QUERY_STORE (OPERATION_MODE = READ_WRITE,

 DATA_FLUSH_INTERVAL_SECONDS = 60, INTERVAL_LENGTH_MINUTES = 1)

GO

That's a decent start with query store. Upcoming posts will cover various bells and whistles in it.