在数据库系统运行中，存储过程因其预编译、可复用和集中管理的特点被广泛使用。但当数据量增长或调用模式变化后，许多原本顺畅的存储过程会出现延迟陡增、锁等待增多等现象。要真正提升其性能，需要从执行计划、写法习惯、对象选型以及统计信息等多个维度系统分析。
一、理解执行计划与参数嗅探
存储过程在首次执行时会根据传入的参数生成执行计划并缓存，后续调用通常复用该计划。这种行为称为参数嗅探。当首次传入的参数对应数据分布极不均匀时，优化器生成的计划可能只适合那一次查询，后续不同参数的调用就会效率极差。
可以通过在测试环境执行SET STATISTICS IO ON与SET STATISTICS TIME ON，观察逻辑读次数与CPU时间。若发现某次调用逻辑读异常高，应查看实际执行计划中的扫描节点。以下示例展示如何重编译以规避不良计划：
-- 使用 WITH RECOMPILE 让每次执行都重新生成计划
CREATE PROCEDURE dbo.GetOrdersByStatus
@status INT
WITH RECOMPILE
AS
BEGIN
SELECT order_id, amount
FROM dbo.orders
WHERE status = @status;
END;
不过WITH RECOMPILE会增加编译开销，仅适合参数敏感且调用不频繁的过程。对于高并发场景，可使用局部变量复制参数，打断嗅探链：
CREATE PROCEDURE dbo.GetOrdersByStatus2
@status INT
AS
BEGIN
DECLARE @s INT = @status;
SELECT order_id, amount
FROM dbo.orders
WHERE status = @s;
END;
这种方式让优化器使用列的平均分布预估，虽不一定最优，但往往比被极端参数带偏更稳定。实践中应结合具体业务评估采用哪种策略。
二、用集合操作替代游标与循环
许多早期存储过程习惯用游标逐行处理数据，这在小规模时尚可，数据膨胀后就会成为性能黑洞。游标不仅产生大量网络与锁开销，还阻止优化器做批量合并。
改写为基于集合的SQL，通常能将分钟级任务压缩到秒级。例如需要将过期订单标记关闭，游标写法如下：
DECLARE cur CURSOR FOR
SELECT order_id FROM dbo.orders WHERE expire_time < GETDATE();
DECLARE @id INT;
OPEN cur;
FETCH NEXT FROM cur INTO @id;
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE dbo.orders SET status = 9 WHERE order_id = @id;
FETCH NEXT FROM cur INTO @id;
END;
CLOSE cur;
DEALLOCATE cur;
等价集合写法只需一句，由存储引擎内部批量完成：
UPDATE dbo.orders SET status = 9 WHERE expire_time < GETDATE();
后者不仅代码简洁，而且能利用索引定位与并行更新。若业务逻辑确实需逐行复杂计算，可考虑在应用层分批拉取处理，或将计算下推为内联表值函数，避免长事务持有锁。
三、临时表与表变量的正确选择
存储过程中常借助临时表（#table）或表变量（@table）暂存中间结果。二者在统计信息维护上差异明显：临时表有完整统计信息，优化器可准确估算行数；表变量在旧版本中无统计，常被判为单行，导致关联时选择嵌套循环。
当中间数据量较大且参与多表关联时，优先用临时表并创建必要索引：
CREATE TABLE #tmp_user (uid INT PRIMARY KEY, score INT); INSERT INTO #tmp_user SELECT uid, SUM(score) FROM dbo.logs GROUP BY uid; SELECT u.name, t.score FROM dbo.users u JOIN #tmp_user t ON u.uid = t.uid;
若数据量极小且生命周期短，表变量可减少事务日志与锁竞争。但应注意，从SQL Server 2019起表变量也支持延迟编译统计，老版本仍需谨慎。通过对比实际IO，可确定哪种方式更适配当前数据规模。
四、避免动态SQL与隐式转换陷阱
在存储过程内拼接动态SQL不仅带来注入风险，还会让执行计划难以复用。如下写法每次字符串不同都会重编译：
DECLARE @sql NVARCHAR(MAX); SET @sql = N'SELECT * FROM dbo.orders WHERE status = ' + CAST(@status AS NVARCHAR(10)); EXEC sp_executesql @sql;
应尽量用参数化查询替代。同时，参数类型与列类型不一致会触发隐式转换，使索引失效。例如列status为TINYINT，而过程参数声明为INT，优化器可能放弃索引查找。统一类型定义是低成本高收益的优化点。
五、索引与统计信息的协同维护
即便语句写法合理，缺失覆盖索引也会迫使存储过程做键查找或全表扫描。利用执行计划的缺失索引建议，结合业务读写比创建包含列索引：
CREATE INDEX ix_orders_status_include ON dbo.orders (status) INCLUDE (order_id, amount);
此外，统计信息过期会让优化器误判行数。对大表应设自动更新并定期手动更新，或启用增量统计。过滤统计针对特定数据子集建模，能进一步提升特殊查询的预估准确度。
综上，存储过程性能优化不是单点调参，而是从计划生成、写法范式、对象选型到索引统计的连贯工程。定位瓶颈后分步验证，才能确保改动真实有效且不引发次生问题。
SQLstored_procedureperformance_optimization修改时间：2026-08-07 06:09:29