排查SQL Server中狭窄和较宽计划的 UPDATE 性能问题

项目
03/24/2023

适用于： SQL Server

语句 UPDATE 在某些情况下可能更快，而其他情况下则较慢。有许多因素可能会导致这种差异，包括更新的行数和系统上的资源使用情况 (阻塞、CPU、内存或 I/O) 。本文将介绍差异的一个具体原因：SQL Server做出的查询计划选择。

什么是狭义和宽泛计划？

对聚集索引列执行UPDATE语句时，SQL Server不仅会更新聚集索引本身，还会更新所有非聚集索引，因为非聚集索引包含群集索引键。

SQL Server有两种执行更新的选项：

窄计划：执行非聚集索引更新以及聚集索引键更新。这种简单的方法很容易理解：更新聚集索引，然后同时更新所有非聚集索引。 SQL Server将更新一行并移动到下一行，直到完成所有操作。此方法称为窄计划更新或 Per-Row 更新。但是，此操作成本相对较高，因为将更新的非聚集索引数据的顺序可能不是聚集索引数据的顺序。如果更新涉及多个索引页，则当数据位于磁盘上时，可能会发生大量随机 I/O 请求。
宽计划：若要优化性能并减少随机 I/O，SQL Server可以选择宽计划。它不会同时执行非聚集索引更新和聚集索引更新。相反，它会先对内存中的所有非聚集索引数据进行排序，然后按该顺序更新所有索引。此方法称为宽计划 (也称为 Per-Index 更新) 。

下面是窄计划和宽计划的屏幕截图：

SQL Server何时选择宽计划？

SQL Server必须满足两个条件才能选择一个广泛的计划：

受影响的行数大于 250。
非聚集索引的叶级别大小 (索引页计数 * 8 KB) 至少为最大服务器内存设置的 1/1000。

狭义和宽泛计划的工作原理是什么？

若要了解窄计划和宽计划的工作原理，请在以下环境中执行以下步骤：

SQL Server 2019 CU11
最大服务器内存 = 1,500 MB

运行以下脚本以创建一个表，该表 mytable1 分别包含 41,501 行、列 c1上的一个聚集索引和其余列上的 5 个非聚集索引。

CREATE TABLE mytable1(c1 INT,c2 CHAR(30),c3 CHAR(20),c4 CHAR(30),c5 CHAR(30))
GO
WITH cte
AS
(
  SELECT ROW_NUMBER() OVER(ORDER BY c1.object_id) id FROM sys.columns CROSS JOIN sys.columns c1
)
INSERT mytable1
SELECT TOP 41000 id,REPLICATE('a',30),REPLICATE('a',20),REPLICATE('a',30),REPLICATE('a',30) 
FROM cte
GO

INSERT mytable1
SELECT TOP 250 50000,c2,c3,c4,c5 
FROM mytable1
GO

INSERT mytable1
SELECT TOP 251 50001,c2,c3,c4,c5 
FROM mytable1
GO

CREATE CLUSTERED INDEX ic1 ON mytable1(c1)
CREATE INDEX ic2 ON mytable1(c2)
CREATE INDEX ic3 ON mytable1(c3)
CREATE INDEX ic4 ON mytable1(c4)
CREATE INDEX ic5 ON mytable1(c5)

运行以下三个 T-SQL UPDATE 语句，并比较查询计划：
- UPDATE mytable1 SET c1=c1 WHERE c1=1 OPTION(RECOMPILE) - 更新了一行
- UPDATE mytable1 SET c1=c1 WHERE c1=50000 OPTION(RECOMPILE) - 更新了 250 行。
- UPDATE mytable1 SET c1=c1 WHERE c1=50001 OPTION(RECOMPILE) - 更新了 251 行。
根据第一个条件检查结果， (受影响行数的阈值为 250) 。

以下屏幕截图显示了基于第一个条件的结果：

与预期一样，查询优化器为前两个查询选择一个窄计划，因为受影响的行数小于 250。将宽计划用于第三个查询，因为受影响的行计数为 251，大于 250。
根据第二个条件检查结果， (叶索引大小的内存至少为最大服务器内存设置) 的 1/1000。

以下屏幕截图显示了基于第二个条件的结果：

为第三 UPDATE 个查询选择了一个宽计划。但计划中未显示列 c3) 的索引 ic3 (。出现此问题的原因是，不符合第二个条件 - 与设置最大服务器内存相比的叶页索引大小。

列 c2c4c4 和的数据类型为 char(30)，而列c3的数据类型为。char(20) 每一行索引 ic3 的大小都小于其他索引行，因此叶页数小于其他页数。

借助动态管理功能 (DMF) sys.dm_db_database_page_allocations，可以计算每个索引的页数。对于索引 ic2、 ic4和 ic5，每个索引有 214 页，其中 209 个是叶页， (结果) 略有不同。叶页消耗的内存为 209 x 8 = 1,672 KB。因此，该比率为 1672/ (1500 x 1024) = 0.00108854101，大于 1/1000。但是， ic3 只有 161 页;其中 159 页是叶页。比率为 159 x 8/ (1500 x 1024) = 0.000828125，小于 1/1000 (0.001) 。

如果插入更多行或减少最大服务器内存以满足条件，计划将更改。若要使索引叶级别大小大于 1/1000，可以通过运行以下命令将最大服务器内存设置降低到 1,200：
```
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'max server memory', 1200;
GO
RECONFIGURE
GO
UPDATE mytable1 SET c1=c1 WHERE c1=50001 OPTION(RECOMPILE) --251 rows are updated.
```
在这种情况下，159 x 8/ (1200 x 1024) = 0.00103515625 > 1/1000。此更改后， ic3 会显示在计划中。

有关的详细信息 show advanced options，请参阅使用 Transact-SQL。

以下屏幕截图显示，当达到内存阈值时，宽计划将使用所有索引：

宽计划是否比窄计划快？

答案是，这取决于数据和索引页是否缓存在缓冲池中。

数据缓存在缓冲池中

如果数据已在缓冲池中，则与窄计划相比，具有宽计划的查询不一定提供额外的性能优势，因为宽计划旨在提高物理读取 (I/O 性能，而不是) 逻辑读取。

若要在数据位于缓冲池中时测试宽计划是否比窄计划快，请在以下环境中执行以下步骤：

SQL Server 2019 CU11
最大服务器内存：30,000 MB
数据大小为 64 MB，而索引大小约为 127 MB。
数据库文件位于两个不同的物理磁盘上：
- I：\sql19\dbWideplan.mdf
- H：\sql19\dbWideplan.ldf

通过运行以下命令创建另一个表 mytable2：

CREATE TABLE mytable2(C1 INT,C2 INT,C3 INT,C4 INT,C5 INT)
GO
CREATE CLUSTERED INDEX IC1 ON mytable2(C1)
CREATE INDEX IC2 ON mytable2(C2)
CREATE INDEX IC3 ON mytable2(C3)
CREATE INDEX IC4 ON mytable2(C4)
CREATE INDEX IC5 ON mytable2(C5)
GO
DECLARE @N INT=1
WHILE @N<1000000
BEGIN
  DECLARE @N1 INT=RAND()*4500
  DECLARE @N2 INT=RAND()*100000
  DECLARE @N3 INT=RAND()*100000
  DECLARE @N4 INT=RAND()*100000
  DECLARE @N5 INT=RAND()*100000
  INSERT mytable2 VALUES(@N1,@N2,@N3,@N4,@N5)
  SET @N+=1
END
GO
UPDATE STATISTICS mytable2 WITH FULLSCAN

执行以下两个查询以比较查询计划：
```
update mytable2 set c1=c1 where c2<260 option(querytraceon 8790) --trace flag 8790 will force Wide plan
update mytable2 set c1=c1 where c2<260 option(querytraceon 2338) --trace flag 2338 will force Narrow plan
```
有关详细信息，请参阅跟踪标志 8790 和跟踪标志 2338。

具有宽计划的查询需要 0.136 秒，而具有窄计划的查询只需 0.112 秒。这两个持续时间非常接近，Per-Index 更新 (宽计划) 不太有利，因为执行语句之前 UPDATE 数据已经在缓冲区中。

以下屏幕截图显示了在缓冲池中缓存数据时的宽计划和窄计划：

数据不会缓存在缓冲池中

若要在数据不在缓冲池中时测试宽计划是否比窄计划快，请运行以下查询：

注意

执行测试时，请确保你的是 SQL Server 中唯一的工作负载，并且磁盘专用于SQL Server。

CHECKPOINT
GO
DBCC DROPCLEANBUFFERS
GO
WAITFOR DELAY '00:00:02' --wait for 1~2 seconds
UPDATE mytable2 SET c1=c1 WHERE c2 < 260 OPTION (QUERYTRACEON 8790) --force Wide plan
CHECKPOINT 
GO
DBCC DROPCLEANBUFFERS
GO 
WAITFOR DELAY '00:00:02' --wait for 1~2 SECONDS
UPDATE mytable2 SET c1=c1 WHERE c2 < 260 OPTION (QUERYTRACEON 2338) --force Narrow plan

具有宽计划的查询需要 3.554 秒，而具有窄计划的查询需要 6.701 秒。这次，宽计划查询的运行速度更快。

以下屏幕截图显示了在缓冲池中未缓存数据时的宽计划：

以下屏幕截图显示了在缓冲池中未缓存数据时的窄计划：

数据未缓存在缓冲池中的窄计划的屏幕截图。

当数据不在缓冲区中时，宽计划查询是否始终比窄查询计划快？

答案是“并不总是”。若要在数据不在缓冲区中时测试宽计划查询是否始终比窄查询计划更快，请执行以下步骤：

通过运行以下命令创建另一个表 mytable2：
```
SELECT c1,c1 AS c2,c1 AS C3,c1 AS c4,c1 AS C5 INTO mytable3 FROM mytable2
GO
CREATE CLUSTERED INDEX IC1 ON mytable3(C1)
CREATE INDEX IC2 ON mytable3(C2)
CREATE INDEX IC3 ON mytable3(C3)
CREATE INDEX IC4 ON mytable3(C4)
CREATE INDEX IC5 ON mytable3(C5)
GO
```
与 mytable3 相同 mytable2，但数据除外。 mytable3 具有具有相同值的所有五列，这使得非聚集索引的顺序遵循聚集索引的顺序。对数据进行这种排序将最大程度地减少宽计划的优势。

执行以下命令以比较查询计划：

CHECKPOINT 
GO
DBCC DROPCLEANBUFFERS
go
UPDATE mytable3 SET c1=c1 WHERE c2<12 OPTION(QUERYTRACEON 8790) --tf 8790 will force Wide plan

CHECKPOINT 
GO
DBCC DROPCLEANBUFFERS
GO
UPDATE mytable3 SET c1=c1 WHERE c2<12 OPTION(QUERYTRACEON 2338) --tf 2338 will force Narrow plan

这两个查询的持续时间都显著缩短！宽计划需要 0.304 秒，这一次比窄计划慢一点。

以下屏幕截图显示了在使用宽和窄时的性能比较：

应用广泛计划的方案

下面是还应用广泛计划的其他方案：

聚集索引列具有唯一键或主键，并且会更新多个行

下面是重现方案的示例：

CREATE TABLE mytable4(c1 INT primary key,c2 INT,c3 INT,c4 INT)
GO
CREATE INDEX ic2 ON mytable4(c2)
CREATE INDEX ic3 ON mytable4(c3)
CREATE INDEX ic4 ON mytable4(c4)
GO
INSERT mytable4 VALUES(0,0,0,0)
INSERT mytable4 VALUES(1,1,1,1)

以下屏幕截图显示，当群集索引具有唯一键时，将使用宽计划：

有关更多详细信息，请参阅维护唯一索引。

在分区方案中指定群集索引列

下面是重现方案的示例：

CREATE TABLE mytable5(c1 INT,c2 INT,c3 INT,c4 INT)
GO
IF EXISTS (SELECT * FROM sys.partition_schemes WHERE name='PS1')
    DROP PARTITION SCHEME PS1
GO
IF EXISTS (SELECT * FROM sys.partition_functions WHERE name='PF1')
    DROP PARTITION FUNCTION PF1
GO
CREATE PARTITION FUNCTION PF1(INT) AS 
  RANGE right FOR VALUES 
  (2000)   
GO
CREATE PARTITION SCHEME PS1 AS 
  PARTITION PF1 all TO 
  ([PRIMARY]) 
GO 
CREATE CLUSTERED INDEX c1 ON mytable5(c1) ON PS1(c1)
CREATE INDEX c2 ON mytable5(c2)
CREATE INDEX c3 ON mytable5(c3)
CREATE INDEX c4 ON mytable5(c4)
GO
UPDATE mytable5 SET c1=c1 WHERE c1=1

以下屏幕截图显示，当分区方案中存在聚集列时，将使用宽计划：

聚集索引列不是分区方案的一部分，分区方案列已更新

下面是重现方案的示例：

CREATE TABLE mytable6(c1 INT,c2 INT,c3 INT,c4 INT)
GO
IF EXISTS (SELECT * FROM sys.partition_schemes WHERE name='PS2')
    DROP PARTITION SCHEME PS2
GO
IF EXISTS (SELECT * FROM sys.partition_functions WHERE name='PF2')
    DROP PARTITION FUNCTION PF2
GO
CREATE PARTITION FUNCTION PF2(int) AS 
  RANGE right FOR VALUES 
  (2000)   
GO
CREATE PARTITION SCHEME PS2 AS 
  PARTITION PF2 all TO 
  ([PRIMARY]) 
GO 
CREATE CLUSTERED INDEX c1 ON mytable6(c1) ON PS2(c2) --on c2 column
CREATE INDEX c3 ON mytable6(c3)
CREATE INDEX c4 ON mytable6(c4)

以下屏幕截图显示更新分区方案列时使用宽计划：

总结

当同时满足以下条件时，SQL Server选择宽计划更新：
- 受影响的行数大于 250。
- 叶索引的内存至少为最大服务器内存设置的 1/1000。
宽计划会以消耗额外内存为代价来提升性能。
如果未使用预期的查询计划，则可能是由于过时的统计信息 (未报告正确的数据大小) 、最大服务器内存设置或其他不相关的问题（如参数敏感计划）。
使用宽计划的语句的 UPDATE 持续时间取决于多种因素，在某些情况下，它可能需要比窄计划更长的时间。
跟踪标志 8790 将强制实施宽计划：跟踪标志 2338 将强制实施窄计划。

通过

排查SQL Server中狭窄和较宽计划的 UPDATE 性能问题

什么是狭义和宽泛计划？

SQL Server何时选择宽计划？

狭义和宽泛计划的工作原理是什么？

宽计划是否比窄计划快？

数据缓存在缓冲池中

数据不会缓存在缓冲池中

当数据不在缓冲区中时，宽计划查询是否始终比窄查询计划快？

应用广泛计划的方案

聚集索引列具有唯一键或主键，并且会更新多个行

在分区方案中指定群集索引列

聚集索引列不是分区方案的一部分，分区方案列已更新

总结

反馈

其他资源