如何诊断SQL存储过程导致的系统资源占用过高问题？-SQL-PHP中文网

直接查sys.dm_exec_requests和sys.dm_exec_sessions结合sys.dm_exec_sql_text，筛选status为running/suspended且cpu_time>10000或logical_reads>100000的会话，通过sql_handle关联实际SQL文本，重点识别command为EXECUTE或RPC的行以定位存储过程入口点。

如何诊断sql存储过程导致的系统资源占用过高问题？

查正在运行的高资源消耗存储过程

直接定位问题源头，别猜。SQL Server里存储过程一旦跑起来，就变成普通查询，但它的调用链和执行上下文藏在系统视图里。重点查 sys.dm_exec_requests 和 sys.dm_exec_sessions，结合 sys.dm_exec_sql_text 把实际执行的语句捞出来：

先过滤出状态为 'running' 或 'suspended' 且 cpu_time > 10000（毫秒）或 logical_reads > 100000 的会话
用 sql_handle 关联 sys.dm_exec_sql_text，确认文本确实是某个存储过程体内的语句，而不是动态拼接的裸SQL
特别注意 command 列显示为 EXECUTE 或 RPC 的行——这大概率是存储过程入口点

示例片段：

SELECT 
    r.session_id,
    s.login_name,
    t.text AS actual_sql,
    r.cpu_time,
    r.logical_reads,
    r.wait_type
FROM sys.dm_exec_requests r
JOIN sys.dm_exec_sessions s ON r.session_id = s.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.status IN ('running', 'suspended')
  AND (r.cpu_time > 10000 OR r.logical_reads > 100000)
  AND s.session_id > 50;

识别存储过程内部低效结构

很多存储过程看起来“封装得挺好”，实则藏着性能炸弹。真正吃资源的往往不是那句 EXEC MyProc，而是它里面反复调用的标量函数、嵌套循环、未参数化的临时表，或者隐式类型转换。

SELECT 语句里调用 UDF（用户定义标量函数）：每行都执行一次，无法并行，CPU飙升典型诱因
用 WHILE 循环逐行处理数据：比集合操作慢几个数量级，sys.dm_exec_query_stats 里能看到 execution_count 高但 total_elapsed_time 更高
临时表没加索引就 JOIN 或 ORDER BY：触发大量 Sort 和 Spool 操作，内存授予暴涨
参数未用 @param 而是拼字符串进 EXEC：导致每次重编译，plan_generation_num > 1

查法：对目标存储过程，用 sys.dm_exec_procedure_stats 看累计指标，再用 sys.dm_exec_query_plan 提取执行计划 XML，搜索关键词或。

区分是单次执行慢还是持续占用资源

存储过程引发的资源问题分两类：一种是单次执行太久（比如跑10分钟），另一种是执行完也不释放——比如开了事务没提交、用了 table variable 却被意外缓存、或 CLR 集成后 AppDomain 没卸载。

看 sys.dm_exec_sessions 的 open_transaction_count：非零值说明事务卡住，可能锁表+阻塞其他会话
查 sys.dm_os_memory_clerks 里 type = 'USERSTORE_TOKENPERM' 或 'CACHESTORE_OBJCP' 是否异常大——前者代表执行计划缓存膨胀，后者是对象缓存，常由大量不同参数的存储过程调用引起
如果错误日志出现 701（内存不足）或 8645（内存授予失败），大概率是某次执行申请了巨量内存但没及时归还

注意：DBCC FREEPROCCACHE 可清计划缓存，但会全局抖动；更稳妥的是只清特定存储过程：DBCC FREEPROCCACHE (plan_handle)，plan_handle 从 sys.dm_exec_cached_plans 查。

验证是否真由存储过程引起而非外部干扰

别一上来就翻存储过程代码。先排除常见干扰项，否则优化半天发现是别人在备份、DBA 在跑 DBCC CHECKDB、或者 OS 层面有其他进程抢内存。

用 sys.dm_os_sys_memory 确认 available_physical_memory_kb 是否真的低——如果还有几GB空闲，那 SQL Server 占内存就是正常行为，不是 bug
查 sys.dm_exec_background_jobs（SQL Server 2022+）或作业历史，看是否有维护任务正在跑
对比 sys.dm_os_wait_stats 中 LCK_M_XX、PAGEIOLATCH_XX、RESOURCE_SEMAPHORE 等等待类型占比：如果是锁等待主导，问题在事务设计；如果是 RESOURCE_SEMAPHORE，才是内存授予瓶颈