針對 SQL Server 中窄型和寬型方案的 UPDATE 效能問題進行疑難解答
適用于：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
執行下列腳本，以建立具有 41,501 個數據列的數據表
mytable1、數據行上的c1一個叢集索引，以及其餘數據行上的五個非叢集索引。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個查詢選取寬計劃。 但是在ic3計劃中看不到數據行) 上的c3索引 (。 發生此問題的原因是不符合第二個準則 - 分葉頁面索引大小與設定最大伺服器記憶體相較。資料列 和的數據
c2c4c4類型為char(30)，而 資料行的數據類型為c3char(20)每個索引ic3數據列的大小都小於其他數據列，因此分葉頁面的數目會小於其他分葉頁面。透過動態管理函式 (DMF)
sys.dm_db_database_page_allocations的協助，您可以計算每個索引的頁面數目。 針對、ic4、 和ic5索引，每個索引ic2都有 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)
下列螢幕快照顯示當分割區配置數據行更新時，會使用寬計劃：
總結
意見反應
即將登場：在 2024 年，我們將逐步淘汰 GitHub 問題作為內容的意見反應機制，並將它取代為新的意見反應系統。 如需詳細資訊，請參閱：https://aka.ms/ContentUserFeedback。
提交並檢視相關的意見反應