ENHANCEMENT: Pre-execution warning when forced plans + AUTOMATIC_PLAN_CORRECTION enabled

[nanoDBA]

Problem

No warning when sp_StatUpdate runs on a table that has:

1. Forced plans (via Query Store)
2. AUTOMATIC_PLAN_CORRECTION enabled on the database

This creates a silent conflict: fresh statistics can unmask cardinality estimate errors in the forced plan tree, but the auto-correction engine may simultaneously try to revert the plan. Result: stats and plan corrections fight each other.

Scenario

-- Table has a forced plan from Query Store
-- Database has AUTOMATIC_PLAN_CORRECTION = ON
EXEC sp_StatUpdate @Table = 'dbo.MyTable', @UpdateStatsWithFullScan = 1;
-- No warning. But stats update may trigger plan advisor conflict.

Solution

Add pre-execution warning:

-- Check if forced plans exist AND auto-correction is enabled
IF EXISTS (
    SELECT 1 FROM sys.database_automatic_tuning_options 
    WHERE name = 'AUTOMATIC_PLAN_CORRECTION' AND actual_state IN (1, 2) -- ON or PENDING
)
AND EXISTS (
    SELECT 1 FROM sys.query_store_plan qsp
    WHERE is_forced_plan = 1
)
BEGIN
    RAISERROR('WARNING: Table has forced plans and AUTOMATIC_PLAN_CORRECTION is enabled. Stats update may trigger plan correction conflicts.', 0, 1);
END;

One query check (minimal overhead), informational warning only.

Why This Matters

At Henry Schein (and similar enterprises), forced plans are critical for stability. Auto-correction helps with query evolution. When both are active, stats updates can cause unexpected plan changes. Users should be aware of this interaction.

Test Vector

• Create table with forced plan
• Enable AUTOMATIC_PLAN_CORRECTION on database
• Run sp_StatUpdate
• Verify warning fires
• (Optional) Monitor Query Store plan corrections 24h post-update

Confidence

High. Paul White (internals expert) + Brent Ozar (production DBA) both validate the concern. Lars's feedback ('heavily use forced plans') confirms priority for this environment.

Priority

P2 for environments using forced plans. P4 for environments that don't.