ENHANCEMENT (P2): @StopByTime can overshoot maintenance window — add budget guard and overshoot warning

[nanoDBA]

---

Summary

@StopByTime (added in v2.3) stops the procedure from starting new stat updates after the specified wall-clock time. However, it does not prevent an in-progress update from running past that time. On large tables with FULLSCAN, a single stat update can run for many minutes — causing significant overshoot of the maintenance window.

---

Example of the Problem

@StopByTime = '02:00:00'    -- maintenance window ends at 2 AM
Current time: 01:59:30
Procedure starts UPDATE STATISTICS on a 500 GB table (FULLSCAN)
Update runs for 8 minutes
Actual end time: 02:07:30   -- 7.5 minutes past the window

The procedure currently has no mechanism to predict whether starting a given stat update will overshoot the window.

---

Proposed Fix

1. Add @MaxSecondsPerStat INT = NULL parameter

When set, this parameter causes the procedure to skip any stat whose estimated duration exceeds the remaining time budget. Estimated duration is derived from CommandLog history using the same lookup mechanism as @LongRunningThresholdMinutes.

-- In the pre-execution check for each stat:
IF @MaxSecondsPerStat IS NOT NULL
   AND @StopByTime IS NOT NULL
BEGIN
    DECLARE @seconds_remaining INT = DATEDIFF(SECOND, GETDATE(), @StopByTime);
    DECLARE @estimated_seconds INT;

    SELECT @estimated_seconds = AVG(DurationSeconds)
    FROM dbo.CommandLog
    WHERE ObjectName = @stat_name
      AND TableName  = @table_name
      AND StartTime >= DATEADD(DAY, -30, GETDATE());

    IF @estimated_seconds IS NOT NULL
       AND @estimated_seconds > @seconds_remaining
    BEGIN
        -- Log as skipped (budget exceeded), continue to next stat
        RAISERROR(
            '[sp_StatUpdate] Skipping %s.%s — estimated %ds exceeds remaining budget (%ds).',
            0, 1, @table_name, @stat_name, @estimated_seconds, @seconds_remaining
        ) WITH NOWAIT;
        CONTINUE;
    END
END

If CommandLog has no history for a stat, the check is skipped (conservative: start the update).

2. Add post-run overshoot warning

After the main loop completes, if the actual end time exceeds @StopByTime by more than 30 seconds:

IF @StopByTime IS NOT NULL AND GETDATE() > DATEADD(SECOND, 30, @StopByTime)
    RAISERROR(
        '[sp_StatUpdate] WARNING: Run exceeded @StopByTime by %d seconds. '
        + 'Consider setting @MaxSecondsPerStat to guard against large-table overshoot.',
        0, 1, DATEDIFF(SECOND, @StopByTime, GETDATE())
    ) WITH NOWAIT;

---

Design Notes

• @MaxSecondsPerStat is only meaningful when @StopByTime is also set. Document accordingly.
• The budget guard is advisory: if CommandLog has no history, the stat runs. First-run installs are not penalized.
• The overshoot warning is purely informational — it does not change behavior, but gives operators visibility in logs.
• The 30-second grace period prevents noisy warnings from minor timing variance.

---

Testing Requirements

• Test with @MaxSecondsPerStat set and CommandLog history present — confirm high-cost stats are skipped near window boundary
• Test with @MaxSecondsPerStat set and no CommandLog history — confirm stats run (no skip on missing data)
• Test overshoot warning fires when actual end > @StopByTime + 30s
• Test overshoot warning does NOT fire when end is within 30s of @StopByTime
• Test @MaxSecondsPerStat without @StopByTime — confirm no behavior change (or emit a warning about redundant parameter)