Diagnosing a Query Plan Regression Before You Force a Fix

A query plan regression on SQL Server doesn’t announce itself with an error string. It shows up as a jump in regressedPlanCpuTimeAverage inside the JSON details column of sys.dm_db_tuning_recommendations, measured against recommendedPlanCpuTimeAverage for the plan the optimizer would rather use. That single row is often the first hard evidence that a query plan regression, not application code, is behind a slow database API response.

Quick take

sys.dm_db_tuning_recommendations exposes queryId, regressedPlanId, and CPU-time deltas so you can confirm a plan regression before forcing anything.

is_executable_action and is_revertable_action tell you whether SQL Server can apply, and later undo, the fix automatically.

Cross-region latency is a separate failure mode from plan regression: routing happens above the SQL connection, so no DMV row will explain it.

AlloyDB and Datastore ship comparable query-insight tooling, but their retention windows and reporting delay differ enough to change how fast you catch a regression.

What the Tuning DMV Actually Records

The type column names the automatic tuning option that produced the row, for example FORCE_LAST_GOOD_PLAN, and the reason column states why that recommendation exists. valid_since and last_refresh timestamps track when the regression first appeared and when it was last confirmed, which matters if you’re trying to correlate a regression against a deployment or a statistics update.

The score field estimates the value of applying the recommendation on a 0-100 scale, where a larger number means a bigger expected improvement. That number is a ranking signal, not a guarantee, and the documentation ties it to the same JSON details payload rather than a separate confidence metric.

Inside details.planForceDetails, the regression is described with regressedPlanExecutionCount, regressedPlanAbortedCount, and both the average and standard deviation of CPU time consumed before the regression was detected, alongside the same figures for the plan SQL Server recommends forcing. That’s the actual mechanism: two plans, two CPU-time distributions, compared automatically.

Why Forcing the Recommended Plan Blind Fails

The tempting move is to read the score and force whatever plan the DMV recommends. But is_executable_action marks whether a row can even be run via Transact-SQL, since some rows are information-only or already reverted, and is_revertable_action marks whether Database Engine can automatically monitor and revert it later.

Most executable actions are revertable, but not all, so a recommendation with a high score and is_executable_action = 0 can’t be applied through the mechanism the DMV describes at all. Skipping that check is the fastest way to burn an afternoon on a fix that was never going to execute.

execute_action_initiated_by and revert_action_initiated_by distinguish whether a plan was forced or reverted by a person or by the automatic tuning system, which is the only way to tell, after the fact, whether a regression was fixed by policy or by a DBA’s manual override.

Query Plan Regression Is Not the Only Latency Source

A Microsoft Q&A thread about an Azure SQL Managed Instance deployment spread across the US, India, and Europe surfaces a different trap: the instance has no built-in geo-detection, so nothing in the database itself decides which regional endpoint a write should hit. Routing has to happen in application code or in a traffic-management layer before the SQL connection is even established.

That means a query can be running efficiently on its own regional plan while the API still looks slow, because the delay is network and routing distance, not execution time. Microsoft’s own response to that thread lists resource saturation and inefficient execution, meaning missing indexes, outdated statistics, or plan regressions, as the standard causes to rule out first, using DMVs and Query Store, before touching routing logic.

The practical order matters here: check CPU, I/O, and wait stats through the DMVs and Query Store first, and only treat cross-region connection routing as the cause once execution-side metrics come back clean. Reversing that order means rewriting queries that were never the problem.

Cross-Platform Query Insight Tools Don’t Behave the Same Way

AlloyDB for PostgreSQL’s advanced query insights features capture query plans for every query and sample up to 20 query plans per minute, with metrics expected to be available within 30 seconds of query completion. Historical data covers up to a 30-day period, giving a long-term view without a separate export pipeline.

Datastore’s Query insights dashboard works on a coarser clock. It reports normalized query text, execution count, average execution duration, and average entities or index entries scanned, at 10-minute granularity for intervals up to 4 days and 1-hour granularity for intervals up to 30 days. The dashboard itself is delayed by one to two hours, which the documentation states as a limitation rather than an edge case.

That delay is the operational difference that matters when a regression is active right now: a near-real-time tool can confirm a plan flip inside the incident window, while a tool with a one to two hour delay confirms it only after the fact.

Neither tool substitutes for the other’s data model. AlloyDB’s insights are built around execution plans and wait events tied to an application stack, while Datastore’s insights are built around read-operation cost and index-entry scans for a document-style query API. Choosing between them isn’t a preference call, it depends on whether the regression you’re chasing is plan-shape driven or scan-cost driven.

Watch out

A slow query isn’t only a performance problem once it’s externally triggerable: a cheap request can force a backend to scan large row sets, hold locks, and occupy a connection and a worker thread, turning a plan regression into a cost amplifier an attacker can repeat on demand.

That framing reclassifies an unpatched plan regression from a tuning backlog item into something closer to a standing denial-of-service surface, because the same query that regressed under normal load regresses identically, and repeatably, under adversarial load.

Before You Force Anything, Verify This

• Confirm is_executable_action = 1 and check is_revertable_action on the specific row before running any forced-plan Transact-SQL.
• Compare regressedPlanCpuTimeAverage against recommendedPlanCpuTimeAverage directly rather than trusting the score alone.
• Rule out CPU, I/O, and wait-stat saturation through DMVs and Query Store before investigating cross-region routing.
• If using AlloyDB or Datastore insights, note the tool’s reporting delay against your incident window so you don’t mistake a lagging dashboard for a resolved regression.

The documentation doesn’t say what happens if you force a plan without checking is_revertable_action first, and it doesn’t quantify how often FORCE_LAST_GOOD_PLAN recommendations turn out to be non-executable in practice. Until you’ve read those two fields for the specific row in front of you, the regression isn’t confirmed, it’s still a guess with a CPU-time number attached.