在SQL Server中如何排查由于嵌套查询引起的内存授予过高？-SQL-PHP中文网

最准最快路径是查执行计划中的GrantedMemoryKB和Table Spool节点，并结合sys.dm_exec_query_stats定位内存申请远超业务规模的语句；关键看Table Spool的Estimated Rows是否严重偏离实际（如预估189000行而真实仅1200行），因后续内存分配均基于该错误基数。

在sql server中如何排查由于嵌套查询引起的内存授予过高？

直接看执行计划里的 GrantedMemoryKB 和 Table Spool 节点，再结合 sys.dm_exec_query_stats 找出实际内存申请远超业务规模的语句——这是最准、最快、不靠猜的路径。

查执行计划里有没有 `Table Spool (Eager)` 和严重偏离的 `Estimated Rows`

SQL Server 遇到多层嵌套（尤其是带参数的 IN/EXISTS）时，常因基数误判生成 Table Spool (Eager) 节点，它会全量物化子查询结果进内存。关键不是“有没有 spool”，而是它的 Estimated Number of Rows 是否离谱：

比如 users 表真实只有 1200 个 status = 'active' 用户，但 spool 节点预估行数标着 189000，后续所有内存申请都基于这个错误数字
用 SET STATISTICS XML ON 跑语句，搜索 XML 中的，对比你心里知道的实际行数
别信 Actual Rows 后面那个数字——它只反映本次执行，而 GrantedMemoryKB 是按预估算的，错一次就全错

用 `sys.dm_exec_query_stats` 找内存授予异常高的语句

光看单条语句的执行计划不够，得从历史维度揪出反复吃内存的“惯犯”。下面这条查询能直接暴露 granted_memory_kb 异常偏高的语句：

SELECT 
  qs.sql_handle,
  qs.granted_memory_kb,
  qs.used_memory_kb,
  qs.execution_count,
  st.text AS sql_text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
WHERE qs.granted_memory_kb > 50000  -- 超 50MB 就值得盯
  AND st.text LIKE '%SELECT%FROM%WHERE%IN%(%SELECT%'  -- 快速筛嵌套结构
ORDER BY qs.granted_memory_kb DESC;

granted_memory_kb 持续 > 100MB 且 execution_count 高，基本就是嵌套+参数化导致计划复用错误
如果 used_memory_kb 远小于 granted_memory_kb（比如 8MB vs 512MB），说明优化器“高估”了，不是真需要那么多
注意排除报表类语句——它们大内存是合理的；重点抓 OLTP 场景下 WHERE user_id IN (...) 这类高频小查询

验证子查询是否被外层每行重复执行

嵌套查询爆内存，最狠的不是层数深，而是优化器选了 nested loop 策略，让内层子查询被调用 N 次。验证方法极简：

单独跑子查询：SELECT COUNT(*) FROM users WHERE status = 'active'，记下逻辑读和耗时（比如 12ms，logical reads = 42）
再跑完整语句：SELECT * FROM orders WHERE user_id IN (SELECT id FROM users WHERE status = 'active')，开 SET STATISTICS IO ON
如果完整语句的 users 表 logical reads 接近 42 × orders表行数（比如 orders 有 6 万行 → reads ≈ 250 万），那就是实锤重复执行
这时加索引没用，必须改写：把子查询提前物化成临时表，或改用 JOIN (SELECT id FROM users WHERE status = 'active') u

为什么 `OPTION (RECOMPILE)` 有时管用、有时更糟

它能强制优化器按当前参数重估基数，对参数敏感型嵌套查询（如 WHERE city = @city）有效，但有两个硬限制：

只对存储过程或即席语句中真正“值变化大”的参数有用；如果 @status 总是 'active'，加了反而增加编译开销
必须配合 SET STATISTICS XML ON 验证：重编译后执行计划里 GrantedMemoryKB 是否回落（比如从 1024000 降到 12288）
它不能修复子查询本身没走索引的问题——如果 users(status) 没索引，重编译只是让优化器更准地“算错”，仍会申请巨量内存

真正容易被忽略的一点：内存授予过高往往不是嵌套语法本身的问题，而是驱动表顺序错了——优化器选了大表做外循环，又没索引支撑内层查找。这时候改写比调参管用得多，但很多人卡在“先看执行计划”这一步，忘了去查 sys.dm_exec_query_stats 里真实的内存申请记录。