BUG (P1): MAX_GRANT_PERCENT hint placed on metadata SELECT, not on UPDATE STATISTICS execution

[nanoDBA]

---

Summary

The OPTION (MAX_GRANT_PERCENT = @MaxGrantPercent) query hint added in v2.3 was applied to the wrong query. It is placed on the metadata discovery SELECT (~line 4449, a ranking sort over sys.stats DMVs) rather than on the UPDATE STATISTICS dynamic SQL builder (lines 5970–6420). The metadata SELECT has a near-zero memory grant. The UPDATE STATISTICS paths — the actual large memory consumers — have no hint at all.

The v2.3 goal of capping UPDATE STATISTICS memory grants is 100% unimplemented as shipped.

---

Impact

• Large FULLSCAN updates can still consume unbounded memory. On a system with max server memory not aggressively tuned, a FULLSCAN on a wide, high-row-count table can request multi-GB memory grants.
• Memory Grant Feedback is disabled for the wrong query. SQL Server 2017+ Memory Grant Feedback tracks and adjusts grants per query. By placing the hint on a near-zero-grant SELECT, MGF is disabled there (harmless) while the high-grant UPDATE STATISTICS queries remain fully exposed to MGF's feedback loop — the opposite of the intended behavior.
• Users who set @MaxGrantPercent have a false sense of security. There is currently no parameter named @MaxGrantPercent exposed to callers; the internal hint uses a hardcoded value of 25. But the intent is clear from the code comment, and the protection is absent.

---

Root Cause

-- CURRENT (v2.3) — WRONG LOCATION (~line 4449)
SELECT ...
FROM sys.stats s
...
ORDER BY ranking_sort
OPTION (MAX_GRANT_PERCENT = 25);   -- ← applied here (near-zero grant; inert)

-- UPDATE STATISTICS execution builder (~lines 5970–6420)
SET @current_command = N'UPDATE STATISTICS ' + @safe_table + N'.' + @safe_stat
    + @with_clause;                -- ← NO hint here; this is where it must go
EXECUTE sp_executesql @current_command;

---

Fix

1. Move the hint to the @current_command builder

Append OPTION (MAX_GRANT_PERCENT = @MaxGrantPercent) to the dynamic SQL string before the terminal semicolon, inside the existing loop at lines 5970–6420:

-- PROPOSED FIX
IF @supports_maxdop_stats = 1 AND @MaxGrantPercent IS NOT NULL
BEGIN
    SET @current_command = @current_command
        + N' OPTION (MAX_GRANT_PERCENT = '
        + CAST(@MaxGrantPercent AS nvarchar(3))
        + N')';
END

Gate on @supports_maxdop_stats = 1 (the existing SQL 2016 SP2+ version gate used for the MAXDOP hint). MAX_GRANT_PERCENT requires the same minimum version.

2. Expose as a caller parameter

@MaxGrantPercent INT = 10    -- default 10%; range 1–100

Add range validation at startup:

IF @MaxGrantPercent < 1 OR @MaxGrantPercent > 100
    RAISERROR('@MaxGrantPercent must be between 1 and 100.', 16, 1);

3. Remove the inert hint from the metadata SELECT

Remove the OPTION (MAX_GRANT_PERCENT = 25) from line ~4449. It provides no benefit and disables Memory Grant Feedback on a query where it is not needed.

---

Version Gate

MAX_GRANT_PERCENT is supported on SQL Server 2016 SP2+ and SQL Server 2017+. Use the same @supports_maxdop_stats version check already present in the procedure. No change to gating logic required.

---

Testing Requirements

• Validate hint reaches the UPDATE STATISTICS dynamic SQL string (unit test: capture @current_command with @Execute = 'N')
• Validate hint is absent when @MaxGrantPercent IS NULL or version gate fails
• Validate range check fires on @MaxGrantPercent = 0 and @MaxGrantPercent = 101
• Multi-container matrix: must test on SQL 2016 SP2 (gate boundary) and SQL 2017

---

References

• SQL Server docs: OPTION (MAX_GRANT_PERCENT)
• v2.3 commit: 0f7a293 (line ~4449)
• Related: Multi-version test matrix issue (blocker for this fix)