如何在SQLServer中优化存储过程？提高执行效率的实用方法-SQL-PHP中文网

答案是优化SQL Server存储过程需从SQL语句、索引设计、执行计划分析和参数嗅探应对等多方面入手。首先避免SELECT *，确保WHERE条件SARGable，合理使用JOIN与临时表，优先用EXISTS代替IN；其次创建覆盖索引、维护索引减少碎片；通过执行计划、STATISTICS IO和DMVs定位高成本操作；针对参数嗅探可采用局部变量、OPTION (RECOMPILE)或OPTIMIZE FOR提示来稳定执行计划。

如何在sqlserver中优化存储过程？提高执行效率的实用方法

在SQL Server中优化存储过程，提高其执行效率，这绝不是一个一劳永逸的任务，它更像是一场持续的侦探工作和精细化打磨。核心思路无非是让数据库少做无用功，或者说，让它用最聪明、最直接的方式完成你的指令。这通常意味着深入理解你的查询是如何被执行的，以及数据结构如何影响这个过程。

提升SQL Server存储过程执行效率，需要从多个维度进行考量和实践。

解决方案

很多时候，我们写存储过程就像在写一份菜谱，希望数据库这个大厨能按部就班地完成。但问题是，我们给的“菜谱”可能不够清晰，或者大厨手里的工具（索引）不够锋利。

首先，审视你的SQL语句本身。这是最直接也最关键的一步。

*避免`SELECT `**：只选取你真正需要的列。这不仅减少了网络传输负担，也可能让覆盖索引发挥作用，避免了不必要的键查找。
优化
WHERE
登录后复制
子句：确保你的过滤条件是“SARGable”（Search Argument Able）。这意味着避免在索引列上使用函数、进行类型转换或使用
```
LIKE '%keyword'
```
登录后复制
这样的模式。例如，
```
WHERE YEAR(OrderDate) = 2023
```
登录后复制
会导致全表扫描，而
```
WHERE OrderDate >= '2023-01-01' AND OrderDate < '2024-01-01'
```
登录后复制
则能有效利用索引。
合理使用
JOIN
登录后复制
和子查询：在很多情况下，
```
JOIN
```
登录后复制
的性能优于相关子查询。但如果子查询返回的数据量很小，或者逻辑上更清晰，也并非绝对不可用。关键在于执行计划会怎么处理。
减少不必要的
OR
登录后复制
条件：过多的
```
OR
```
登录后复制
条件有时会让优化器难以选择最佳索引。如果可能，考虑使用
```
UNION ALL
```
登录后复制
来拆分查询，或者重新设计逻辑。
处理大数据量操作：对于涉及大量数据更新或删除的存储过程，考虑分批处理。例如，在一个
```
WHILE
```
登录后复制
循环中每次处理几千条记录，而不是一次性处理几十万条。这能有效降低事务日志的压力，减少锁的持有时间。
临时表与表变量的选择：
- 表变量 (
  DECLARE @TableVariable TABLE (...)
  登录后复制
  )：通常在小数据集、生命周期短的场景下表现良好，因为它不涉及事务日志和统计信息，编译时优化器对其行数估计为1，可能导致次优计划。
- 临时表 (
  CREATE TABLE #TempTable (...)
  登录后复制
  )：适用于大数据集，或者你需要索引、统计信息来帮助优化器生成更好的执行计划的场景。它会写入
```
tempdb
```
  登录后复制
  ，有事务日志，但优化器能获取到更准确的统计信息。
善用
EXISTS
登录后复制
代替
IN
登录后复制
：当子查询返回大量数据时，
```
EXISTS
```
登录后复制
通常比
```
IN
```
登录后复制
更高效，因为它在找到匹配项后就会停止扫描。

其次，索引是存储过程的“加速器”。

创建合适的索引：不仅仅是主键和外键。分析你的
```
WHERE
```
登录后复制
子句、
```
JOIN
```
登录后复制
条件和
```
ORDER BY
```
登录后复制
子句中经常出现的列，为它们创建非聚集索引。
覆盖索引：如果一个非聚集索引包含了查询中所有需要的列（在
```
INCLUDE
```
登录后复制
子句中），那么数据库就不需要再去查找实际的数据行，这能显著提高
```
SELECT
```
登录后复制
查询的性能。
维护索引：定期重建或重组索引，减少碎片，保持索引的效率。

最后，理解执行计划。

这是数据库优化最核心的工具。通过
```
SET SHOWPLAN_ALL ON
```
登录后复制
、
```
SET STATISTICS IO ON
```
登录后复制
、
```
SET STATISTICS TIME ON
```
登录后复制
或者直接在SSMS中查看“实际执行计划”，你可以看到查询每一步的成本、扫描了多少页、逻辑读写次数等等。
关注那些成本最高的运算符，比如“Table Scan”（全表扫描）、“Clustered Index Scan”（聚集索引扫描，如果不是你想要的）、“Sort”（排序）、“Key Lookup”（键查找，通常意味着非聚集索引没有覆盖所有列，需要回表）。这些都是优化的重点。

记住，没有银弹，每一个优化都可能带来新的权衡。

如何识别SQL Server存储过程的性能瓶颈？

要找出存储过程的性能瓶颈，就像医生给病人诊断一样，需要一套系统的方法和趁手的工具。我通常会从几个角度入手，因为有时候问题并不出在存储过程本身，而是环境。

一个直接的方法是使用SQL Server Management Studio (SSMS)。打开一个查询窗口，执行你的存储过程，然后查看它的实际执行计划。这简直是数据库优化师的“X光片”。在执行计划中，你可以看到每个操作符的相对成本，以及数据流向。那些颜色深、百分比高的操作符，就是你首先要关注的。比如，如果看到一个“Table Scan”或者“Clustered Index Scan”的成本特别高，而你预期它应该走索引查找，那很可能就是索引缺失或不当。

除了执行计划，我还会经常用到

SET STATISTICS IO ON

登录后复制

和
SET STATISTICS TIME ON
登录后复制
。这两个命令在执行查询后，会返回逻辑读、物理读、CPU时间、消耗时间等关键指标。逻辑读（logical reads）是衡量I/O效率最重要的指标之一，因为它代表了数据库引擎从数据缓存中读取数据页的次数。读得越多，通常意味着效率越低。

更进一步，SQL Server的动态管理视图（DMVs）是宝藏。

sys.dm_exec_query_stats

登录后复制

和

sys.dm_exec_procedure_stats

登录后复制

能告诉你哪些查询或存储过程消耗了最多的CPU、I/O或执行时间。你可以通过它们快速定位到系统中最“耗资源”的那些家伙。比如，我可能会这样查询：

SELECT
    DB_NAME(qp.dbid) AS DatabaseName,
    OBJECT_NAME(qp.objectid, qp.dbid) AS ObjectName,
    SUBSTRING(st.text, (qs.statement_start_offset / 2) + 1,
              ((CASE qs.statement_end_offset
                  WHEN -1 THEN DATALENGTH(st.text)
                  ELSE qs.statement_end_offset
                END - qs.statement_start_offset) / 2) + 1) AS StatementText,
    qs.execution_count,
    qs.total_logical_reads,
    qs.total_logical_writes,
    qs.total_worker_time, -- CPU time
    qs.total_elapsed_time, -- Total duration
    qs.creation_time
FROM
    sys.dm_exec_query_stats qs
CROSS APPLY
    sys.dm_exec_sql_text(qs.sql_handle) st
CROSS APPLY
    sys.dm_exec_query_plan(qs.plan_handle) qp
WHERE
    st.text LIKE '%YourStoredProcedureName%' -- 替换为你的存储过程名
ORDER BY
    qs.total_logical_reads DESC;

登录后复制

这段代码能帮你找到特定存储过程中，哪些语句的逻辑读最高。

存了个图

视频图片解析/字幕/剪辑，视频高清保存/图片源图提取

查看详情

对于生产环境的持续监控，SQL Server Query Store是一个非常棒的功能。它可以自动捕获查询历史、执行计划和运行时统计信息，让你能轻松地识别出回归的查询、查看历史性能数据，甚至强制使用某个特定的执行计划。我个人觉得，Query Store是近年来SQL Server在性能诊断方面最实用的进步之一。

虽然SQL Server Profiler和Extended Events也能捕获详细的事件数据，但Profiler在生产环境中使用时开销较大，Extended Events则更为轻量和强大，但学习曲线稍陡。对于日常的存储过程优化，我通常会从SSMS的执行计划、

STATISTICS IO/TIME

登录后复制

和DMVs开始。

参数嗅探对存储过程执行效率有何影响，又该如何应对？

参数嗅探（Parameter Sniffing），这个词听起来有点神秘，但它在SQL Server存储过程优化中扮演着一个非常微妙且重要的角色。简单来说，当一个存储过程第一次被执行时，SQL Server的查询优化器会“嗅探”到你传入的参数值，并基于这些特定的参数值生成一个它认为最优的执行计划。然后，这个执行计划就会被缓存起来，供后续调用使用。

问题就出在这里：如果后续调用传入的参数值，其数据分布与第一次嗅探到的参数值大相径庭，那么之前缓存的执行计划可能就不是最优的了，甚至会变得非常低效。比如，第一次调用你传入一个不常见的值，优化器可能生成一个使用索引查找的计划；但第二次你传入一个非常常见的值（比如某个状态码），这个计划可能导致大量的查找，远不如全表扫描或更宽泛的索引扫描。这就是参数嗅探的“双刃剑”效应。

我曾经遇到过一个存储过程，在开发环境跑得飞快，一到生产环境就偶尔慢如蜗牛，最后发现就是参数嗅探在作怪。某个特定参数在生产环境的分布极其不均匀，导致缓存的计划在某些情况下完全失效。

那么，如何应对参数嗅探呢？有几种策略，每种都有其适用场景和权衡：

```
WITH RECOMPILE
```
登录后复制
选项：在创建或修改存储过程时，可以在
```
CREATE PROCEDURE
```
登录后复制
或
```
ALTER PROCEDURE
```
登录后复制
语句后面加上
```
WITH RECOMPILE
```
登录后复制
。这意味着每次执行这个存储过程时，它都会重新编译，生成一个新的执行计划。
```
CREATE PROCEDURE GetOrdersByStatus
    @Status INT
WITH RECOMPILE
AS
BEGIN
    SELECT * FROM Orders WHERE OrderStatus = @Status;
END;
```
登录后复制
优点：每次都能获得针对当前参数值的最佳计划。缺点：频繁的重新编译会增加CPU开销，对于执行非常频繁的存储过程，这可能不是一个好主意。
```
OPTION (RECOMPILE)
```
登录后复制
查询提示：如果不想整个存储过程都重新编译，你可以在存储过程内部的某个特定查询上使用
```
OPTION (RECOMPILE)
```
登录后复制
。这样只有这个查询会重新编译，而不是整个存储过程。
```
CREATE PROCEDURE GetOrdersByStatus
    @Status INT
AS
BEGIN
    SELECT * FROM Orders WHERE OrderStatus = @Status
    OPTION (RECOMPILE);
END;
```
登录后复制
优点：更细粒度的控制，只对有参数嗅探问题的查询进行重新编译。缺点：同样有编译开销，但比
```
WITH RECOMPILE
```
登录后复制
小。
使用局部变量“欺骗”优化器：这是一种常见的技巧。在存储过程内部，将传入的参数值赋给一个局部变量，然后用这个局部变量进行查询。优化器在编译时，对局部变量的值是“未知”的，它会生成一个基于平均数据分布的通用执行计划，而不是基于特定参数值的计划。
```
CREATE PROCEDURE GetOrdersByStatus
    @Status INT
AS
BEGIN
    DECLARE @LocalStatus INT = @Status;
    SELECT * FROM Orders WHERE OrderStatus = @LocalStatus;
END;
```
登录后复制
优点：避免了重新编译的开销，通常能生成一个“足够好”的通用计划。缺点：生成的计划可能不是针对任何特定参数值的“最优”计划，但对于参数分布不均的情况，它往往比被嗅探的计划更稳定。
```
OPTIMIZE FOR
```
登录后复制
查询提示：你可以告诉优化器，针对某个特定的参数值来生成执行计划，或者针对“未知”值来生成计划。
```
CREATE PROCEDURE GetOrdersByStatus
    @Status INT
AS
BEGIN
    SELECT * FROM Orders WHERE OrderStatus = @Status
    OPTION (OPTIMIZE FOR (@Status UNKNOWN)); -- 针对未知值优化
    -- 或者 OPTION (OPTIMIZE FOR (@Status = 1)); -- 针对特定值优化
END;
```
登录后复制
优点：可以指导优化器生成更符合你预期的计划。缺点：如果指定了特定值，那么对于其他值可能又会回到参数嗅探的问题。

我个人更倾向于先尝试使用局部变量，因为它通常在性能和稳定性之间找到了一个不错的平衡点。如果局部变量法依然不够，或者某个查询确实需要频繁地针对不同参数进行优化，那么

OPTION (RECOMPILE)

登录后复制

是下一个值得考虑的选项。

索引在存储过程优化中扮演怎样的角色？

索引，在SQL Server存储过程优化中，扮演的角色简直是基石级的。没有合适的索引，再精妙的SQL语句也可能在海量数据面前寸步难行。我经常把索引比作一本书的目录或字典的部首检字表。没有它们，你要找一个词或一个章节，就得从头到尾翻阅整本书，效率可想而知。

核心作用：索引的主要作用是加速数据检索。当你执行

SELECT

登录后复制

、

UPDATE

登录后复制

、

DELETE

登录后复制

语句时，

WHERE

登录后复制

子句、

JOIN

登录后复制

条件和

ORDER BY

登录后复制

子句都会尝试利用索引来快速定位数据，而不是进行代价高昂的全表扫描。

聚集索引与非聚集索引：

聚集索引（Clustered Index）：这是表数据物理存储的顺序。一张表只能有一个聚集索引。它直接决定了数据行在磁盘上的存放顺序。如果你的查询经常需要按照某个列的范围进行检索，或者需要返回大量数据，那么将该列设置为聚集索引通常是最佳选择。例如，订单表通常会以订单ID或创建日期作为聚集索引。
非聚集索引（Non-Clustered Index）：它不改变数据行的物理顺序，而是创建了一个独立于数据行存储的结构，其中包含索引列的值和指向实际数据行的指针（对于聚集表是聚集索引键，对于堆表是RID）。一张表可以有多个非聚集索引。它们就像书本后面附的多个主题索引，可以从不同维度快速定位内容。

覆盖索引（Covering Index）：这是一个非常强大的概念。如果一个非聚集索引包含了你查询中

SELECT

登录后复制

列表和

WHERE

登录后复制

子句中所有需要的列，那么数据库就不需要再去查找实际的数据行（“回表”或“键查找”）。这种情况下，数据库可以直接从索引中获取所有需要的信息，大大减少了I/O操作，提升了性能。我经常会为了几个关键查询而专门设计覆盖索引，效果立竿见影。

索引的维护与权衡：索引并非越多越好，也不是一劳永逸。

写入性能影响：每次对表进行
```
INSERT
```
登录后复制
、
```
UPDATE
```
登录后复制
、
```
DELETE
```
登录后复制
操作时，数据库不仅要更新表中的数据，还要更新所有相关的索引。索引越多，写入操作的开销就越大。因此，需要在读取性能和写入性能之间找到一个平衡点。
存储空间：索引也需要占用磁盘空间。
索引碎片：随着数据的不断修改，索引可能会变得碎片化，影响其性能。定期进行索引重建（Rebuild）或重组（Reorganize）是必要的维护工作。

如何利用索引优化存储过程：

分析执行计划：这是第一步，也是最重要的一步。执行计划会清楚地告诉你，哪些查询步骤没有使用索引，或者使用了效率低下的索引。特别关注“Table Scan”、“Clustered Index Scan”和“Key Lookup”等操作符。
为
WHERE
登录后复制
、
JOIN
登录后复制
、
ORDER BY
登录后复制
子句中的列创建索引：这些是索引最常发挥作用的地方。
考虑列的基数（Cardinality）：对于那些唯一值很少的列（如性别、状态码），索引的效果可能不如唯一值多的列。但如果这些列经常被用于过滤，并且结合其他列一起创建复合索引，效果会很好。
复合索引的列顺序：复合索引的列顺序非常重要。通常，将查询中最常用于过滤的列放在前面，然后是用于排序或连接的列。例如，如果你的查询经常是
```
WHERE ColA = X AND ColB = Y
```
登录后复制
，那么索引
```
(
```
登录后复制
ColA
```
,
```
登录后复制
ColB
```
)
```
登录后复制
会比
```
(
```
登录后复制
ColB
```
,
```
登录后复制
ColA
```
)
```
登录后复制
更有效。
筛选索引（Filtered Index）：如果你的查询经常针对表中数据的一个小子集进行过滤（例如
```
WHERE Status = 'Active'
```
登录后复制
），可以创建一个筛选索引，只包含满足特定条件的行。这能减少索引的大小，提高查询效率。