SQL Server存储过程优化-育师

数据准备

优化必须有数据量。只有几十行数据时，很多慢 SQL 问题不会暴露。

建议准备一张订单表和一张用户表，用来模拟真实业务查询。

建表

CREATE TABLE dbo.Users ( UserId INT IDENTITY(1,1) PRIMARY KEY, UserName NVARCHAR(50) NOT NULL, Phone VARCHAR(20) NULL, CreateTime DATETIME NOT NULL DEFAULT GETDATE() ); CREATE TABLE dbo.Orders ( OrderId BIGINT IDENTITY(1,1) PRIMARY KEY, UserId INT NOT NULL, Status TINYINT NOT NULL, OrderAmount DECIMAL(18,2) NOT NULL, CreateTime DATETIME NOT NULL, Remark NVARCHAR(500) NULL );

插入数据

INSERT INTO dbo.Users(UserName, Phone) SELECT TOP (100000) N'User_' + CAST(ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS NVARCHAR(20)), CAST(13000000000 + ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS VARCHAR(20)) FROM sys.all_objects a CROSS JOIN sys.all_objects b; INSERT INTO dbo.Orders(UserId, Status, OrderAmount, CreateTime, Remark) SELECT TOP (1000000) ABS(CHECKSUM(NEWID())) % 100000 + 1, ABS(CHECKSUM(NEWID())) % 5, ABS(CHECKSUM(NEWID())) % 10000 / 10.0, DATEADD(MINUTE, -ABS(CHECKSUM(NEWID())) % 1000000, GETDATE()), N'测试订单' FROM sys.all_objects a CROSS JOIN sys.all_objects b CROSS JOIN sys.all_objects c;

开启观察标

SET STATISTICS IO ON; SET STATISTICS TIME ON;

看

逻辑读取（logical reads ）：逻辑读，越高说明扫描的数据页越多。
CPU 时间（CPU time ）：CPU消耗
占用时间（elapsed time ）：实际的执行时间

执行SQL后，在“消息”这里会出现以下内容

SQL Server 分析和编译时间：这个阶段主要做以下三件事情，解析SQL语法、检查对象和字是否存在、生成或服用执行计划
行数和IO信息：这是查询实际访问数据的情况，比如下图：
1. 100000行受影响：说明这条SQL返回或影响了100000行数据
2. 表‘user'：说明统计的是user表
3. 扫描计数：对这个表/索引扫描1次
4. 逻辑读取721次：从内存缓存里读取了721数据页，每页8KB，721*8K=5768KB≈5.6m
5. 物理读取2次：有2页是从磁盘中读取
6. 预读724次：说明SQL Server判断接下来要读这些页，于是提前从磁盘预读了7245页
执行时间:CPU真正执行的耗时

SQL Server数据页
表和索引在磁盘/内存里的最下存储单元
1、数据页大小固定8KB
2、一行数据也会放进数据页
3、SQL Server不是按行读取，而是按页读取，比如：查一行数据，它会把所在的数据页读出来
4、页组成区（Extent）1页=8KB，1区=8页=64KB
5、索引也是由页组成的，索引不是一个抽象的目录，它本身也存放在8KB的页里面，索引结构类似B+树，从根页开始找-->找到中间页-->找到叶子页-->定位到数据行
FAQ:
Q1:表数据怎么分页存？
①如果有主键，主键会创建聚集索引，数据按聚集索引建组织，简单说，就是表数据页会按照id顺序排列
比如：Page 1: id 1 - 100
Page 2: id 101 - 200
Page 3: id 201 - 300
如果查 id = 150 ，SQL Server可以通过B+树快速定位到Page2.
②没有聚集索引，这个表叫堆表（Heap）,堆表数据没有明确的顺序，
比如：Page A: id 900, 12, 300
Page B: id 5, 8000, 21
Page C: id 100, 77, 600
如果查id = 150 ，可能就要扫描很多页

一、不要先优化，先测量

没有测量就没有优化。一个存储过程慢，可能是：

缺索引。
索引用不上。
返回列太多。
排序或聚合太重。
参数嗅探。
被其他事务阻塞。
统计信息过旧。

创建一个存储过程：

Create or alter PROC dbo.GetOrdersByUser @UserId INT AS BEGIN select * from dbo.Orders where UserId = @UserId; end;

运行这个存储过程：

exec dbo.GetOrdersByUser @UserId = 10011;

查看运行情况：

如图，执行计划中的逻辑读取（logical reads ）很高6475次

记录本次执行内容

执行耗时：9ms CPU 时间：78ms Orders 表 logical reads：6475

FAQ：为什么会产生两次分析和两次执行日志？
执行的是存储过程，不是一条单独的 SELECT，SQL Server会把不用层级/语句的时间分别打印出来，所以会有两组
第一组外层exec命令本身的解析/编译时间，几乎没成本
第二组存储过程中真正SQL语句的编译时间
后面两个执行时间也类似
第一个是SQL的执行时间
第二个是存储过程内部查询语句的执行时间

二、select * 问题

select * 的问题不只是返回的列多，还会影响索引设计。

如果查询只需要5个字段，却返回30个字段，会导致：

IO增加
网络传输增加
内存消耗增加
更容易产生Key Lookup
很难用覆盖索引优化

去掉*优化

create or alter proc GetUserOrders_Good @UserId int AS begin select OrderId,UserId,Status,OrderAmount,CreateTime from dbo.Orders where UserId = @UserId; end; -- 执行语句 exec dbo.GetUserOrders_Good @UserId = 10011;

执行结果：

记录本次执行内容

执行耗时：34ms CPU 时间：0ms Orders 表 logical reads：6475

FAQ
Q1：为什么不加*，指明列名，逻辑读取数量还是一样的
它们现在大概率都在扫描同一张orders表/同一个聚集索引，虽然返回列不同，但为了找到目标值行，读取的数据页是一样的
Q2：不加*，怎么执行耗时还变长了？
一次执行的“占用时间”会波动，尤其现在是毫秒级查询，不能直接说明不加*反而更慢

创建非聚合索引

创建非聚集索引

create index IX_Orders_UserId on dbo.Orders(UserId);

调用存储过程结果

记录本次执行结果

执行耗时：2ms CPU 时间：0ms Orders 表 logical reads：36

FAQ：聚集索引和非聚集索引区别
聚集索引：
决定表数据物理/逻辑存放顺序
数据表本身就会按照 id 组织在聚集索引的叶子节点上
一张表通常只能有一个聚集索引，因为数据只能按一种方式组织
适合场景：主键、范围查询、排序
写入影响：有影响
非聚集索引：
额外建的一份“目录”
适合场景：高频查询条件、关联字段、覆盖查询
写入影响：索引越多写入越慢

创建覆盖索引

create index IX_Orders_UserId_Cover on dbo.Orders(UserId) include(Status,OrderAmount,CreateTime);

执行结果：

记录本次执行结果：

执行耗时：0ms CPU 时间：0ms Orders 表 logical reads：3

FAQ
Q1：include 是什么意思?
include 里的列不参与索引排序，只存放在索引叶子节点上。
以上这句的意思是
按UserId创建目录，叶子节点上额外带上Status,OrderAmount,CreateTime
UserId用于where查询，Status,OrderAmount,CreateTime用于select返回
Q2：非集合索引和覆盖索引对比
非集合索引覆盖索引
索引 UserId UserId
包含列无 Status,OrderAmount,CreateTime
能否按UserId查找可以可以
是否覆盖查询不一定可以覆盖指定查询
是否容易Key Lookup 容易不容易
占用空间小大
写入维护成本较低较高
适合场景只过滤或返回少量键列高频查询固定返回这些列

三、索引的核心：让查询少读数据

索引优化的本质不是让SQL用上索引，而是让SQl少读数据。

常见索引类型

聚集索引：决定数据物理组织方式，一张表通常一个
非聚合索引：额外的数据查找结构
组合索引：多个字段组成的索引
覆盖索引：索引中包含查询需要的所有列

先删除之前创建的索引

drop index IX_Orders_UserId on dbo.Orders; drop index IX_Orders_UserId_Cover on dbo.Orders;

示例查询语句

SELECT OrderId, UserId, Status, OrderAmount, CreateTime FROM dbo.Orders WHERE UserId = 23093 AND Status = 0 AND CreateTime >= '2024-01-06' AND CreateTime < '2026-08-06' ORDER BY CreateTime DESC;

结果

记录本次结果：

执行耗时：29ms CPU 时间：62ms Orders 表 logical reads：6475

FAQ：为什么日志中会出现表'Worktable'？
worktable是SQL Server在执行查询时临时创建的内部工作表，通常放在tempdb中。
常见的触发场景：
order by、group by、distinct、union、hash join / hash aggregate、spool、游标、复杂查询中间结果

推荐索引

CREATE INDEX IX_Orders_User_Status_CreateTime ON dbo.Orders(UserId, Status, CreateTime DESC) INCLUDE(OrderAmount);

示例语句执行结果：

记录本次执行结果

执行耗时：0ms CPU 时间：0ms Orders 表 logical reads：3

为什么这样设计

UserId：等值过滤，放前面
Status：等值过滤，继续放前面
CreateTime：范围过滤，按照倒序存放，同时满足order by
OrderAmount：只返回，不过滤，放 include

删除IX_Orders_User_Status_CreateTime索引，分别建立以下两个索引，查看结果。

CREATE INDEX IX_Orders_UserId_Test ON dbo.Orders(UserId); 记录本次执行结果 执行耗时：0ms CPU 时间：0ms Orders 表 logical reads：54 CREATE INDEX IX_Orders_Status_Test ON dbo.Orders(Status); 记录本次执行结果 执行耗时：10ms CPU 时间：0ms Orders 表 logical reads：6475

注意：每次训练完毕后，请删除索引

四、组合索引顺序

组合索引不是字段越多越好，字段顺序非常重要

一般原则：

等值查询列优先
范围查询列放在等值查询之后
排序列尽量和索引顺序一致
只返回但不筛选的列放include

比较两种不同顺序的索引

CREATE INDEX IX_Test_A ON dbo.Orders(UserId, Status, CreateTime); 记录本次执行结果 执行耗时：0ms CPU 时间：0ms Orders 表 logical reads：18 CREATE INDEX IX_Test_B ON dbo.Orders(CreateTime, UserId, Status); 记录本次执行结果 执行耗时：44ms CPU 时间：47ms Orders 表 logical reads：3363

可通过逻辑读取来看，按照原则顺序来，查询的数据页越少

五、避免函数包字段

如果在字段外面套函数，SQL Server往往无法直接利用索引范围查找，简单说，用函数会使索引失效。

先加索引

CREATE INDEX IX_Orders_CreateTime ON dbo.Orders(CreateTime) INCLUDE(OrderAmount);

用函数写法 select OrderId,CreateTime,OrderAmount from dbo.Orders where CONVERT(date,CreateTime) = '2026-08-10'; 记录本次执行结果 执行耗时：268ms CPU 时间：0ms Orders 表 logical reads：12 优化写法，不使用函数 select OrderId,CreateTime,OrderAmount from dbo.Orders where CreateTime >= '2026-08-10' and CreateTime < '2026-08-11' 记录本次执行结果 执行耗时：0ms CPU 时间：134ms Orders 表 logical reads：6

六、避免隐式转换

参数类型和字段类型不一致，会导致隐式转换，可能会让索引失效

Users表字段 Phone的类型是VARCHAR(20)

比较下面两个存储

-- 创建一个不匹配类型的存储 CREATE OR ALTER PROC dbo.GetUserByPhone_A @Phone bigint AS BEGIN SELECT UserId, UserName FROM dbo.Users WHERE Phone = @Phone; END; -- 执行存储过程 exec dbo.GetUserByPhone_A @Phone = 13000096415 记录本次执行结果 执行耗时：16ms CPU 时间：16ms Users 表 logical reads：721 -- 创建一个类型匹配的存储 CREATE OR ALTER PROC dbo.GetUserByPhone_B @Phone VARCHAR(20) AS BEGIN SELECT UserId, UserName FROM dbo.Users WHERE Phone = @Phone; END; -- 执行存储过程 exec dbo.GetUserByPhone_B @Phone = 13000096415; 记录本次执行结果 执行耗时：1ms CPU 时间：0ms Users 表 logical reads：6

查看逻辑读取发现，隐式转换会使索引失效

七、Key Lookup优化

Key Lookup:索引里字段不够，SQL Server 再按主键回主表取缺少的字段。

少量Ket Lookup可以接受，大量key lookup会很慢

--添加UserId索引 CREATE INDEX IX_Orders_UserId ON dbo.Orders(UserId); --示例SQL SELECT OrderId, UserId, OrderAmount, CreateTime FROM dbo.Orders WHERE UserId = 1001; 记录本次执行结果 执行耗时：8ms CPU 时间：0ms Orders 表 logical reads：30 --添加覆盖索引 CREATE INDEX IX_Orders_UserId_Cover2 ON dbo.Orders(UserId) INCLUDE(OrderAmount, CreateTime); --示例SQL SELECT OrderId, UserId, OrderAmount, CreateTime FROM dbo.Orders WHERE UserId = 1001; 记录本次执行结果 执行耗时：0ms CPU 时间：0ms Orders 表 logical reads：3

减少key lookup可以提升查询掉率

注意：不要把大字段放进include，如果不是高频查询的必要字段，不建议放入覆盖索引

八、OR条件优化

or容易让优化器难以选择索引，尤其两个条件对应不用字段时

-- 创建索引 CREATE INDEX IX_Orders_UserId ON dbo.Orders(UserId); -- exists select u.UserId,u.UserName from dbo.Users u where exists ( select 1 from dbo.Orders o where o.UserId = u.UserId ); 记录本次执行结果 执行耗时：935ms CPU 时间：109ms Orders 表 logical reads：2252 Users 表 logical reads：721 -- join select u.UserId,u.UserName from dbo.Users u join dbo.Orders o on o.UserId = u.UserId; 记录本次执行结果 执行耗时：9172ms CPU 时间：967ms Orders 表 logical reads：2310 Users 表 logical reads：757 -- in select u.UserId,u.UserName from dbo.Users u where u.UserId in ( select o.UserId from dbo.Orders o ); 记录本次执行结果 执行耗时：934ms CPU 时间：63ms Orders 表 logical reads：2252 Users 表 logical reads：721

结果如下

FAQ
Q1:出现的Workfile是什么？
Workfile也是SQL Server内部临时文件，通常也在tempdb中
触发的场景：Hash join，Hash Aggregate，Sort 溢出，并行查询中间数据
Q2：Worktable为什么出现两次？
一个用于union去重，一个用于并行/中间结果/排序

注意：

union 会默认去重，等价于union distinct
如果不需要去重，可以使用union all，不去重，通常更快

九、Exists、in、join

只判断是否存在，优先考虑exists

需要返回关联表字段时，用join

判断值是否在集合中，子查询返回单列，用in

下面比较判断是否存在

-- 创建索引 CREATE INDEX IX_Orders_UserId ON dbo.Orders(UserId); -- exists select u.UserId,u.UserName from dbo.Users u where exists ( select 1 from dbo.Orders o where o.UserId = u.UserId ); -- join select u.UserId,u.UserName from dbo.Users u join dbo.Orders o on o.UserId = u.UserId; -- in select u.UserId,u.UserName from dbo.Users u where u.UserId in ( select o.UserId from dbo.Orders o );

EXISTS 和 IN 基本等价；
JOIN 明显更慢，是因为它返回了重复数据。

十、大分页优化

传统的分页越往后越慢，例如

offset 90000 rows fetch next 20 rows only

这意味着前90000行也要被扫描、排序、跳过。

-- 创建索引 CREATE INDEX IX_Orders_CreateTime_OrderId ON dbo.Orders(CreateTime DESC, OrderId DESC) INCLUDE(OrderAmount); -- 使用分页逻辑 select OrderId,CreateTime,OrderAmount from dbo.Orders o order by CreateTime desc offset 90000 rows fetch next 20 rows only; 记录本次执行结果 执行耗时：50ms CPU 时间：0ms Orders 表 logical reads：364 -- 使用创建时间进行查询 SELECT TOP (20) OrderId, CreateTime, OrderAmount FROM dbo.Orders WHERE CreateTime < '2026-06-08 20:49:08.033' ORDER BY CreateTime DESC; 记录本次执行结果 执行耗时：0ms CPU 时间：0ms Orders 表 logical reads：3

如果是大分页会导致逻辑读取增多，可以使用时间进行约束

十一、临时表拆分复杂查询

复杂的SQL不一定要写一条到底，对于大数据查询，可以先过滤，再关联，再聚合。

临时表的优点：

可以缩小数据查询范围
可以给中间结果加索引
SQL Server可以为临时表生成统计信息

比如以下示例

select u.UserId,u.UserName,SUM(o.OrderAmount) as totalAmount from dbo.Users u inner join dbo.Orders o on o.UserId = o.UserId where o.CreateTime > '2022-01-01' and o.CreateTime < '2024-12-31' group by u.UserId,u.UserName 记录本次执行结果 执行耗时：1130ms CPU 时间：327ms Orders 表 logical reads：6475 Users 表 logical reads：757

后面拆分成临时表，并加索引

-- 创建临时表#FilteredOrders select OrderId,UserId,OrderAmount into #FilteredOrders from dbo.Orders where CreateTime > '2022-01-01' and CreateTime < '2024-12-31'; -- 在临时表#FilteredOrders加UserId索引 create index IX_FilteredOrders_UserId on #FilteredOrders(UserId); -- 查询 select u.UserId,u.UserName,SUM(f.OrderAmount) as totalAmount from dbo.Users u inner join #FilteredOrders f on u.UserId = f.OrderId group by u.UserId,u.UserName 记录本次执行结果 执行耗时：319ms CPU 时间：0ms #FilteredOrders 表 logical reads：584 Users 表 logical reads：757

两次结果对比：拆分临时表后，逻辑读取变少，内存消耗减少

十二、表变量和临时表

小数据量可以使用表变量，大数据量优先使用临时表

创建表变量：它不是普通变量，而是一张临时的小表。

-- 创建表变量 DECLARE @OrderIds TABLE ( OrderId BIGINT PRIMARY KEY ); -- 在变中将查询的id，放入表变量中 INSERT INTO @OrderIds(OrderId) SELECT OrderId FROM dbo.Orders WHERE UserId = 10011;

表变量的逻辑是，创建一个临时表变量@OrderIds，里面只有一列OrderId，之后可以将查到的orderid放入表变量中

十三、参数嗅探

SQL Server 会缓存存储过程执行计划，第一次执行时的参数可能会影响后续执行

如果不同参数对应的数据量差异巨大，就可能出现：

小数据参数编译出来的计划，用在大数据参数上很慢
大数据参数编译出来的计划，用在小数据参数上也可能不理想

示例SQL

CREATE OR ALTER PROC dbo.GetOrderByStatus @Status VARCHAR(50) AS BEGIN SELECT OrderId, UserId, Status, OrderAmount FROM dbo.Orders WHERE Status IN ( SELECT TRY_CAST(value AS TINYINT) FROM STRING_SPLIT(@Status, ',') WHERE TRY_CAST(value AS TINYINT) IS NOT NULL ); END;

查询状态，Status=0,1,2,3 有80%的数据，Status=4有20%的数据，这时同一个执行计划就不适用所有参数

方案一：重新编译

在最后加入OPTION (RECOMPILE); 让其每次执行SQL会重新编译执行计划

正常情况下，SQL Server会把执行计划缓存起来：

第一次执行：编译计划 --> 执行 --> 缓存计划
第二次执行：复用上次计划

加入OPTION (RECOMPILE);后：

每次执行：重新根据当前参数编译计划 --> 执行

如果第一次执行：Status=4 ，会生成一个适合小数据量的计划

之后执行Status=0,1,2,3，却复用这个小数据量的计划，可能就很慢

加入OPTION (RECOMPILE);后让其每次执行重新编译计划，以上这种情况就会消除

CREATE OR ALTER PROC dbo.GetOrderByStatus @Status VARCHAR(50) AS BEGIN SELECT OrderId, UserId, Status, OrderAmount FROM dbo.Orders WHERE Status IN ( SELECT TRY_CAST(value AS TINYINT) FROM STRING_SPLIT(@Status, ',') WHERE TRY_CAST(value AS TINYINT) IS NOT NULL ) -- 每次执行都会重新编译 OPTION (RECOMPILE); END;

方案二：指定优化参数

可以指定参数进行，使用OPTION (OPTIMIZE FOR (@Status = '0,1,2,3,4'))，它的作用是参数嗅探，让执行计划更稳定，之后每次运行都会按照@Status = '0,1,2,3,4'的计划去执行

CREATE OR ALTER PROC dbo.GetOrderByStatus @Status VARCHAR(50) AS BEGIN SELECT OrderId, UserId, Status, OrderAmount FROM dbo.Orders WHERE Status IN ( SELECT TRY_CAST(value AS TINYINT) FROM STRING_SPLIT(@Status, ',') WHERE TRY_CAST(value AS TINYINT) IS NOT NULL ) -- 每次执行都会按照Status = '0,1,2,3,4'的编译计划取运行 OPTION (OPTIMIZE FOR (@Status = '0,1,2,3,4')) END;

方案三：动态SQL

动态SQL作用是让SQL条件更灵活，让不同参数生成不同的SQL计划，能改善参数嗅探

CREATE OR ALTER PROC dbo.GetOrderByStatus @Status VARCHAR(50) AS BEGIN SET NOCOUNT ON; DECLARE @sql NVARCHAR(MAX) = N' SELECT OrderId, UserId, Status, OrderAmount FROM dbo.Orders WHERE Status IN ( SELECT TRY_CAST(value AS TINYINT) FROM STRING_SPLIT(@Status, '','') WHERE TRY_CAST(value AS TINYINT) IS NOT NULL );'; EXEC sp_executesql @sql, N'@Status VARCHAR(50)', @Status = @Status; END;

对比三种方案

优点	缺点	适用场景
重新编译	1、计划更贴合当前参数 2、处理参数差异大的查询很有效	1、每次都编译，会增加CPU 2、高频接口慎用	1、报表查询 2、复杂查询 3、参数差异大 4、执行频率不高
指定优化参数	1、执行计划稳定 2、避免第一次参数影响后续执行	1、实际参数和指定参数差异大，不适用	1、大多数请求都是一个值 2、不想每次重新编译 3、希望计划稳定
动态SQL	1、适合多条件查询 2、避免可选参数导致低效 3、不同查询可以生成不用的计划	1、拼接不当会有SQL注入风险 2、计划缓存会变多 3、调试不如静态SQL直观	多条件搜索

十四、分批更新和删除

一次更新或删除几百万行，会带来

大事务
大量日志
长时间锁表或锁页
阻塞其他业务

先创建一个备份表

SELECT * INTO dbo.Orders_Bak FROM dbo.Orders;

全表删除

delet from dbo.Orders 记录本次执行结果 执行耗时：5614ms CPU 时间：5250ms Orders 表 logical reads：3238038

恢复数据

-- 因为有自增列，需要开启允许手动插入 SET IDENTITY_INSERT dbo.Orders ON; INSERT INTO dbo.Orders ( OrderId, UserId, Status, OrderAmount, CreateTime ) SELECT OrderId, UserId, Status, OrderAmount, CreateTime FROM dbo.Orders_Bak; SET IDENTITY_INSERT dbo.Orders OFF;

分批次删除，分5000行

while 1 = 1 begin delete top(5000) from dbo.Orders; if @@ROWCOUNT = 0 break; end; 记录本次执行结果 每次平均执行耗时：91ms 每次平均CPU 时间：87ms 总执行耗时：18234 ms 总cpu时间：17374ms Orders 表 logical reads：3214112

分批次删除，分10000行

while 1 = 1 begin delete top (10000) from dbo.Orders; if @@ROWCOUNT = 0 break; end; 每次平均执行耗时：207ms 每次平均CPU 时间：184ms 总执行耗时：20657 ms 总cpu时间：18407ms Orders 表 logical reads：9071443

分批次后耗时会增加，cpu时间会增加，逻辑查询会增加，是因为每次都会查询，虽然时间上涨，但是分批次处理，每次处理的时间会减少，可大大减少风险

十五、避免游标和逐行处理

SQL Server擅长集合操作，不擅长一行一行处理

游标可以理解成：把查询结果一行一行拿出来处理

游标示例：

-- 声明一个变量，后面游标每取一行订单就放在这个变量中 declare @OrderId bigint; --声明一个游标cur,取游标的数据来源 --那么游标cur结果是 --1001 --1002 --1003 --... declare cur cursor for select OrderId from dbo.Orders where Status = 0; -- 打开游标，从游标cur取下一行数据放进@OrderId中 open cur; fetch next from cur into @OrderId; -- 开始循环，@@FETCH_STATUS表示上一次fetch是否成功 -- 常见值 -- 0：取值成功 -- -1：取数据失败或没有下一行 -- -2：取到的行不存在 WHILE @@FETCH_STATUS = 0 begin update dbo.Orders set Status = 1 where OrderId = @OrderId; -- 再从游标取下一行OrderId fetch next from cur into @OrderId; end; -- 关闭游标并释放资源 close cur; deallocate cur; 每次平均执行耗时：0ms 每次平均CPU 时间：0ms 总执行耗时：5472ms 总cpu时间：44841ms Orders 表 logical reads：1408928

这里注意恢复数据，先前已经备份了order表数据，请先进行恢复

优化写法，这个表的数据有10万，可以用分批更新的方法

WHILE 1 = 1 begin update top (5000) dbo.Orders set Status = 1 where Status = 0; if @@ROWCOUNT = 0 BREAK; END; 每次平均执行耗时：283ms 每次平均CPU 时间：275ms 总执行耗时：11594ms 总cpu时间：11279ms Orders 表 logical reads：109561

一行一行执行更新，一行一次日志，一行一次锁操作，一行一次执行开销，会浪费很多资源

FAQ：select、update、delete、insert分别是什么锁
锁类型：更新锁（U Lock）、排他锁（X Lock）、共享锁（S Lock）
select：共享锁，正常update一行，select会等待；正在select一行，update会等待。
update：排他锁、更新锁，先找要更新的行，加排他锁，真正修改适时，加更新锁
delete：排他锁，找到删除的行，加排他锁
insert：排他锁防止别人同时修改同一行或相关索引结构

十六、事务范围要小

事务越大，锁持有时间越长，越容易阻塞别人

事务里只放必须包怎一致性的写操作

示例差写法：

先创建一个orderlog表

create table dbo.OrderLog ( OrderId bigint, Content NVARCHAR(200) );

-- 开启事务 begin tran; select * from dbo.Orders where OrderId = 10011; update dbo.Orders set Status = 1 where OrderId = 10011; insert into dbo.OrderLog (OrderId,Content) values (10011,N'订单状态变更'); -- 提交事务 commit;

优化写法：

select * from dbo.Orders where OrderId = 10011; -- 开启事务 begin tran; update dbo.Orders set Status = 1 where OrderId = 10011; insert into dbo.OrderLog (OrderId,Content) values (10011,N'订单状态变更'); -- 提交事务 commit;

把无关select放在事务外，是保证事务一致性写操作原则

十七、锁等待和阻塞

如果SQL本身逻辑读不高，但执行很慢，可能不是查询问题，而是被锁住了

1、先开启一个SSMS查询窗口1

begin tran; update dbo.Orders set Status = 4 where OrderId = 10012; --注意：这里先不要提交事务 --commit;

这时窗口1已经更新这行数据，但事务没提交，它会持有这行的排他锁

2、在开启一个查询窗口2

SET STATISTICS IO ON; SET STATISTICS TIME ON; UPDATE dbo.Orders SET Status = 3 WHERE OrderId = 10012;

这条SQL理论上只更新一行，逻辑读取不高，但是它会一直等待窗口1释放锁

3、再开启一个查询窗口3：查看阻塞

SELECT session_id, blocking_session_id, wait_type, wait_time, wait_resource FROM sys.dm_exec_requests WHERE blocking_session_id <> 0;

结果

这里每个字段的意思：

session_id：被阻塞的会话
blocking_session_id：阻塞它的会话
wait_type：等待锁
wait_tiem：已经等待的时间
wait_resource：正在等待哪个锁资源

这里的LCK_M_X是等待排他锁

之后在窗口1提交事务

窗口2会立即执行完毕，查看执行记录，会发现执行时间很长

执行耗时：461669ms cpu时间：16ms Orders 表 logical reads：6

逻辑读不高但执行慢，这是可以查阻塞/锁等待

十八、统计信息和索引维护

优化器依赖统计信息估算行数，统计信息过旧时，会导致执行计划错误。

SQL Server在执行SQL前，会先估算每个条件大概能查出多少行数据；这个估算依赖于统计信息，如果统计信息不准，会影响执行计划。

更新统计信息

UPDATE STATISTICS dbo.Orders;

全库更新：更新库中所有的统计信息

EXEC sp_updatestats;

统计信息是SQL Server用来估算行数的依据，比如

数据分布是否均匀 最大值、最小值 每个范围大概有多少行 这个字段有多少不用值

查看索引碎片

SELECT OBJECT_NAME(object_id) AS TableName, index_id, avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats( DB_ID(), NULL, NULL, NULL, 'LIMITED' ) WHERE avg_fragmentation_in_percent > 10;

查看当前数据库中索引碎片大于10%的索引，可以理解为索引页顺序乱不乱

如果碎片高，范围查询，扫描、排序可能会变慢

5% 以下：通常不用管 10% - 30%：可以考虑重组 30% 以上：可以考虑重建

重组索引:将索引页稍微整理顺一点

ALTER INDEX IX_Orders_UserId ON dbo.Orders REORGANIZE;

重建索引：将索引重新创建一遍

ALTER INDEX IX_Orders_UserId ON dbo.Orders REBUILD;

注意：重建索引会消耗资源，生产环境要安排维护时间。

十九、总结

先测试再优化
着重看哪些表的逻辑读取很多，再看用了什么索引
索引不是越多越好，能少逻辑读取才好

优化关键：减少逻辑读取

SQL Server存储过程优化