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后，在“消息”这里会出现以下内容

1. SQL Server 分析和编译时间：这个阶段主要做以下三件事情，解析SQL语法、检查对象和字是否存在、生成或服用执行计划
2. 行数和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页
3. 执行时间: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

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

四、组合索引顺序

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

一般原则：

1. 等值查询列优先
2. 范围查询列放在等值查询之后
3. 排序列尽量和索引顺序一致
4. 只返回但不筛选的列放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;

对比三种方案

十四、分批更新和删除

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

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

先创建一个备份表

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）

1. select：共享锁，正常update一行，select会等待；正在select一行，update会等待。
2. update：排他锁、更新锁，先找要更新的行，加排他锁，真正修改适时，加更新锁
3. delete：排他锁，找到删除的行，加排他锁
4. 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;

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

十九、总结

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

优化关键：减少逻辑读取