SQL Server Query Store: finding the slow query

To find the slow query on SQL Server 2022, start with Query Store. It is on by default for new databases, it records every query's plans and run-time statistics in hourly buckets, and it survives restarts, so "what got slow yesterday afternoon" has an answer. Open the Top Resource Consuming Queries report in SSMS, sort by total CPU or logical reads, and the query at the top is almost always the one to fix. For what is slow right now - a request running at this moment, a session blocking others - the dynamic management views (sys.dm_exec_requests and friends) are the tool. This guide covers both, and what to do with a query once you have found it.

Everything here works on the Express edition. Query Store is available in every edition, and it matters more on Express than anywhere else: with four cores and about 1.4 GB of buffer pool, a single bad query is a large share of the machine.

What Query Store records#

Query Store sits inside each user database and captures, for every query it decides to track:

• The query text and a hash of it, so the same statement from different sessions is one query.
• Every plan the optimiser has produced for that query, with the plan XML.
• Run-time statistics per plan, per interval: executions, duration, CPU time, logical and physical reads, writes, memory grants, row counts, each as average, minimum, maximum and standard deviation.
• Wait statistics per plan, per interval (from SQL Server 2017), grouped into categories such as CPU, Buffer IO, Lock and Memory.

Because it keeps plans over time, Query Store answers the question the plan cache cannot: "this query was fast last week, what changed?" The plan cache only holds what is cached now, loses everything at restart, and evicts entries under memory pressure - which, on an Express instance, happens constantly.

The data lives in the database itself. It goes with the database in a backup, comes back on restore, and - on Express - counts towards the 10 GB limit per database. That last point shapes how you configure it.

Turning it on and sizing it#

Check its state first:

SELECT actual_state_desc, desired_state_desc, readonly_reason,       current_storage_size_mb, max_storage_size_mb,       query_capture_mode_desc, interval_length_minutesFROM sys.database_query_store_options;

Databases created on SQL Server 2022 have Query Store on in read-write mode. Databases restored or upgraded from older versions keep whatever setting they had, which is often off. To turn it on with settings suitable for a small server:

ALTER DATABASE [appdb] SET QUERY_STORE = ON (    OPERATION_MODE = READ_WRITE,    QUERY_CAPTURE_MODE = AUTO,    MAX_STORAGE_SIZE_MB = 300,    INTERVAL_LENGTH_MINUTES = 60,    CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30),    SIZE_BASED_CLEANUP_MODE = AUTO);

On a 10 GB Express database, the default 1,000 MB ceiling is a tenth of your allowance. 200 to 500 MB holds a month of history for a typical application. Leave capture mode on AUTO: ALL on an application that sends unparameterised SQL fills the store with thousands of one-off queries.

If Query Store reaches its maximum size, it switches itself to read-only and stops recording, and readonly_reason says why. Size-based cleanup is meant to prevent that; if it happens anyway, raise the limit slightly or shorten the retention, then set OPERATION_MODE = READ_WRITE again.

The built-in reports#

In SSMS, expand the database, then Query Store. The reports that earn their place:

In Top Resource Consuming Queries, change the metric from the default to total CPU time or total logical reads, not the average. A query that takes 4 ms but runs 2 million times an hour costs far more than a 3-second report run twice a day, and averages hide it. The chart on the right shows every plan the selected query has used, one colour per plan; a query with two plans, one fast and one slow, is the clearest sign of a plan problem rather than an indexing one.

Finding the slow query with T-SQL#

The reports are views over a handful of catalog views, which you can query directly - useful on a server where you only have sqlcmd, or to save the results:

SELECT TOP (20)    q.query_id,    SUM(rs.count_executions)                                AS executions,    SUM(rs.avg_cpu_time * rs.count_executions) / 1000.0     AS total_cpu_ms,    SUM(rs.avg_duration * rs.count_executions) / 1000.0     AS total_duration_ms,    SUM(rs.avg_logical_io_reads * rs.count_executions)      AS total_reads,    MAX(qt.query_sql_text)                                  AS query_textFROM sys.query_store_runtime_stats AS rsJOIN sys.query_store_runtime_stats_interval AS i    ON i.runtime_stats_interval_id = rs.runtime_stats_interval_idJOIN sys.query_store_plan AS p        ON p.plan_id = rs.plan_idJOIN sys.query_store_query AS q       ON q.query_id = p.query_idJOIN sys.query_store_query_text AS qt ON qt.query_text_id = q.query_text_idWHERE i.start_time >= DATEADD(HOUR, -24, SYSDATETIMEOFFSET())GROUP BY q.query_idORDER BY total_cpu_ms DESC;

Times in Query Store are in microseconds, hence the division by 1,000. Change the ORDER BY to total_reads to find the queries reading the most pages - on a memory-capped instance, those are the ones pushing everything else out of the cache.

Waits tell you why a query is slow rather than just that it is:

SELECT TOP (20) p.query_id, ws.wait_category_desc,       SUM(ws.total_query_wait_time_ms) AS wait_msFROM sys.query_store_wait_stats AS wsJOIN sys.query_store_plan AS p ON p.plan_id = ws.plan_idGROUP BY p.query_id, ws.wait_category_descORDER BY wait_ms DESC;

CPU waits mean the query is doing work - fix it with a better plan or index. Buffer IO means it is reading pages from disk because they were not in memory. Lock means it is blocked by another session. Memory means it is waiting for a memory grant, often because a bad estimate asked for far too much.

What is slow right now: the DMVs#

Query Store looks back. For a problem happening this minute, ask the engine what it is doing:

SELECT r.session_id, r.status, r.command, r.wait_type, r.wait_time,       r.blocking_session_id, r.cpu_time, r.logical_reads,       r.total_elapsed_time / 1000 AS elapsed_s,       t.text AS sql_textFROM sys.dm_exec_requests AS rCROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS tWHERE r.session_id <> @@SPID  AND r.session_id > 50ORDER BY r.total_elapsed_time DESC;

A non-zero blocking_session_id means the request is waiting for a lock held by that session. Follow the chain to the session at its head - the one blocking others but not blocked itself - and look at what it is running, or at what it ran and never committed. sys.dm_exec_sessions gives its login, host and program name. Long blocking chains on a small server are usually one forgotten open transaction, the same culprit that stops a transaction log from truncating, covered in SQL Server recovery models and log growth.

For the heaviest queries since the plan cache was last cleared, sys.dm_exec_query_stats is the older alternative to Query Store:

SELECT TOP (10) qs.execution_count,       qs.total_worker_time / 1000 AS total_cpu_ms,       qs.total_logical_reads,       SUBSTRING(t.text, qs.statement_start_offset / 2 + 1,           (CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(t.text)            ELSE qs.statement_end_offset END - qs.statement_start_offset) / 2 + 1) AS statementFROM sys.dm_exec_query_stats AS qsCROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS tORDER BY qs.total_worker_time DESC;

It is instance-wide and needs no setup, but forgets everything at restart and whenever a plan leaves the cache.

Plan regressions and forcing a plan#

The most dramatic slowdowns are not caused by data growth. They are caused by a query switching to a different plan - after a statistics update, a restart, an index change, or a compatibility level upgrade. One day the query seeks; the next it scans, and nothing in the code changed.

Query Store makes this visible and fixable. In Regressed Queries or Tracked Queries, the plan chart shows the old plan's dots low and the new plan's dots high. Select the good plan and click Force Plan, or do it in T-SQL:

EXEC sp_query_store_force_plan @query_id = 412, @plan_id = 1187;-- Later, once the underlying cause is fixedEXEC sp_query_store_unforce_plan @query_id = 412, @plan_id = 1187;

From then on, SQL Server uses that plan for that query whenever it is still valid. Forcing is a tourniquet, not a cure. It stops the bleeding today, but the data will keep changing, and a plan that was right for last month's data may be wrong in six months. Note every forced plan, find out why the optimiser chose the bad one - stale statistics, a missing index, a skewed parameter - and unforce it once that is fixed. SQL Server can also force the last good plan automatically through its automatic tuning option, where the edition supports it; check before relying on it on Express.

SQL Server 2022 added Query Store hints, which attach a query hint to a query id without changing the application's code:

EXEC sys.sp_query_store_set_hints @query_id = 412,     @query_hints = N'OPTION (RECOMPILE)';

That is the way to fix a query generated by an ORM you cannot easily edit. sys.sp_query_store_clear_hints removes it.

Parameter sniffing and other usual causes#

When you have found the query, the cause is usually one of a short list:

• A missing or unhelpful index. The plan scans a large table or does thousands of key lookups. SQL Server indexes and execution plans covers reading the plan and designing the index.
• Parameter sniffing. A parameterised query is compiled once, for the first parameter values it sees, and that plan is reused for every later value. If the first call was for a customer with three orders and the next is for one with three million, the reused plan can be terrible. Queries With High Variation is where this shows. Fixes range from OPTION (RECOMPILE) on an infrequent query, to OPTIMIZE FOR a representative value, to a better index that suits both. At compatibility level 160, SQL Server 2022's parameter sensitive plan optimisation can keep several plans for some eligible queries, which helps but does not catch every case.
• Implicit conversions. A string parameter sent as nvarchar against a varchar column, so the index on that column cannot be used for a seek. Look for CONVERT_IMPLICIT in the plan.
• Stale statistics after a large load or delete. UPDATE STATISTICS dbo.Orders; and compare.
• Too many round trips. A page that runs the same small query 400 times in a loop. Each one is fast; the total is not. The total-CPU ranking catches these, and the fix is in the application, not the database. The general method in EXPLAIN ANALYZE and slow queries applies here too.

On Express, also read the waits against the edition's limits. Lots of Buffer IO waits on an instance with plenty of memory left means the working set exceeds the 1,410 MB buffer pool cap. More RAM on the plan will not change that; reading fewer pages will, through better indexes and narrower queries. Lots of CPU waits with all four cores busy is the compute cap, and the same advice applies.

A worked example: from complaint to fix#

The complaint is typical: "the orders page has been slow since Tuesday". Nobody changed the code. Here is the sequence that turns that into a fix, in about twenty minutes.

1. Narrow the window. Open Overall Resource Consumption for the last week. CPU jumps on Tuesday at about 03:00 and stays high. Something changed at that moment, and 03:00 is when the nightly statistics job runs.
2. Find the query. Open Top Resource Consuming Queries for the last 48 hours, metric total CPU time. The top query, by a wide margin, is the order list: SELECT ... FROM dbo.Orders WHERE CustomerId = @p0 ORDER BY CreatedAt DESC. It runs about 30,000 times an hour.
3. Look at its plans. The chart shows two plans. Plan 1187, used until Tuesday, averages 2 ms. Plan 1204, used since, averages 180 ms. This is a regression, not growth.
4. Compare the plans. Plan 1187 seeks on an index over CustomerId and does a few key lookups. Plan 1204 scans the clustered index and sorts. The estimated row count on the new plan is 40,000; the actual is 12. The plan was compiled after the statistics update, for a call with the one wholesale customer who has 40,000 orders, and it has been reused for every small customer since. That is parameter sniffing, triggered by the recompile that the statistics update caused.
5. Stop the bleeding. Force plan 1187. CPU drops within the minute, and the page is fast again.
6. Fix the cause. The old plan was only adequate, too - it needed key lookups. Create an index on (CustomerId, CreatedAt) that includes the columns the page reads. With a covering index, a seek is the best plan for every customer, large or small, so the optimiser has no bad choice to make whatever value it sniffs.
7. Unforce and verify. Unforce plan 1187. Watch Tracked Queries for the query over the next day: a new plan appears, using the new index, averaging under a millisecond, and it stays there through the next 03:00 statistics run.

The pattern generalises. Query Store narrows "the app is slow" to one query and one moment; the plans explain what changed; forcing buys time; a structural fix - an index, a rewritten predicate, a corrected parameter type - removes the reason to force anything. The one step people skip is the last one, and an unreviewed forced plan is how the same page goes slow again in six months, when the data has moved on and the pinned plan no longer suits it.

It also shows why total CPU, not average duration, is the right first ranking. The order list never appeared in any "slowest queries" list - 180 ms is not slow for one call - but at 30,000 calls an hour it was most of the server.

FAQ#

Does Query Store slow the database down?

The overhead is small - a few percent at most on typical workloads, and usually less. The cases that hurt are capture mode ALL with a flood of unparameterised queries, or a store that is too small and spends its time cleaning up. AUTO capture and a sensible size avoid both.

Is Query Store available on SQL Server Express?

Yes, in every edition since SQL Server 2016. It is on by default for new databases on SQL Server 2022. On Express, keep MAX_STORAGE_SIZE_MB modest, because the data counts towards the 10 GB limit per database.

Why is the query I care about not in Query Store?

With capture mode AUTO, queries that run rarely and cost little are not captured. It may also be in another database: Query Store is per database, and a query is recorded in the database it ran in. Check that Query Store was in READ_WRITE mode at the time.

How do I clear Query Store data?

ALTER DATABASE [appdb] SET QUERY_STORE CLEAR; removes everything collected so far. It is useful after a large application change, when old data would only confuse the reports. Note that it also discards the history you would need to compare before and after.

Should I force plans permanently?

No. A forced plan is a temporary fix while you find the real cause. Review forced plans after every significant data change or upgrade, and unforce them once the underlying index, statistics or query problem is solved.