"It was fast yesterday, and nothing changed" usually means the execution plan changed: after a statistics update, a restart, parameter sniffing or an upgrade.
1. Make sure Query Store is on
SELECT actual_state_desc, desired_state_desc, current_storage_size_mb, max_storage_size_mb
FROM sys.database_query_store_options;
ALTER DATABASE Sales SET QUERY_STORE = ON (OPERATION_MODE = READ_WRITE);
(On by default in Azure SQL Database and for new databases in SQL Server 2022.)
2. Find regressed queries
In SSMS: database → Query Store → Regressed Queries. Or with T-SQL, queries with more than one plan and a big difference in duration:
SELECT q.query_id, p.plan_id, qt.query_sql_text,
rs.avg_duration/1000 AS avg_ms, rs.count_executions, rs.last_execution_time
FROM sys.query_store_query q
JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id
JOIN sys.query_store_plan p ON q.query_id = p.query_id
JOIN sys.query_store_runtime_stats rs ON p.plan_id = rs.plan_id
WHERE q.query_id IN (SELECT query_id FROM sys.query_store_plan GROUP BY query_id HAVING COUNT(*) > 1)
ORDER BY q.query_id, rs.last_execution_time DESC;
3. Force the good plan (immediate fix)
EXEC sp_query_store_force_plan @query_id = 4711, @plan_id = 12;
Check the forced plan is used and the query is fast again:
SELECT query_id, plan_id, is_forced_plan, force_failure_count, last_force_failure_reason_desc
FROM sys.query_store_plan WHERE query_id = 4711;
Forcing can fail if the plan becomes invalid (for example, an index it used was dropped).
4. Find the root cause
Compare the two plans side by side (Compare Showplan in SSMS):
| Difference | Likely cause | Lasting fix |
|---|---|---|
| Estimated rows far from actual | Stale or sampled statistics | UPDATE STATISTICS ... WITH FULLSCAN on the key table |
| Fine for one parameter, terrible for another | Parameter sniffing | OPTION (OPTIMIZE FOR UNKNOWN) or RECOMPILE for that query; SQL 2022 Parameter Sensitive Plan optimization |
| Scan instead of seek | Missing index, or an implicit conversion (varchar vs nvarchar) | Add the index; fix the parameter data type |
| Changed after an upgrade | New cardinality estimator | Test with the old compatibility level / LEGACY_CARDINALITY_ESTIMATION per database |
5. Automatic tuning (optional)
ALTER DATABASE CURRENT SET AUTOMATIC_TUNING (FORCE_LAST_GOOD_PLAN = ON);
SQL Server then detects regressions and forces the last good plan by itself, and reverts if it doesn't help.
Tip
A forced plan is a bandage. Note it in the ticket and review forced plans monthly; un-force them once the root cause is fixed.