如何防止SQL存储过程中的资源泄露？

未显式关闭游标会导致资源泄露，引发“Cursor is already open”或超时错误；事务未提交或回滚会持续持锁并阻塞其他会话；临时表未清理可能造成创建失败或tempdb空间占用；SET NOCOUNT ON可防止客户端连接状态机异常。

存储过程中没显式关闭游标会怎样？

游标（DECLARE CURSOR）在存储过程里一旦打开，就占用服务器内存和连接资源。如果忘记 CLOSE 和 DEALLOCATE，下次执行时可能因资源耗尽报错：Cursor is already open 或更隐蔽的 Timeout expired——尤其在高并发调用场景下，这种泄露会快速累积。

实操建议：

• 每个 OPEN 后必须配对 CLOSE + DEALLOCATE，不能只写一个
• 把游标操作包进 BEGIN TRY ... END TRY / BEGIN CATCH ... END CATCH 块，确保异常路径也能释放
• 优先用集合操作替代游标：比如用 UPDATE ... FROM 或窗口函数代替逐行处理

事务没回滚或提交会导致连接挂起吗？

是的。BEGIN TRANSACTION 后没跟 COMMIT 或 ROLLBACK，事务会一直持有锁、阻塞其他会话，同时该连接会被标记为“正在运行事务”，SQL Server 的 sys.dm_exec_sessions 中 open_transaction_count 会持续为 1，直到连接超时或被 kill。

常见触发点：

• 存储过程里有 IF 分支，只在某个分支写了 COMMIT，其他分支漏了
• 调用嵌套存储过程，内层出错但外层没捕获，导致外层事务悬空
• 错误处理只写 RETURN，没加 ROLLBACK

正确做法：所有事务路径（包括 CATCH 块）都必须明确结束事务；用 XACT_STATE() 判断当前事务状态再决定 ROLLBACK 还是 COMMIT。

临时表没清理干净会影响后续执行？

局部临时表（#temp）在会话结束时自动删除，但存储过程里重复创建同名临时表会失败：There is already an object named '#temp' in the database。这不是资源泄露的直接表现，而是逻辑错误暴露了资源管理缺失。

更严重的是全局临时表（##temp）：它存活到最后一个引用它的会话断开，若存储过程异常退出，##temp 可能残留数小时，占用 tempdb 空间并干扰其他用户。

建议：

• 避免用 ##temp，99% 场景局部临时表足够
• 创建前先 DROP TABLE IF EXISTS #temp（SQL Server 2016+）
• 不要依赖“自动清理”，显式 DROP TABLE #temp 放在逻辑末尾或 CATCH 块里

为什么 SET NOCOUNT ON 不只是性能优化？

SET NOCOUNT ON 关闭每条语句影响行数的消息返回，表面看是减少网络流量，但它实际影响客户端连接的状态机。某些旧版 ADO.NET 驱动或 ODBC 应用，在收到多余的消息（如 (1 row affected)）后，可能误判结果集边界，导致连接未正常释放，表现为连接池耗尽、后续请求卡住。

这不是理论风险——生产环境里真有人因为漏写这句，查了半天才发现连接池里堆积了上百个“已关闭但未释放”的连接。

所以：每个存储过程开头第一句就写 SET NOCOUNT ON，别省这行代码。