ENHANCEMENT (P2): QS forced plan detection should run unconditionally, not gated on @QueryStorePriority

[nanoDBA]

---

Summary

The Query Store forced plan pre-run inventory — which detects the "forced plan + RESAMPLE compounds estimate drift" failure mode — is currently gated behind @QueryStorePriority = 'Y'. This means the detection is invisible for the majority of executions where users haven't opted into QS-priority mode, even when Query Store is enabled and forced plans are present.

The failure mode (RESAMPLE on a stat underlying a forced-plan query can cause estimate divergence that the forced plan then amplifies) is rare but silent. Users have no warning unless they know to set @QueryStorePriority = 'Y'.

---

Current Behavior

-- Simplified current logic
IF @QueryStorePriority = 'Y'
BEGIN
    -- Check for forced plans that might be affected by stat updates
    SELECT ...
    FROM sys.query_store_plan qsp
    WHERE qsp.is_forced_plan = 1
    ...
END

If a database has Query Store enabled with active forced plans and the user runs sp_StatUpdate without @QueryStorePriority = 'Y', the forced plan inventory never runs. The procedure updates statistics without any awareness of which stats underlie forced plans.

---

Proposed Fix

Add a pre-run inventory that runs whenever Query Store is enabled on the target database (actual_state IN (1, 2)), regardless of @QueryStorePriority. Output the result as an informational RAISERROR WITH NOWAIT warning — not an error, not a stop condition.

-- PRE-RUN INVENTORY (runs unconditionally when QS is enabled)
IF EXISTS (
    SELECT 1
    FROM sys.database_query_store_options
    WHERE actual_state IN (1, 2)    -- READ_WRITE or READ_ONLY
)
BEGIN
    -- Count forced plans in the target database
    DECLARE @forced_plan_count INT;
    SELECT @forced_plan_count = COUNT(*)
    FROM sys.query_store_plan
    WHERE is_forced_plan = 1;

    IF @forced_plan_count > 0
        RAISERROR(
            '[sp_StatUpdate] INFO: %d forced plan(s) detected in Query Store. '
            + 'If RESAMPLE is used, verify post-run that plan regressions have not occurred. '
            + 'Use @QueryStorePriority = ''Y'' to prioritize stats underlying forced plans.',
            0, 1, @forced_plan_count
        ) WITH NOWAIT;
END

This approach:

• Surfaces the risk unconditionally, without changing execution behavior
• Keeps @QueryStorePriority as the opt-in for prioritized execution order
• Requires no new parameters
• Adds negligible overhead (single sys.query_store_plan scan, pre-run)

---

Why This Matters

The RESAMPLE-compounds-estimate-drift failure mode is not widely documented. A DBA running sp_StatUpdate with RESAMPLE against a database with QS forced plans may observe plan regressions hours later with no obvious cause. The pre-run warning makes the risk visible at the moment the user can act on it.

---

Testing Requirements

• Confirm warning fires when actual_state IN (1,2) and forced plans exist, regardless of @QueryStorePriority value
• Confirm warning does NOT fire when QS is disabled (actual_state NOT IN (1,2))
• Confirm warning does NOT fire when QS is enabled but no forced plans exist
• Confirm @QueryStorePriority = 'Y' still affects execution order (no regression)
• Test on SQL 2016+ (Query Store minimum version)