Log Forced Plans That Automatic Tuning Reverts with XEvents

When a query regresses, automatic plan correction forces the last known good plan and then keeps checking it. The script below creates an Extended Events session that writes each check ending in an unforced plan to an XEL file on disk, one event per reverted correction.

Purpose and Overview

A plan that automatic plan correction (APC) forces is not a final decision. After forcing a plan, automatic tuning keeps monitoring the forced plan and verifies that its performance does not degrade. APC forces a recommendation on its own when the estimated CPU gain is higher than 10 seconds, or the new plan has more errors than the recommended plan. If the forced plan is not better than the regressed plan, the Database Engine unforces it and compiles a new plan.

qds.automatic_tuning_plan_regression_verification_check_completed fires each time SQL Server finishes checking whether a forced plan should be kept, and the session keeps only the checks where APC found a regression and reverted the plan. Recommendations also appear in sys.dm_db_tuning_recommendations, but that DMV is not persisted after the database engine restarts. The XEL file stays on disk.

The minimum version is SQL Server 2017 (14.x). Automatic tuning applies to SQL Server 2017 and later, and the automatic tuning XEvents have been available since SQL Server 2017 RC1. The option_id filter below is the value for SQL Server 2017. The session is server-scoped. sys.server_event_sessions lists server-scoped session definitions, and Azure SQL Database supports only database-scoped event sessions, so the script does not run there as written. APC also needs Query Store enabled in each database it tunes.

Code Breakdown

The full script follows, with the author header comment trimmed and the option_id term set to (0).

 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_Reverted_plan_corrections')
 5    DROP EVENT SESSION [APC_Reverted_plan_corrections] ON SERVER;
 6GO
 7
 8CREATE EVENT SESSION [APC_Reverted_plan_corrections] ON SERVER
 9ADD EVENT qds.automatic_tuning_plan_regression_verification_check_completed(
10    WHERE ((([is_regression_detected]=(1))
11      AND ([is_regression_corrected]=(1)))
12      AND ([option_id]=(0))))
13ADD TARGET package0.event_file(SET filename=N'reverted_plan_corrections')
14WITH (STARTUP_STATE=ON);
15GO
16
17ALTER EVENT SESSION [APC_Reverted_plan_corrections] ON SERVER STATE = start;
18GO

Version guard

The guard compares SERVERPROPERTY('ProductMajorVersion') with 14, the major version of SQL Server 2017 (14.x). Below that, it raises a severity 20 error WITH LOG, and the message names SQL Server 2017 RC1 as the floor.

On SQL Server 2017 and later the IF is false and the RAISERROR never runs. The guard sits in the first batch only, together with the drop-if-exists check. The batches after the first GO contain no version test of their own.

Drop-if-exists against sys.server_event_sessions

The name column in sys.server_event_sessions is unique, so one match on name identifies the session. Dropping and recreating it makes the script safe to rerun. Rerunning is also how a changed predicate or target option gets applied.

To read the view you need VIEW SERVER STATE on SQL Server 2019 and earlier, and VIEW SERVER PERFORMANCE STATE on SQL Server 2022 and later. Once the script has run, the same view confirms what was stored:

1SELECT name,
2       startup_state,
3       event_retention_mode_desc
4FROM sys.server_event_sessions
5WHERE name = N'APC_Reverted_plan_corrections';

startup_state defaults to 0, so a 1 here shows that STARTUP_STATE=ON took effect. event_retention_mode_desc shows ALLOW_SINGLE_EVENT_LOSS, the default, because the script does not set it.

The event and predicate

The verification event carries two flags. is_regression_detected is 1 when SQL Server detects that the forced plan regressed. is_regression_corrected is 1 when SQL Server unforces the plan and lets the Query Optimizer create a new one. To decide, it compares performance across two consecutive intervals and unforces the plan if the difference is large enough. A value of 0 in is_regression_corrected means the difference was too small and the plan stays forced.

With both flags set to 1, the predicate drops every check where the forced plan held and keeps only the reverts.

The third term is ([option_id]=(0)). On SQL Server 2017, option_id is always 0, because FORCE_LAST_GOOD_PLAN is the first and only automatic tuning option in that release, so a filter on option_id = 1 would never record an event there.

Target and session options

The event_file target is given only a file name, reverted_plan_corrections. It has no path, no file size limit and no rollover count, so all of those are left to the defaults.

The WITH clause sets only STARTUP_STATE, so the other session options take their defaults. MAX_MEMORY for event buffering is 4 MB by default, and event loss is handled as ALLOW_SINGLE_EVENT_LOSS.

STARTUP_STATE = ON starts the session when the Database Engine is stopped and then restarted. It does not start the session now. The last batch, ALTER EVENT SESSION ... STATE = start, begins capture straight away instead of waiting for the next restart. Creating the session needs CREATE ANY EVENT SESSION (introduced in SQL Server 2022) or ALTER ANY EVENT SESSION.

Key Benefits and Use Cases

• Spotting queries whose forced plans do not hold — a query that turns up in the file again and again is one where APC keeps forcing a plan and then taking it back.
• Evidence for a per-database decision — automatic tuning is enabled per database, so the reverts you count give you something concrete to go on before you turn FORCE_LAST_GOOD_PLAN off in a database.
• One session for the whole instance — the session is server-scoped and has no database filter, so it collects reverts from every database where APC is enabled.
• Pairing with the not-corrected session — the companion APC - plans that are not corrected session uses qds.automatic_tuning_plan_regression_detection_check_completed with is_regression_corrected = 0. With both running, you cover the regressions APC chose not to fix as well as the fixes it later withdrew.

Performance Considerations

• Event volume: the session captures one event, filtered to completed verifications that ended in a revert, so the file grows only when a revert happens.
• Event loss: under ALLOW_SINGLE_EVENT_LOSS, a single event is dropped only when all event buffers are full, so the setting does not guarantee that every revert is written.
• Dispatch delay: events are held in memory for up to 30 seconds by default before they go to the target, so on a test instance wait that long before reading the file after a revert.
• Restart effects: if the instance restarts before a forcing action is verified, that plan is unforced automatically, and plan forcing that has been verified persists across restarts. The session itself comes back with the server through STARTUP_STATE=ON.
• Run time: on SQL Server 2025 and later, MAX_DURATION defaults to UNLIMITED, so a session that does not set it runs until it is stopped with ALTER EVENT SESSION ... STATE = STOP.

Practical Tips

• Confirm APC is on before blaming an empty file — in each database, once Query Store is enabled there, run ALTER DATABASE <yourDatabase> SET AUTOMATIC_TUNING ( FORCE_LAST_GOOD_PLAN = ON );.
• Keep a history table for the DMV — copy sys.dm_db_tuning_recommendations rows into a table on a schedule, and read sqlserver_start_time from sys.dm_os_sys_info to see when the DMV was last emptied.
• Give the target a path and limits — set the file location and size limits yourself, in the form SET filename = N'C:\xe\reverted_plan_corrections.xel', max_file_size = 256, max_rollover_files = 10, and rerun the script.
• Read cpu_gain in the companion session — cpu_gain is the estimated number of microseconds a recommended plan would save. Below 10,000,000 (10 CPU seconds), the recommendation will probably not be applied.

Conclusion

Each event in the file is a forced plan that automatic plan correction verified, found regressed and unforced, so the file is the place to look when a query's plan changed again after APC fixed it.

References

• APC_Reverted_plan_corrections.sql — the script (its option_id = 1 term changed to 0)
• Monitoring automatic tuning actions using XEvents — verification event fields, option_id always 0 on SQL Server 2017, the original (0) predicate, XEvents since 2017 RC1, the not-corrected session and cpu_gain
• Automatic tuning — 2017 and later scope, forcing triggers (10-second CPU gain or error count), unforce and revert behavior, restart effects, DMV persistence and sqlserver_start_time, per-database enablement, Query Store dependency
• sys.server_event_sessions (Transact-SQL) — server-scoped definitions, unique name, permissions, startup_state and retention defaults, ALLOW_SINGLE_EVENT_LOSS behavior, Azure SQL Database scope
• CREATE EVENT SESSION (Transact-SQL) — STARTUP_STATE behavior, MAX_MEMORY and MAX_DISPATCH_LATENCY defaults, MAX_DURATION on SQL Server 2025, event_file path and rollover syntax, create permissions