Catch Plan Regressions Automatic Tuning Leaves Uncorrected

calendar Oct 28, 2026 / · 8 min read · sys.server_event_sessions sys.dm_db_tuning_recommendations extended events automatic tuning automatic plan correction query store plan regression sql server 2017 sql server performance  ·
Share on: linkedin copy

When the gap between a regressed plan and the last known good plan is not high enough, automatic plan correction in SQL Server leaves the regressed plan in place. The recommendation it records is lost at the next restart. The script below creates an Extended Events session that writes each detected but uncorrected regression to an event_file target on disk.

Purpose and Overview

Automatic tuning was introduced in SQL Server 2017 (14.x), and its plan correction feature forces the last known good plan when a query's plan regresses. Plan correction depends on Query Store being enabled in the database, and you turn it on per database with ALTER DATABASE <yourDatabase> SET AUTOMATIC_TUNING ( FORCE_LAST_GOOD_PLAN = ON );. Once it is on, the engine forces any recommendation where the estimated CPU gain is higher than 10 seconds, or where the number of errors in the new plan is higher than in the recommended plan.

Regressions that meet neither criterion are detected and left alone. They show up in sys.dm_db_tuning_recommendations, but data in that DMV is not persisted after a restart of the Database Engine. An Extended Events session with a file target keeps its own record of every skipped regression on disk, outside the DMV.

The minimum version is SQL Server 2017, and the script checks the version first. The session is server-scoped, so its definition lives in sys.server_event_sessions on SQL Server and Azure SQL Managed Instance. Azure SQL Database supports only database-scoped event sessions, so the script does not apply there.

Code Breakdown

The script below is the published version with the author header comment removed and the logic unchanged.

 1IF SERVERPROPERTY('ProductMajorVersion') < 14
 2RAISERROR ('Your version of SQL Server is not supported! This extended event works only for version >= 2017 RC1', 20, 1) WITH LOG;
 3
 4IF EXISTS (SELECT 1 FROM sys.server_event_sessions WHERE name = 'APC_plans_that_are_not_corrected')
 5    DROP EVENT SESSION [APC_plans_that_are_not_corrected] ON SERVER;
 6GO
 7
 8CREATE EVENT SESSION [APC_plans_that_are_not_corrected] ON SERVER
 9ADD EVENT qds.automatic_tuning_plan_regression_detection_check_completed(
10WHERE ((([is_regression_detected]=(1))
11  AND ([is_regression_corrected]=(0)))
12  AND ([option_id]=(1))))
13ADD TARGET package0.event_file(SET filename=N'plans_that_are_not_corrected')
14WITH (STARTUP_STATE=ON);
15GO
16
17ALTER EVENT SESSION [APC_plans_that_are_not_corrected] ON SERVER STATE = start;
18GO

Version guard

The guard compares SERVERPROPERTY('ProductMajorVersion') with 14, the major version of SQL Server 2017 (14.x). A lower version hits RAISERROR at severity 20 with WITH LOG, and the message states the floor: version 2017 RC1 or later. The guard shares the first batch with the drop, ahead of the first GO.

Idempotent drop via sys.server_event_sessions

The IF EXISTS test reads sys.server_event_sessions, the catalog view that lists all the server-scoped event session definitions on the instance. The name column is unique and not nullable, so the test matches one row or none. DROP EVENT SESSION runs only when a session with that name is already defined. That makes the script safe to re-run, because a second execution replaces the session instead of colliding with the existing name.

Reading the view requires VIEW SERVER STATE on SQL Server 2019 (15.x) and earlier, and VIEW SERVER PERFORMANCE STATE on SQL Server 2022 (16.x) and later.

The session name appears four times: once as the string in the IF EXISTS query and three times as a bracketed identifier, in DROP, CREATE and ALTER. None of this is dynamic SQL, so there is no quoting risk. If you rename the session, change all four, or the existence test will stop matching the session it is supposed to drop.

The event and its predicate

qds.automatic_tuning_plan_regression_detection_check_completed fires whenever automatic tuning detects that a plan has potentially regressed and compares it with the last known good plan. Two of its fields carry the outcome. is_regression_detected means SQL Server has identified a possible plan change regression, and is_regression_corrected means the regression was corrected. The predicate keeps events where the first is 1 and the second is 0, which is the set APC examined and chose not to fix.

The third term filters on ([option_id]=(1)). In SQL Server 2017, option_id is always 0, because FORCE_LAST_GOOD_PLAN is the first and only automatic tuning option in that version.

On an instance that emits 0, a filter on 1 never matches, and the event file stays empty however many regressions APC skips. Changing the term to AND ([option_id]=(0)) is a one-character fix. Check which value your instance emits before you trust either filter (see Practical Tips).

Target and session options

package0.event_file gets filename=N'plans_that_are_not_corrected' with no path. The default location for Extended Events files is \MSSQL\Log within the installation folder of the instance, and that is where the session's files go. If that drive is short on space, put a full path in the SET filename clause.

The WITH clause sets only STARTUP_STATE, so every other option takes its default. Event retention is ALLOW_SINGLE_EVENT_LOSS, stored as S in event_retention_mode, and buffer memory is 4 MB. The alternative, NO_EVENT_LOSS, forces all the tasks that fire events to wait until space is available in an event buffer.

track_causality defaults to 0 (OFF). max_dispatch_latency is stored in milliseconds, and a value of 0 there means the dispatch latency is infinite.

STARTUP_STATE plus ALTER ... START

STARTUP_STATE=ON sets the startup_state bit, which determines whether or not the session is started automatically when the server starts. It has no effect until the server next starts. The final ALTER EVENT SESSION ... STATE = start is the statement that starts capture now. Without it, the session stays defined but idle until the next restart.

The startup_state default is 0, so if you remove the option, the session no longer comes back after a restart.

Key Benefits and Use Cases

  • One definition per instance — the predicate has no database filter, and the session is a single server-scoped definition in sys.server_event_sessions, not a per-database copy.
  • A measured gap to the threshold — cpu_gain is the estimated number of microseconds that would be saved if the recommended plan replaced the regressed one, so it shows how close each skipped regression came to being forced.
  • Pairs with a reverted-corrections session — the same pattern on qds.automatic_tuning_plan_regression_verification_check_completed with is_regression_corrected = 1 finds plans that were unforced because they regressed, which covers the other end of APC.
  • A shortlist for manual review — a query that keeps showing up with cpu_gain close to 10,000,000 sits below APC's line, so a human has to decide whether to force its plan.

Performance Considerations

  • Event frequency: the event fires on APC detection checks, when automatic tuning compares a possibly regressed plan with the last known good plan.
  • Memory partitioning: the session takes the default memory_partition_mode of NONE, which creates a single set of buffers within the instance instead of one set per CPU or per NUMA node.
  • Restarts: if the instance restarts before a plan forcing action is verified, that plan is automatically unforced, and the DMV loses its recommendations. The session itself comes back because of STARTUP_STATE=ON.
  • Verified forcings persist: a plan forcing that has been verified is persisted on SQL Server restarts, so after a restart those plans stay forced while the event file still holds the regressions APC skipped.

Practical Tips

  • Read the file with a wildcard — SELECT * FROM sys.fn_xe_file_target_read_file('plans_that_are_not_corrected*.xel', NULL, NULL, NULL); reads every file the session has written, because the function accepts * as a wildcard.
  • Filter on timestamp_utc — add WHERE CAST(timestamp_utc AS DATETIME2(7)) > DATEADD(DAY, -1, GETUTCDATE()) to see the last day; the timestamp_utc column applies to SQL Server 2017 (14.x) and later, which the version guard already guarantees.
  • Test option_id before trusting an empty file — do a trial run without the option_id term, check the option_id value in the captured events, then restore the filter as (0) or (1) to match.
  • Check the definition in sys.server_event_sessions — SELECT name, startup_state, event_retention_mode_desc, max_memory, max_dispatch_latency FROM sys.server_event_sessions WHERE name = 'APC_plans_that_are_not_corrected'; shows the stored options, and startup_state = 1 means the session starts with the server, not that it is running now. Leave out max_duration, which applies to SQL Server 2025 (17.x) and later.
  • Read cpu_gain against 10 seconds — below 10,000,000 microseconds (10 CPU seconds), the recommendation will probably not be applied. A regression where the new plan has more errors than the recommended plan is forced on that criterion whatever its CPU gain, so it is corrected and does not reach this session.

Conclusion

The script gives automatic plan correction an audit trail for the regressions it chooses not to fix, and it keeps that trail in event files that survive a restart. Keep it alongside your Query Store and sys.dm_db_tuning_recommendations queries, and check its option_id filter against each instance it runs on.

References

Posts in this series