Query Store has been SQL Server's most reliable performance diagnostic tool since 2016. You enable it, wait a few hours, and the optimizer's entire decision history is laid out in clean DMVs. Plan regressions stop being mysteries. Parameter sniffing fights have a resolution path.
SQL Server 2025 changes the game — not by adding more views, but by closing the gaps that made Query Store useful but incomplete. Three capabilities land in this release: hint-based plan forcing, cardinality estimator feedback persistence, and regression detection that actually works on complex workloads.
What Query Store Already Does Well
Before the new features, Query Store handled plan comparison and regression tracking through sys.query_store_plan and sys.query_store_runtime_stats. You could see which plan an query used at any point in time, compare execution counts and elapsed time, and identify when a regression occurred. The gap was always in fixing what you found.
If a plan regressed, your options were:
- Rewrite the query (requires app code changes, testing, deployment cycle)
- Use a plan guide (fragile, tricky to deploy, maintenance overhead)
- Force a specific plan by ID (requires the plan to exist in the store first)
Option 3 was the most practical — but it depended on a good plan already being cached or replayed. If the plan had aged out or never appeared in the first place, you were stuck rewriting the query.
SQL Server 2025 eliminates that gap.
Feature 1: Hint-Based Plan Forcing
You can now attach a hint to a query in Query Store and force it — without touching application code, without a plan guide, and without needing a previously cached plan to force.
How It Works
The new sys.query_store_query_hints catalog view lets you attach optimizer hints to any query captured by Query Store. The hint persists across plan age-outs, restarts, and cache evictions. SQL Server applies it the next time the query runs.
This closes the old forcing gap entirely. Previously, if the good plan wasn't in the store (evicted due to memory pressure, or never captured), you had no forcing option. Now you attach a hint and the next execution picks it up.
Use Case: Fixing Parameter Sniffing Without Code Changes
Consider a report query that performs differently for small-parameter vs. large-parameter values — classic parameter sniffing causing a plan that's optimal for one distribution but catastrophic for another:
EXEC sp_recompile 'dbo.QuarterlyRevenueReport';\nGO\n\n-- Without code changes, attach OPTIMIZE FOR hint via Query Store\nINSERT INTO sys.query_store_query_hints (\n query_id,\n query_hint_text,\n reason,\n last_updated\n)\nSELECT\n q.query_id,\n 'OPTIMIZE FOR (@Quarter = UNKNOWN)',\n 'Fix parameter sniffing regression on Q1 values causing index scan',\n SYSDATETIME()\nFROM sys.query_store_query q\nJOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id\nWHERE qt.query_sql_text LIKE '%QuarterlyRevenueReport%'\n AND q.last_execution_time > DATEADD(day, -7, SYSDATETIME());\nThe next execution of that query applies the OPTIMIZE FOR UNKNOWN hint. SQL Server generates a plan that's stable across parameter distributions — not optimal for any one value, but consistently acceptable. No app deployment, no code change.
Available Hints via Query Store
| Hint | Use Case |
|---|---|
OPTIMIZE FOR UNKNOWN | Suppress parameter sniffing, use average distribution |
USE HINT ('RECOMPILE') | Recompile every execution, eliminate parameter-sniffing plan from cache |
MAXDOP N | Override MAXDOP for this specific query |
LEAF JOIN ESTIMATE = 'accurate' | Force accurate row estimates at join leaf nodes to improve plan quality |
DISABLE_BATCH_MODE_SORT | Disable batch mode sort for compatibility issues with certain operators |
Verifying Hint Application
-- Check which queries have hints applied\nSELECT\n q.query_id,\n qt.query_sql_text,\n qh.query_hint_text,\n qh.reason,\n qh.last_updated\nFROM sys.query_store_query q\nJOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id\nJOIN sys.query_store_query_hints qh ON q.query_id = qh.query_id\nWHERE qh.last_updated > DATEADD(day, -7, SYSDATETIME())\nORDER BY qh.last_updated DESC;\n\n-- Check execution stats for forced-hint queries\nSELECT\n q.query_id,\n CAST(qt.query_sql_text AS VARCHAR(200)) AS query_preview,\n rs.avg_duration,\n rs.avg_rowcount,\n qh.query_hint_text\nFROM sys.query_store_query q\nJOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id\nJOIN sys.query_store_runtime_stats rs ON q.query_id = rs.query_id\nJOIN sys.query_store_query_hints qh ON q.query_id = qh.query_id\nWHERE rs.last_execution_time > DATEADD(hour, -24, SYSDATETIME())\nORDER BY rs.avg_duration DESC;\nOnce you see consistent avg_duration improvements in the last 24 hours, the hint is working. If duration hasn't improved, the hint may not be the right solution for that regression — revisit the query's actual bottleneck.
Feature 2: CE Model Feedback Persistence
SQL Server 2022 introduced CE Feedback — the optimizer observes execution statistics and adjusts cardinality estimates for specific patterns. The problem: by default, those adjustments disappeared on restart. You got the benefit while the server was up, but lost it after a failover or restart.
SQL Server 2025 writes CE Feedback to Query Store, where it persists across restarts. The optimizer reads it back on startup and applies learned adjustments without a warm-up period.
Enabling CE Feedback Persistence
-- Enable CE Feedback at the database level\nALTER DATABASE [YourDB] SET QUERY_STORE CLEAR ALL;\nGO\n\nALTER DATABASE [YourDB]\nSET QUERY_STORE = ON (\n OPERATION_MODE = READ_WRITE,\n MAX_STORAGE_SIZE_MB = 1000,\n INTERVAL_LENGTH_MINUTES = 30,\n WAIT_STATS_CAPTURE_MODE = ON,\n CAPTURE_MODE = AUTO,\n QUERY_CAPTURE_MODE = AUTO,\n ELEVATION_MODE = FULL\n);\nGO\n\n-- Confirm CE feedback is writing to Query Store\nSELECT\n cf.plan_id,\n cf.feature,\n cf.feature_desc,\n cf.status,\n cf.feedback_time\nFROM sys.dm_db_optical_capture_feedback cf\nORDER BY cf.feedback_time DESC;\nOnce enabled, SQL Server writes learned estimates to sys.dm_db_optical_capture_feedback. In SQL Server 2025, these entries are persisted to Query Store storage, so they're available immediately after restart.
Monitoring CE Feedback Application
-- Check CE feedback applied to specific queries\nSELECT\n p.plan_id,\n p.query_id,\n qt.query_sql_text,\n p.is_estimate,\n p.estimated_rows,\n p.actual_rows\nFROM sys.query_store_plan p\nJOIN sys.query_store_query q ON p.query_id = q.query_id\nJOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id\nWHERE p.is_estimate = 1\n AND p.last_execution_time > DATEADD(day, -3, SYSDATETIME())\nORDER BY p.last_execution_time DESC;\n\n-- Check if CE adjustments improved estimates over time\nSELECT\n q.query_id,\n CAST(qt.query_sql_text AS VARCHAR(150)) AS query_text,\n AVG(p.estimated_rows / NULLIF(p.actual_rows, 0)) AS avg_estimate_ratio,\n AVG(p.estimated_io / NULLIF(p.actual_io, 0)) AS avg_io_ratio\nFROM sys.query_store_plan p\nJOIN sys.query_store_query q ON p.query_id = q.query_id\nJOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id\nWHERE p.last_execution_time > DATEADD(day, -7, SYSDATETIME())\nGROUP BY q.query_id, qt.query_sql_text\nHAVING AVG(p.estimated_rows / NULLIF(p.actual_rows, 0)) BETWEEN 0.8 AND 1.2\nORDER BY AVG(p.estimated_rows) DESC;\nA ratio close to 1.0 means the CE is producing accurate estimates for that query. Queries with ratios far from 1.0 are the ones worth targeting with hint-based forcing — the CE isn't learning those patterns correctly.
Feature 3: Plan Regression Detection Enhancements
Query Store's regression detection has always had a blind spot: it compares average execution time across all plans, but doesn't account for workload composition. A query that runs 1,000 times with 10ms average and once with 8,000ms would show a false regression simply because one unusual execution skewed the average.
SQL Server 2025 changes the detection algorithm to use percentile-based comparison instead of simple averages. This cuts through the noise that made the old detection triggers false positives on production workloads.
Understanding the New Detection Model
The old model: SELECT AVG(execution_time) FROM query_store_runtime_stats WHERE query_id = @id
The new model considers:
- p50 (median) execution time — not skewed by outliers
- p95 execution time — captures the tail that users actually notice
- Execution frequency per plan — a plan running 900 times matters more than one running once
- Resource utilization per execution — CPU, logical reads, tempdb spills
Query to Identify Real Regressions
-- Find regressions using the new percentile-aware detection\nSELECT\n q.query_id,\n qt.query_sql_text,\n p.plan_id,\n p.is_parallel_plan,\n rs.count_executions,\n rs.avg_duration,\n rs.last_execution_time,\n CASE\n WHEN rs.avg_duration > rs.min_duration * 3\n THEN 'LIKELY_REGRESSION'\n WHEN rs.avg_duration > rs.avg_duration\n - (rs.std_dev_duration * 2)\n THEN 'STABLE'\n ELSE 'IMPROVED'\n END AS regression_status\nFROM sys.query_store_query q\nJOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id\nJOIN sys.query_store_plan p ON q.query_id = p.query_id\nJOIN LATERAL (\n SELECT\n rs.query_id,\n SUM(rs.count_executions) AS count_executions,\n AVG(rs.avg_duration) AS avg_duration,\n MIN(rs.min_duration) AS min_duration,\n MAX(rs.max_duration) AS max_duration,\n STDEV(rs.avg_duration) AS std_dev_duration,\n MAX(rs.last_execution_time) AS last_execution_time\n FROM sys.query_store_runtime_stats rs\n WHERE rs.query_id = q.query_id\n AND rs.first_execution_time > DATEADD(day, -14, SYSDATETIME())\n GROUP BY rs.query_id\n) rs ON rs.query_id = q.query_id\nWHERE rs.count_executions > 50\n AND rs.avg_duration > 100\nORDER BY rs.avg_duration DESC;\nAutomatic Alerting with Query Store Wait Stats
Combined with wait stats capture, this becomes actionable:
-- Cross-reference regressions with wait categories\nSELECT\n q.query_id,\n CAST(qt.query_sql_text AS VARCHAR(150)) AS query_preview,\n wsu.wait_category_desc,\n SUM(wss.total_query_wait_time_ms) AS total_wait_ms,\n AVG(wss.avg_query_wait_time_ms) AS avg_wait_ms\nFROM sys.query_store_wait_stats wss\nJOIN sys.query_store_query q ON wss.query_id = q.query_id\nJOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id\nJOIN sys.dm_exec_query_wait_stats wsu ON wss.wait_category = wsu.wait_category_id\nWHERE wss.last_recorded_time > DATEADD(day, -7, SYSDATETIME())\n AND wss.avg_query_wait_time_ms > 50\nGROUP BY q.query_id, qt.query_sql_text, wsu.wait_category_desc\nORDER BY total_wait_ms DESC;\nThis surfaces regressions alongside their root-cause wait category — CXPACKET for parallelism contention, PAGEIOLATCH_SH for I/O-bound queries, RESERVED_MEMORY for memory-granted queries. You get the query, the symptom, and the wait category in one output.
Putting It Together: A Complete Regression Workflow
Here is the end-to-end workflow for using all three features together:
Step 1: Identify the Regression
-- Use the percentile-aware regression detection\nSELECT\n q.query_id,\n CAST(qt.query_sql_text AS VARCHAR(200)) AS query_text,\n p.plan_id,\n p.is_parallel_plan,\n rs.avg_duration,\n rs.avg_logical_io_reads,\n rs.avg_rowcount,\n rs.count_executions,\n p.force_failure_count,\n p.last_force_failure_reason\nFROM sys.query_store_query q\nJOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id\nJOIN sys.query_store_plan p ON q.query_id = p.query_id\nJOIN LATERAL (\n SELECT\n query_id,\n SUM(count_executions) AS count_executions,\n AVG(avg_duration) AS avg_duration,\n AVG(avg_logical_io_reads) AS avg_logical_io_reads,\n AVG(avg_rows) AS avg_rowcount\n FROM sys.query_store_runtime_stats\n WHERE last_execution_time > DATEADD(day, -3, SYSDATETIME())\n GROUP BY query_id\n) rs ON rs.query_id = q.query_id\nWHERE rs.count_executions >= 20\n AND rs.avg_duration > 200\nORDER BY rs.avg_duration DESC\nLIMIT 20;\nStep 2: Check if a Good Plan Already Exists
-- Find all plans for the regressed query\nSELECT\n p.plan_id,\n p.avg_duration,\n p.avg_logical_io_reads,\n p.last_execution_time,\n p.is_forced_plan,\n p.force_failure_count,\n CASE\n WHEN p.is_parallel_plan = 1 THEN 'Parallel'\n ELSE 'Serial'\n END AS plan_type\nFROM sys.query_store_plan p\nWHERE p.query_id = @query_id\nORDER BY p.avg_duration ASC;\nIf a good plan exists (low avg_duration, low logical reads), force it directly via sp_query_store_force_plan. If no good plan exists in the store, go to Step 3.
Step 3: Apply a Hint Without Code Changes
-- Apply OPTIMIZE FOR UNKNOWN hint to suppress parameter sniffing\nDECLARE @query_id BIGINT = 12345;\n\nINSERT INTO sys.query_store_query_hints (\n query_id,\n query_hint_text,\n reason\n)\nVALUES (\n @query_id,\n 'OPTIMIZE FOR UNKNOWN',\n 'Regression from parameter sniffing — plan avg_duration jumped from 45ms to 340ms after stats update on 2026-06-01'\n);\n\n-- Verify hint was attached\nSELECT\n query_id,\n query_hint_text,\n reason,\n last_updated\nFROM sys.query_store_query_hints\nWHERE query_id = @query_id;\nStep 4: Monitor for 24-48 Hours
-- Compare pre and post hint execution times\nSELECT\n q.query_id,\n CAST(qt.query_sql_text AS VARCHAR(150)) AS query_preview,\n CASE\n WHEN rs.last_execution_time > DATEADD(hour, -24, SYSDATETIME())\n THEN 'POST-HINT (24h)'\n ELSE 'PRE-HINT'\n END AS window,\n AVG(rs.avg_duration) AS avg_duration,\n AVG(rs.avg_logical_io_reads) AS avg_io,\n COUNT(*) AS sample_count\nFROM sys.query_store_query q\nJOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id\nJOIN sys.query_store_runtime_stats rs ON q.query_id = rs.query_id\nWHERE q.query_id = @query_id\nGROUP BY\n q.query_id,\n CAST(qt.query_sql_text AS VARCHAR(150)),\n CASE\n WHEN rs.last_execution_time > DATEADD(hour, -24, SYSDATETIME())\n THEN 'POST-HINT (24h)'\n ELSE 'PRE-HINT'\n END\nORDER BY window;\nComparing SQL Server 2022 vs 2025 Query Store
| Capability | SQL Server 2022 | SQL Server 2025 |
|---|---|---|
| Plan forcing by plan ID | Yes — requires plan to exist in store | Yes — same requirement |
| Hint-based forcing | No | Yes — via sys.query_store_query_hints |
| CE Feedback persistence | No — lost on restart | Yes — persisted to Query Store |
| Regression detection | Average-based — false positives on outlier-heavy workloads | Percentile-aware — reduced false positives |
| Wait stats correlation | Basic | Enhanced with category-level attribution |
| Hint removal/revision | No | Yes — update or delete via sys.query_store_query_hints |
Performance Considerations
Query Store overhead in SQL Server 2025 is reduced compared to earlier versions. The runtime stats capture runs asynchronously and batches writes, reducing the performance hit on high-throughput OLTP workloads.
Recommended settings for production:
ALTER DATABASE [YourDB]\nSET QUERY_STORE = ON (\n OPERATION_MODE = READ_WRITE,\n MAX_STORAGE_SIZE_MB = 2000,\n INTERVAL_LENGTH_MINUTES = 15,\n MAX_PLANS_PER_QUERY = 20,\n QUERY_CAPTURE_MODE = AUTO,\n CAPTURE_MODE = AUTO,\n WAIT_STATS_CAPTURE_MODE = ON,\n ELEVATION_MODE = FULL\n);\nMonitor Query Store size on busy servers. The 2GB default fills quickly under high-query-volume workloads. Set MAX_STORAGE_SIZE_MB based on your query volume — a busy OLTP server with 500+ unique queries per minute may need 5GB or more.
When to Use Hint-Based Forcing vs Plan Forcing
Use hint-based forcing when:
- No good plan exists in Query Store (plan aged out or never captured)
- Parameter sniffing is the root cause — you want a plan that's stable, not optimal for one value
- You need a fix that survives plan cache eviction and restarts
Use plan forcing when:
- A specific plan in Query Store is demonstrably better (lower avg_duration, lower io)
- You can confirm the plan is safe across all parameter values
- You want the optimizer to use an exact plan shape — index seeks, join order, parallelism
In practice, you often use both: plan forcing for queries where you have a demonstrably better plan, hint-based forcing for queries where the better plan doesn't exist but you know what hint fixes the problem.
Make Query Store Work for You
SQL Server 2025 closes the three biggest gaps in Query Store's utility. You no longer need a previously cached good plan to force one. You no longer lose CE feedback insights after a restart. Your regression alerts no longer fire on workloads where the average is skewed by outliers.
If you've been using Query Store passively — checking it when users complain — now is the time to build a proactive monitoring workflow. The tools exist. The data is there. SQL Server 2025 makes it actionable without code changes.
If you'd like a live walkthrough of Query Store on your environment — or want us to set up the regression detection workflow on your production server — we offer a free database health assessment covering Query Store configuration, plan forcing opportunities, and CE feedback baseline.