Forcing a Query Plan with Query Store

2018-07-10 · mssql, performance, query-store

"It was fast yesterday and nothing was deployed." I hear that a lot, and it's almost always a plan change: statistics got updated, the plan fell out of cache, and the optimizer chose a worse plan on recompile. Before SQL Server 2016 my options were plan guides (fiddly) or query hints (needs a code change). Query Store keeps plan history for every query and lets you pin the good plan with one procedure call.

It works on 2016 or 2017, any edition, Standard and Express included. You need ALTER on the database to enable it and db_owner to force plans.

Turn it on first

ALTER DATABASE Sales SET QUERY_STORE = ON;
ALTER DATABASE Sales SET QUERY_STORE (
    OPERATION_MODE = READ_WRITE,
    DATA_FLUSH_INTERVAL_SECONDS = 900,
    INTERVAL_LENGTH_MINUTES = 60,
    MAX_STORAGE_SIZE_MB = 1024,
    QUERY_CAPTURE_MODE = AUTO,
    SIZE_BASED_CLEANUP_MODE = AUTO,
    CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30)
);

QUERY_CAPTURE_MODE = AUTO skips trivial ad hoc queries, which keeps the store from filling up with one-off statements. The default MAX_STORAGE_SIZE_MB of 100 is too small for a busy database; when it fills, Query Store switches itself to READ_ONLY and stops collecting.

Query Store needs time to collect data before it's useful, so enable it before you need it. I've turned it on in the middle of an incident before, and it's no help for the plan that already changed.

Find the regressed query

SSMS has a Query Store → Regressed Queries report under the database node that does this graphically. In T-SQL, look for queries with more than one plan and a large difference in average duration:

SELECT q.query_id, qt.query_sql_text,
       p.plan_id, p.is_forced_plan,
       rs.count_executions,
       rs.avg_duration / 1000 AS avg_duration_ms,
       rs.avg_logical_io_reads,
       rsi.start_time
FROM sys.query_store_query q
JOIN sys.query_store_query_text qt ON qt.query_text_id = q.query_text_id
JOIN sys.query_store_plan p ON p.query_id = q.query_id
JOIN sys.query_store_runtime_stats rs ON rs.plan_id = p.plan_id
JOIN sys.query_store_runtime_stats_interval rsi ON rsi.runtime_stats_interval_id = rs.runtime_stats_interval_id
WHERE qt.query_sql_text LIKE '%GetOrdersByCustomer%'
ORDER BY q.query_id, rsi.start_time DESC;

You want a query_id with two or more plan_id values where the older plan has a much lower avg_duration_ms. Note both numbers.

Compare the plans

Pull the plan XML for both:

SELECT plan_id, CAST(query_plan AS xml) AS query_plan, last_compile_start_time
FROM sys.query_store_plan
WHERE query_id = 1187;

Click the XML to open the graphical plan. Typically the fast plan uses an index seek with a nested loops join and the slow one a scan with a hash match, because the recompile happened with an unusual parameter value. If both plans look reasonable for different parameters, this is parameter sniffing and forcing a plan is a trade-off; if one is simply bad, forcing is the right answer.

Force the good one

EXEC sp_query_store_force_plan @query_id = 1187, @plan_id = 2231;

The effect is immediate for new executions. Existing cached plans are invalidated so the next execution recompiles and uses the forced plan.

In SSMS, the same thing is the Force Plan button in the Tracked Queries report after selecting the query and clicking the plan you want.

Forcing can fail quietly

A forced plan can fail to apply, for example when an index it relies on is dropped or renamed. Query Store does not raise an error to the application; it falls back to normal optimization and records the failure:

SELECT q.query_id, p.plan_id, p.is_forced_plan,
       p.force_failure_count, p.last_force_failure_reason_desc
FROM sys.query_store_plan p
JOIN sys.query_store_query q ON q.query_id = p.query_id
WHERE p.is_forced_plan = 1;

last_force_failure_reason_desc values like NO_INDEX or NO_PLAN tell you what went wrong. I check this view once a week.

Did it work?

Run the query a few times, then check that new runtime stats are attached to the forced plan and that the duration is back to normal:

SELECT p.plan_id, p.is_forced_plan, rs.count_executions, rs.avg_duration / 1000 AS avg_duration_ms, rs.last_execution_time
FROM sys.query_store_plan p
JOIN sys.query_store_runtime_stats rs ON rs.plan_id = p.plan_id
WHERE p.query_id = 1187
ORDER BY rs.last_execution_time DESC;

You can also confirm in the actual execution plan: the SELECT operator's properties include Use plan = True when a forced plan was applied.

Problems

Msg 12406: Query Store is in read-only mode. The store hit MAX_STORAGE_SIZE_MB. Increase it and set OPERATION_MODE = READ_WRITE again, or purge with ALTER DATABASE Sales SET QUERY_STORE CLEAR.
Msg 12408: Plan forcing is not supported for this query type. Some statements cannot be forced, notably queries against a linked server and certain cursor operations.
The forced plan is used but the query is still slow. The plan shape is the same but the data changed, or the slow part is blocking or a missing index rather than plan choice. Forcing is not a substitute for tuning.
Query Store shows a query but no runtime stats. DATA_FLUSH_INTERVAL_SECONDS has not elapsed yet. Stats live in memory until flushed; EXEC sp_query_store_flush_db forces a flush.

Forcing a plan is a bandage, not a fix. It buys time to find out why the plan changed. Once you know, un-force with sp_query_store_unforce_plan and let the optimizer prove it can do it on its own.