Skip to content

The plan-force bot reads a database missing from the latest automatic-tuning capture as 'off' and would force #4770

Closed

Description

@erikdarlingdata

The plan-force bot decides whether SQL Server's automatic plan correction (APC) owns a plan from the server's newest plan_correction capture. When the plan's database is missing from that capture, the state is not empty, APC reads as off, and no blocker applies. The verdict is WouldForce, with no detail. No bot test covers that case.

A database can be missing for ordinary reasons:

  • Captures land one database at a time, and a database that fails or runs over budget is skipped.
  • plan_correction runs apart from analysis, so a pass can read a partial capture.
  • The collector is off.

Each such row spends part of the bot's budget of 3 a day and starts a 24-hour cooldown.

Where (as of 30cbbcf)

  • The state read: Darling/PerformanceMonitor.Darling.Service/Mcp/DarlingForcePlanTargetStateReader.cs:121-129 and :168-169, and PerformanceMonitor.Analysis/ForcePlanTargetState.cs:146-153.
  • The verdict: PerformanceMonitor.Analysis/ForcePlanBotPolicy.cs:303-323 and Darling/PerformanceMonitor.Darling.Service/PlanForceBot.cs:187-217.
  • Skipped databases: PerformanceMonitor.Collectors/EnumeratedCollectorDriver.cs:875-909 and :925-932.
  • The detached run: Darling/PerformanceMonitor.Darling.Service/DarlingWorker.cs:9756-9758.
  • The daily budget: Darling/PerformanceMonitor.Darling.Service/PgPlanForceActionStore.cs:244-252.

Fix

Treat a database with no enablement row as unknown, and block it, but only where the collector applies. plan_correction never collects on SQL Server 2016 (PerformanceMonitor.Collectors/PlanCorrectionCollector.cs:461-466), while Query Store does (PerformanceMonitor.Collectors/QueryStoreCollector.cs:403). So if every unknown database is blocked, every 2016 target is blocked forever.

The reader takes each database's newest enablement row inside the 24-hour lookback, not only the newest capture. Ship this with #4769.

Test that must fail first

  • A plan whose database is missing from the newest capture, but present 3 hours earlier, uses that earlier state.
  • With no row in 24 hours on SQL Server 2017 or later, the plan is blocked as unknown.
  • On SQL Server 2016, it is not blocked for that reason.

Metadata

Metadata

Assignees

No one assigned

    Labels

    bugSomething isn't working

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions