Query Was Fast Yesterday, Slow Today: Find What Changed

The query was fast yesterday and slow today. One procedure went from milliseconds to seconds overnight, nobody deployed anything, the server looks bored, and the developer swears the code has not changed. They are right, it has not. The plan has.

SQL Server does not keep a plan forever. A statistics update, an index rebuild, a restart, a failover or plain memory pressure throws it away, and the next caller compiles a new one with whatever parameter value it happens to bring. This page is the order to check it in, with a reproduction on the lab instance so every number below is real. If the whole server is slow rather than one query, SQL Server Is Slow: What to Check First is the page you want.

CRITICAL, 115 times the baseline CPU, and total_plan_count 2. The recent averages are a blend of the good and bad executions, so the real damage per call is worse than 115x, which the next query shows. Take the query_id and ask Query Store which plan ran when:

SELECT p.plan_id,
       CONVERT(char(16), MIN(ri.start_time), 120) AS first_seen_utc,
       CONVERT(char(16), MAX(ri.end_time), 120) AS last_seen_utc,
       SUM(rs.count_executions) AS execs,
       CAST(AVG(rs.avg_logical_io_reads) AS int) AS avg_reads,
       CAST(AVG(rs.avg_duration) / 1000.0 AS decimal(9,2)) AS avg_ms
FROM sys.query_store_query AS q
JOIN sys.query_store_plan AS p ON p.query_id = q.query_id
JOIN sys.query_store_runtime_stats AS rs ON rs.plan_id = p.plan_id
JOIN sys.query_store_runtime_stats_interval AS ri
    ON ri.runtime_stats_interval_id = rs.runtime_stats_interval_id
WHERE q.object_id = OBJECT_ID('dbo.usp_CustomerOrderSummary')
GROUP BY p.plan_id
ORDER BY first_seen_utc;

That is the incident in two rows. Plan 9 had run since 11:16 at 8 reads a call. Plan 14 appeared at 12:21 and read 6,083 pages a call, 760 times the work, and took 114 ms against 7. Swap q.object_id = OBJECT_ID(...) for q.query_id = the id from the script if the query is ad hoc rather than inside a procedure.

Plan 14 lasted 51 executions. The lab instance is short of memory and evicts plans constantly, so the next compile came from a small customer and plan 9 came back on its own. In production that is the incident that “fixed itself by lunchtime” and comes back next week. One plan with a flat runtime means the plan did not change, and the answer is elsewhere: waits, blocking or a resource, or more rows than yesterday through the same plan.

SELECT s.name, sp.last_updated, sp.rows, sp.modification_counter
FROM sys.stats AS s
CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) AS sp
WHERE s.object_id = OBJECT_ID('dbo.Orders');

13:21:04, and plan 14 was first compiled at 12:21:04. That is the same second. The hour between them is the trap: Query Store stores its times in UTC, and last_updated is in the server’s local time, which on the lab is British Summer Time, an hour ahead. Convert one of them before you decide the two are unrelated.

The other triggers, documented rather than reproduced here:

• Index maintenance. A rebuild updates that index’s statistics with a full scan, so a nightly rebuild job is a statistics job as far as plans are concerned. Check when it ran.
• A restart, failover or memory pressure. A restart or failover empties the whole plan cache, memory pressure evicts plans from it, and each query that lost its plan recompiles with the first value it sees. The SQL Server error log has the restart or the failover, and on a busy instance the plan cache has no history before the event.
• A schema change nobody called a deployment. An index dropped or added, a column altered. Get Schema Change History reads the default trace for exactly this.
• A patch or a compatibility level change. A new CU can change optimiser behaviour, and a compatibility level change can switch the cardinality estimator and other optimiser behaviour with it. Get Patch Level confirms the build, and Compatibility Levels: What Actually Changes covers the other one.

Plan 9 was built for a customer with 2 rows: seek the index, look up 2 rows. Plan 14 was built for customer 1 and 106,000 rows: scan the whole table, which is the right plan for that customer and a terrible one for the 300 small customers who followed. Neither plan is wrong. The damage is reuse. Open both in SSMS from the Regressed Queries report or with SELECT CAST(query_plan AS xml) FROM sys.query_store_plan WHERE plan_id = 14;, and the parameter list on the root operator shows the compiled value.

The mechanism and its measured fixes are in Parameter Sniffing in SQL Server. If the recompile picked a plan that suits nobody, rather than one customer, go back to the statistics: a stale or low sample histogram produces a bad estimate for every value, and that fixes with an update, not a hint.

SELECT name, type, reason, valid_since, state, details
FROM sys.dm_db_tuning_recommendations;

That is automatic plan correction detecting the regression from 6 executions and writing the fix, then not applying it because FORCE_LAST_GOOD_PLAN is not switched on for the database. Worth knowing it is there: on SQL Server 2017 and later, with Query Store on, this view may already hold your answer. Forcing it by hand:

EXEC sys.sp_query_store_force_plan @query_id = 9, @plan_id = 9;

SELECT p.plan_id, p.is_forced_plan, p.last_force_failure_reason_desc
FROM sys.query_store_plan AS p
WHERE p.query_id = 9
ORDER BY p.plan_id;

The recommendation’s state flipped to match:

Plan 27 is not a mistake. After the force, the procedure was recompiled and run for customer 1, then customer 42. Query Store logged those runs as a new plan 27, which has plan 9’s seek shape but was compiled with customer 1’s estimate of 106,000 rows, so its hash differs. The force is still recorded on plan 9 and the shape held. Customer 42 read 9 pages, back to normal.

Now the cost. Customer 1, the big account, on the forced seek plan: 325,820 logical reads, against 6,083 on its own scan plan. Forcing the last good plan made that call more than 50 times worse to make everyone else fast again. On this data no single plan suits both, and forcing plan 9 is exactly what automatic correction would have done on your behalf. That is the honest case for treating a force as a splint:

• Write down the query, the plan and the date, and check the force is still holding with Get Query Store Forced Plans. If the forced plan can no longer be built, say because its index was dropped, the force fails silently: the query compiles a plan of its own with no error to the caller, and only force_failure_count and last_force_failure_reason_desc in sys.query_store_plan record it.
• Fix the query for both kinds of caller. OPTION (RECOMPILE) on the statement, OPTIMIZE FOR a value you choose, or separate code paths for the big account. Each is measured in the parameter sniffing post and in the fixes table on the SSMS vs application page.
• Then unforce it with sp_query_store_unforce_plan, so the next schema change can produce a better plan.

MS Docs: Automatic tuning, sys.dm_db_tuning_recommendations, sp_query_store_force_plan and Query Store usage scenarios.

If Query Store Is Off

Then there is no yesterday to compare with, and that is the real finding. The plan cache only holds the current plan: sys.dm_exec_query_stats has a creation_time for when it was compiled and a plan_generation_num that rises with each recompile, which tells you the plan is new but not what it replaced. Documented, not reproduced here. Get Top CPU Queries ranks what the cache holds now, so you can at least name the query.

Then turn Query Store on before the next one. How to Check if Query Store Is Enabled takes ten seconds, and Get Query Store Status checks every database on the instance, including the ones that switched themselves to read-only when they filled up. On SQL Server 2022 and later it is on by default for new databases.

Frequently Asked Questions