如何在SQL Server中监控GROUP BY查询的内存授予不足-SQL-PHP中文网

应重点关注granted_memory_kb是否被拒绝及total_spills是否为0：granted_memory_kb为实际分配内存，used_memory_kb为运行中使用量，max_used_memory_kb达峰值时若近granted_memory_kb则易溢出，结合query_plan_hash与sql_handle聚合同类group by查询。

如何在sql server中监控group by查询的内存授予不足

查 `sys.dm_exec_query_stats` 中的内存等待和授予信息

GROUP BY 查询常触发哈希聚合或排序操作，这类操作需要一次性申请足够内存（REQUEST_MAX_MEMORY_GRANT_KB），若申请失败或被限制，会退化为磁盘工作表（spill），导致性能骤降。直接看执行统计是最准的起点。

关键指标不是“用了多少内存”，而是“是否被拒绝授予”或“是否发生溢出”。重点关注以下字段：

granted_memory_kb：实际分配到的内存（可能远低于请求值）
used_memory_kb：运行中真正使用的量
max_used_memory_kb：峰值使用量（若接近 granted_memory_kb，说明快撑满）
query_plan_hash + sql_handle：用于聚合同类 GROUP BY 查询

运行示例查询定位高风险语句：

SELECT TOP 20
  qs.sql_handle,
  qs.plan_handle,
  qs.granted_memory_kb,
  qs.used_memory_kb,
  qs.max_used_memory_kb,
  qs.execution_count,
  qs.total_spills,
  qs.last_spill_size,
  t.text AS query_text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) t
WHERE t.text LIKE '%GROUP BY%' 
  AND (qs.total_spills > 0 OR qs.granted_memory_kb <h3>识别 <code>PAGEIOLATCH_SH</code> 和 <code>RESOURCE_SEMAPHORE</code> 等等待类型</h3><p>内存不足时，SQL Server 不会直接报错，而是通过等待体现出来。GROUP BY 查询若反复 spill 到 tempdb，会引发两类典型等待：</p>

RESOURCE_SEMAPHORE：表示查询正在排队等待内存授予——说明资源调控器或整体内存配置已成瓶颈
PAGEIOLATCH_SH（尤其在 tempdb 数据文件上）：说明正在从磁盘读取 spill 出去的工作表页，是溢出的直接证据

检查当前会话级等待：

SELECT session_id, wait_type, wait_time_ms, blocking_session_id
FROM sys.dm_exec_requests
WHERE wait_type IN ('RESOURCE_SEMAPHORE', 'PAGEIOLATCH_SH')
  AND sql_handle IS NOT NULL;

注意：PAGEIOLATCH_SH 若集中在 tempdb 的 data file（可通过 sys.dm_io_virtual_file_stats 验证），基本可断定是 GROUP BY spill 导致的 I/O 拖累。

验证 `REQUEST_MAX_MEMORY_GRANT_PERCENT` 是否被过度限制

即使服务器内存充足，GROUP BY 查询也可能因工作负荷组设置被人为压低内存上限。最常见的是修改了 [default] 工作负荷组的 REQUEST_MAX_MEMORY_GRANT_PERCENT，比如从默认 25% 降到 5% 或 10%。

检查当前生效值：

SELECT 
  wg.name AS workload_group_name,
  wg.request_max_memory_grant_percent,
  rp.max_memory_kb,
  rp.max_memory_kb * wg.request_max_memory_grant_percent / 100 AS max_grant_kb
FROM sys.resource_governor_workload_groups wg
JOIN sys.dm_resource_governor_resource_pools rp ON wg.pool_id = rp.pool_id;

如果返回的 max_grant_kb 小于 100 MB（即约 102400 KB），多数 GROUP BY 查询都可能频繁 spill，尤其涉及百万级以上行数时。

临时放宽可测试影响：

ALTER WORKLOAD GROUP [default] WITH (REQUEST_MAX_MEMORY_GRANT_PERCENT = 25);
ALTER RESOURCE GOVERNOR RECONFIGURE;

但要注意：该操作会影响所有未分类到其他组的查询，需结合业务低峰期操作。

用 `DBCC MEMORYSTATUS` 快速确认内存压力源头

当怀疑是全局内存紧张而非单个查询问题时，DBCC MEMORYSTATUS 是最快捷的诊断快照。它不保证长期兼容，但对即时排查极有效。

重点看两块输出：

Memory Manager 节下的 Target Committed vs Current Committed：若后者长期显著低于前者，说明 SQL Server 没拿到预期内存（可能是 max server memory 设太低，或 OS 层有其他进程挤压）
Buffer Pool 节下的 Database Pages 和 Stolen Pages：若 Stolen Pages 持续高于 Database Pages 的 15%，说明计划缓存、查询工作区等“偷走”太多缓冲池空间，间接压缩 GROUP BY 可用内存

执行命令后，不要逐行读完全部输出，直接搜索关键词 Target Committed 和 Stolen Pages 即可快速定位。

真正容易被忽略的是：GROUP BY 查询的内存问题往往不是孤立发生的——它和 tempdb 压力、计划缓存膨胀、甚至 CLR 或链接服务器组件争抢内存密切相关。单独调大某个参数可能治标不治本；必须把 sys.dm_exec_query_stats、等待类型、资源调控器配置、DBCC MEMORYSTATUS 四者交叉比对，才能锁定根因。