根本原因是查询优化器基于统计信息和运算符预估内存需求，而非存储过程本身；max server memory 不限制单查询授予，需通过更新统计信息、避免函数谓词、使用OPTION提示或Resource Governor来调控。

SQL Server 2019 存储过程中单个查询的内存授予（memory grant）过大，根本原因不是存储过程本身能“申请多少”，而是其内部查询（尤其是排序、哈希联接、位图等操作）在编译时预估所需内存过高——限制它必须从查询级入手，而非对存储过程加全局开关。

为什么 sp_configure 的 max server memory 不影响单查询内存授予

设置 max server memory (MB) 只控制 SQL Server 整体缓冲池和内部结构的内存上限，不约束单个查询运行时向缓冲池申请的执行内存（grant）。一个复杂排序可能预估需要 2 GB 内存，哪怕服务器只配了 4 GB 总内存、max server memory 设为 3072 MB，只要该查询被判定为“高优先级且资源可用”，仍会拿到接近预估的 grant。这会导致其他并发查询饥饿、query wait 超时，甚至触发 tempdb 溢出。

• 内存授予由查询优化器基于统计信息、行数预估、运算符类型（如 Sort、Hash Match）动态计算，与存储过程是否 WITH RECOMPILE 或是否本机编译无关
• min memory per query 是下限保障，不是上限；设高了反而加剧争用，设低了可能导致频繁溢出到 tempdb
• 没有类似 PostgreSQL 的 work_mem 或 Oracle 的 PGA_AGGREGATE_LIMIT 那样的 per-query 硬性上限配置项

真正有效的限制手段：从查询计划和参数入手

你无法“禁止”SQL Server 给某个存储过程分配大内存，但可以干预它“为什么认为要分配那么多”。关键在让优化器预估更保守、更贴近实际。

• 确保参与排序/联接的列有最新统计信息：UPDATE STATISTICS dbo.orders WITH FULLSCAN, NORECOMPUTE；过时的统计常导致行数预估偏差 10–100 倍，grant 直接翻倍
• 避免在 WHERE 或 JOIN 条件中对字段使用函数或表达式（如 WHERE YEAR(order_date) = 2024），这会让优化器放弃基数估算，转而按全表比例粗略预估
• 对已知小结果集的中间步骤，显式用 OPTION (RECOMPILE, OPTIMIZE FOR (@param = 'small_value')) 引导优化器按典型值生成计划，而非最坏情况
• 若查询含 TOP N 且 N 很小（如 TOP 100），加上 OPTION (FAST 100) 可显著降低排序内存需求

当必须硬性截断时：Resource Governor 是唯一内置方案

SQL Server 2019 仍不支持 per-query 内存 cap，但可通过 Resource Governor 对整个工作负荷组（workload group）设置 REQUEST_MAX_MEMORY_GRANT_PERCENT，这是最接近“限制存储过程内存”的可行路径。

• 需先创建资源池：CREATE RESOURCE POOL pool_lowmem WITH (MAX_MEMORY_PERCENT = 40);
• 再建对应工作负荷组：CREATE WORKLOAD GROUP wg_lowmem USING pool_lowmem;
• 最后绑定登录或应用程序名：CREATE CLASSIFIER FUNCTION fn_wg_classifier() RETURNS sysname AS BEGIN RETURN CASE WHEN APP_NAME() = 'MyAppProc' THEN 'wg_lowmem' ELSE 'default' END; END;
• 启用后，该组内所有查询的 grant 不得超过池内存的指定百分比（例如 25%），超出部分会被拒绝或降级执行
• 注意：Resource Governor 在 Standard Edition 中仅限 CPU 控制，内存限制需 Enterprise 或 Developer Edition

容易被忽略的陷阱：内存优化表变量反而放大问题

如果你在存储过程中混用了内存优化表变量（MEMORY_OPTIMIZED = ON）和传统磁盘表，或者在非本机编译过程中引用它们，不仅得不到性能提升，还可能因类型检查、序列化/反序列化开销，导致查询重编译失败或 grant 计算异常升高。

• 内存优化表变量只在 WITH NATIVE_COMPILATION 过程中生效；普通 T-SQL 里声明它只是多一层解析负担
• DURABILITY = SCHEMA_AND_DATA 表写日志、占持久内存，和“限制内存”目标完全相悖；临时中间结果必须用 SCHEMA_ONLY
• 哈希索引的 bucket_count 若远大于实际键数量（比如设 1000000 但只存 1000 行），会预先占用大量内存页，直接抬高初始 grant

真正卡住内存授予的，从来不是配置项开关，而是统计质量、谓词写法、以及是否误把内存优化当万能药——这些地方动一动，比调 max server memory 管用十倍。