SQL Server 2025 Performance Regression: Query Store Workflow

DBA PARK may earn a commission from purchases through links in this article, at no extra cost to you.

If a workload becomes slower after moving to SQL Server 2025, do not start by changing server-wide settings. First determine whether the regression follows the new server environment, the new database-engine version, database compatibility level 170, or one specific execution plan.

The safest workflow is to keep variables separate, capture a baseline in Query Store, change compatibility level in a controlled window, and compare the fast and slow plans. Compatibility level can be reversed; an engine/file-format upgrade cannot be treated the same way.

Classify the Regression

Observation	Likely area	First evidence
Slow on the new server even at the old compatibility level	Infrastructure, configuration, drivers, security, or engine-level behavior	Waits, I/O latency, CPU, memory, network, instance settings
Fast at old compatibility; slow at level 170	Optimizer/cardinality/plan behavior	Query Store plan comparison
Only one application login or path is slow	Different SET options, permissions, RLS, parameter values, or connection behavior	Session settings and actual plan for each path
All queries pause together	Resource saturation, blocking, I/O, worker starvation, or external dependency	Wait stats and active requests during the event
Query is fast in SSMS but slow in the application	Parameter/SET differences, result consumption, network, or application processing	Application trace plus server duration and waits

Record the Starting State

SELECT
    SERVERPROPERTY('ProductVersion') AS product_version,
    SERVERPROPERTY('ProductUpdateLevel') AS update_level,
    SERVERPROPERTY('Edition') AS edition;

SELECT
    name,
    compatibility_level,
    is_query_store_on,
    snapshot_isolation_state_desc,
    is_read_committed_snapshot_on
FROM sys.databases
WHERE name = N'YourDatabase';

Also record the source and target:

CPU count, NUMA layout, memory, storage latency, and VM power policy.
SQL Server build, edition, trace flags, and database-scoped configurations.
MAXDOP, cost threshold, max server memory, Resource Governor, and tempdb layout.
Statistics state, parameter values, SET options, and application driver versions.
Business duration, CPU time, logical reads, rows, waits, and plan ID for the affected query.

A faster machine can still produce a slower result if the plan, storage latency, connection path, or application behavior changed.

Enable and Size Query Store

Enable Query Store before changing compatibility level. Choose retention and size for the workload rather than copying a universal value.

ALTER DATABASE [YourDatabase] SET QUERY_STORE = ON;

ALTER DATABASE [YourDatabase]
SET QUERY_STORE (
    OPERATION_MODE = READ_WRITE,
    QUERY_CAPTURE_MODE = AUTO,
    WAIT_STATS_CAPTURE_MODE = ON
);

Confirm Query Store remains read-write and has enough space during the comparison period. A read-only Query Store cannot capture the evidence you need.

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

Keep the Engine Move and Compatibility Change Separate

SQL Server 2025 supports compatibility level 170. Microsoft deliberately separates engine upgrade from compatibility-level enablement because query-processor changes can help most workloads while regressing an important individual query.

Recommended sequence:

Run the workload on SQL Server 2025 at the existing compatibility level.
Collect a representative Query Store baseline.
Change to level 170 during a controlled window.
Run the same business workload and parameter patterns.
Compare regressed queries, plans, runtime statistics, and waits.

ALTER DATABASE [YourDatabase]
SET COMPATIBILITY_LEVEL = 170;

Changing compatibility level clears plan cache entries for the database and can cause recompilation load. Schedule and monitor the operation accordingly.

Find Regressed Queries

Use the Query Store “Regressed Queries” report in SSMS or query the catalog views. Start with the report because it keeps plan and time-window context together. For a targeted review, capture query ID, plan ID, execution count, average duration, CPU, reads, and wait categories before and after the change.

Then open the fast and slow plan XML. Compare:

Estimated versus actual rows at the first large divergence.
Join order and join algorithm.
Seek/scan choice and residual predicates.
Memory grant, spills, and grant feedback.
Parallelism and degree of parallelism.
Parameter values and parameter-sensitive variants.
Warnings, implicit conversions, and missing indexes.

Use the execution-plan guide and join-algorithm guide for the comparison.

Check Active Waits Before Blaming the Optimizer

SELECT
    r.session_id,
    r.status,
    r.command,
    r.wait_type,
    r.wait_time,
    r.wait_resource,
    r.cpu_time,
    r.total_elapsed_time,
    r.logical_reads,
    r.reads,
    r.writes,
    r.dop,
    DB_NAME(r.database_id) AS database_name
FROM sys.dm_exec_requests AS r
WHERE r.session_id <> @@SPID
ORDER BY r.total_elapsed_time DESC;

A plan regression is only one possibility. Storage waits, blocking, log flush latency, thread-pool pressure, memory pressure, compilation, network waits, or application result processing can produce the same user complaint.

For a broader snapshot, use the essential DMV queries. Treat cumulative wait statistics as deltas across a defined interval; a lifetime total is not proof of the current incident.

Mitigation Options

Mitigation	Use when	Caution
Force a known good Query Store plan	One query regressed and the old plan remains valid	Monitor failures and changing data distributions
Query Store hint	A targeted hint is understood and tested	Document and periodically reassess it
Return to the previous compatibility level	Many critical queries regress and more analysis is required	This postpones new compatibility-level behavior; it is not root-cause resolution
Index/statistics/query correction	The plan exposes a durable design issue	Test write cost, storage, and other query plans
Apply a current CU or Microsoft-supported fix	The symptom matches a documented product issue	Follow change control and regression testing

For an emergency rollback of compatibility level:

ALTER DATABASE [YourDatabase]
SET COMPATIBILITY_LEVEL = 160; -- Example only: use the verified prior level.

Do not copy `160` blindly. Use the exact level validated for this database, and record the change, reason, and follow-up investigation.

Common Mistakes

Changing MAXDOP, cost threshold, memory, indexes, and compatibility level together.
Comparing one SSMS execution with a full application workload.
Clearing the entire plan cache repeatedly.
Forcing a plan without checking parameters and data distribution.
Assuming SQL authentication itself is slow without comparing session context and server timing.
Blaming SQL Server when the server completed quickly but the application consumed results slowly.

Summary

A SQL Server 2025 performance regression should be reduced to a controlled comparison. Keep the original compatibility level long enough to capture a baseline, enable level 170 separately, use Query Store to identify plan changes, and verify active waits and the application path. Mitigate narrowly, preserve rollback, and investigate the root cause before making server-wide changes.

Continue learning: To develop the plan-analysis skills used in this workflow, browse Udemy and search for SQL Server performance tuning courses with exercises on execution plans, statistics, and wait analysis. To practice writing more efficient SQL Server queries, explore the interactive exercises on DataCamp.