DB-유

Query Store in SQL Server 2016 and 2017

· mssql, query store, performance, sql server 2016

"It was fast last week and slow now, and we changed nothing." I dislike that call more than any other. Before 2016 all I had was the plan cache, which is wiped on restart and quietly evicts old plans, or a trace I should have started earlier. Query Store records every query, every plan and runtime stats per interval inside the database itself, so history survives restarts, failovers and restores.

I now turn it on for every user database that matters. It works on SQL Server 2016 or 2017 in any edition (Standard and Express included), and you need some free space: the default cap is 100 MB on SQL Server 2016 and 1 GB on SQL Server 2017, fine for small systems, too small for busy OLTP.

Enable it

Here are the settings I use:

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

QUERY_CAPTURE_MODE = AUTO skips trivial and rarely executed ad hoc statements, which keeps the store from filling up with one-off queries. INTERVAL_LENGTH_MINUTES = 60 is coarse enough for long-term trends; use 15 if you want to see spikes within the hour. On 2017, add WAIT_STATS_CAPTURE_MODE = ON to also record wait categories per plan.

Check the effective state:

SELECT actual_state_desc, readonly_reason, current_storage_size_mb, max_storage_size_mb, query_capture_mode_desc
FROM sys.database_query_store_options;

If actual_state_desc says READ_ONLY and readonly_reason is 65536, the store is full; raise MAX_STORAGE_SIZE_MB or lower STALE_QUERY_THRESHOLD_DAYS.

Finding the regressed queries

SSMS 17 adds a Query Store folder under the database with reports such as Regressed Queries and Top Resource Consuming Queries. The reports are good for a first look; for anything specific I go to the views. This finds queries that have more than one plan and shows the average duration per plan:

SELECT q.query_id, qt.query_sql_text, p.plan_id,
       SUM(rs.count_executions) AS executions,
       AVG(rs.avg_duration) / 1000 AS avg_duration_ms,
       AVG(rs.avg_logical_io_reads) AS avg_reads,
       MIN(rs.first_execution_time) AS first_seen, MAX(rs.last_execution_time) AS last_seen
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 i ON i.runtime_stats_interval_id = rs.runtime_stats_interval_id
WHERE i.start_time > DATEADD(day, -7, GETUTCDATE())
  AND q.query_id IN (SELECT query_id FROM sys.query_store_plan GROUP BY query_id HAVING COUNT(*) > 1)
GROUP BY q.query_id, qt.query_sql_text, p.plan_id
ORDER BY q.query_id, avg_duration_ms DESC;

A query with two plans where one averages 50 ms and the other 8,000 ms is the classic parameter sniffing pattern. The query_plan column of sys.query_store_plan contains the XML; cast it to xml and click it in SSMS to see the graphical plan.

Forcing the good plan

Once you know which plan_id is the good one:

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

From then on the optimizer uses that plan for that query text whenever it is valid. Forcing survives restarts and is stored with the database. To undo:

EXEC sp_query_store_unforce_plan @query_id = 1417, @plan_id = 2231;

See what is currently forced and whether forcing is failing:

SELECT query_id, plan_id, is_forced_plan, force_failure_count, last_force_failure_reason_desc
FROM sys.query_store_plan WHERE is_forced_plan = 1;

A forced plan is a bandage. The permanent fix is usually a rewritten query, an index, or OPTION (RECOMPILE) on the affected statement, but forcing buys you time at 3 a.m.

Automatic plan correction (2017 only)

SQL Server 2017 can do the forcing on its own. When a new plan is measurably slower than the previous one, it forces the last good plan and keeps checking that it is still better:

ALTER DATABASE Sales SET AUTOMATIC_TUNING (FORCE_LAST_GOOD_PLAN = ON);

Even without enabling it, sys.dm_db_tuning_recommendations lists what it would have done, with the T-SQL to apply it in the details JSON column. I run it in recommendation-only mode for a few weeks on a new system before letting it act.

Is the forced plan being used?

Run the query that was slow, then check that executions are landing on the forced plan:

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

Runtime stats are written to disk every DATA_FLUSH_INTERVAL_SECONDS, but the views read the in-memory part as well, so new executions show up immediately.

Odd behaviour

  • Query Store shows READ_ONLY after a restore to a server with less disk, or it was turned read-only by MAX_STORAGE_SIZE_MB. ALTER DATABASE Sales SET QUERY_STORE CLEAR empties it if the history is not needed; otherwise raise the limit.
  • "Query Store is not supported on the system databases" – it can only be enabled on user databases. master, tempdb and msdb are out.
  • Forced plan silently ignored with last_force_failure_reason_desc = NO_INDEX. Someone dropped or renamed an index the plan depends on. Unforce, fix the index, re-force.
  • Many query_id rows for what looks like the same statement. Query Store keys on the exact text including whitespace and on the context settings (ANSI options, default schema). Applications that build SQL with literal values create one entry per value; that is the case for QUERY_CAPTURE_MODE = AUTO and, on the application side, for parameterisation.
  • Early 2016 builds had performance issues with Query Store under heavy ad hoc load; make sure you are on SP1 CU or later before enabling it on a busy system.

I wish I'd had this ten years ago. Turn it on before you need it, because the history only exists if it was already running.