SQL Server の狭いプランと幅の広いプランに関する UPDATE のパフォーマンスに関する問題のトラブルシューティング

[アーティクル]
12/14/2024

適用対象: SQL Server

UPDATEステートメントは、場合によっては高速で、他の場合は遅くなる場合があります。更新された行の数やシステム上のリソース使用量 (ブロック、CPU、メモリ、または I/O) など、このような差異につながる要因は多数あります。この記事では、SQL Server によって作成されたクエリプランの選択という、差異の具体的な理由の 1 つについて説明します。

狭くて広いプランとは

クラスター化インデックス列に対して UPDATE ステートメントを実行すると、クラスター化インデックス自体だけでなく、クラスター化されていないインデックスもすべて更新されます。クラスター化されていないインデックスにはクラスター化インデックスキーが含まれているためです。

SQL Server には、更新を行う次の 2 つのオプションがあります。

狭いプラン: クラスター化されていないインデックスの更新と、クラスター化インデックスキーの更新を行います。この簡単なアプローチは簡単に理解できます。クラスター化インデックスを更新し、クラスター化されていないすべてのインデックスを同時に更新します。 SQL Server は 1 つの行を更新し、すべてが完了するまで次の行に移動します。この方法は、狭いプランの更新または行ごとの更新と呼ばれます。ただし、更新される非クラスター化インデックスデータの順序がクラスター化インデックスデータの順序にならない可能性があるため、この操作は比較的コストがかかります。更新に多数のインデックスページが含まれている場合、データがディスク上にある場合、多数のランダム I/O 要求が発生する可能性があります。
ワイドプラン: パフォーマンスを最適化し、ランダムな I/O を減らすために、SQL Server では幅広いプランを選択できます。クラスター化インデックス以外の更新は、クラスター化インデックスの更新と共に行われません。代わりに、最初にメモリ内のすべての非クラスター化インデックスデータを並べ替え、その順序ですべてのインデックスを更新します。このアプローチは、ワイドプラン (インデックスごとの更新とも呼ばれます) と呼ばれます。

幅の狭いプランとワイドプランのスクリーンショットを次に示します。

SQL Server がワイドプランを選択するタイミング

SQL Server でワイドプランを選択するには、次の 2 つの条件を満たす必要があります。

影響を受けた行の数が 250 を超える。
非クラスター化インデックスのリーフレベルのサイズ (インデックスページ数 * 8 KB) は、最大サーバーメモリ設定の 1/1000 以上です。

狭いプランとワイドプランはどのように機能しますか?

幅の狭いプランとワイドプランがどのように機能するかを理解するには、次の環境で次の手順に従います。

SQL Server 2019 CU11
最大サーバーメモリ = 1,500 MB

次のスクリプトを実行して、41,501 行、列c1に 1 つのクラスター化インデックス、残りの列にそれぞれ 5 つの非クラスター化インデックスを含むテーブル mytable1を作成します。

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)

次の 3 つの T-SQL UPDATE ステートメントを実行し、クエリプランを比較します。
- UPDATE mytable1 SET c1=c1 WHERE c1=1 OPTION(RECOMPILE) - 1 つの行が更新されます
- UPDATE mytable1 SET c1=c1 WHERE c1=50000 OPTION(RECOMPILE) - 250 行が更新されます。
- UPDATE mytable1 SET c1=c1 WHERE c1=50001 OPTION(RECOMPILE) - 251 行が更新されます。
最初の条件に基づいて結果を調べます (影響を受ける行数のしきい値は 250 です)。

次のスクリーンショットは、最初の条件に基づく結果を示しています。

予想どおり、クエリオプティマイザーは、影響を受けた行の数が 250 未満であるため、最初の 2 つのクエリに対して狭いプランを選択します。影響を受ける行数は 251 (250 を超える) であるため、3 番目のクエリにはワイドプランが使用されます。
2 番目の条件に基づいて結果を調べます (リーフインデックスサイズのメモリは、最大サーバーメモリ設定の 1/1000 以上です)。

次のスクリーンショットは、2 番目の条件に基づく結果を示しています。

3 番目の UPDATE クエリにはワイドプランが選択されます。ただし、(列c3の) インデックスic3はプランに表示されません。この問題は、設定の最大サーバーメモリと比較して、2 番目の条件 (リーフページのインデックスサイズ) が満たされていないために発生します。

列 c2、 c4 、および c4 のデータ型は 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) 未満です。

追加の行を挿入するか、条件を満たすために max サーバーメモリを減らすと、プランが変更されます。インデックスのリーフレベルのサイズを 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 です。
データベースファイルは、次の 2 つの異なる物理ディスク上にあります。
- 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

次の 2 つのクエリを実行して、クエリプランを比較します。
```
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 秒しかかかりません。 2 つの期間は非常に近く、インデックスごとの更新 (ワイドプラン) は、 UPDATE ステートメントが実行される前に既にバッファー内にあるため、あまり有益ではありません。

次のスクリーンショットは、バッファープールにデータがキャッシュされている場合の幅の広いプランと狭いプランを示しています。

データがバッファープールにキャッシュされない

データがバッファープールにない場合に幅の広いプランが狭いプランよりも高速かどうかをテストするには、次のクエリを実行します。

Note

テストを行うときは、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 には、同じ値を持つ 5 つの列がすべて含まれています。これにより、非クラスター化インデックスの順序はクラスター化インデックスの順序に従います。このデータの並べ替えにより、ワイドプランの利点が最小限に抑えられます。
次のコマンドを実行して、クエリプランを比較します。
```
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 がワイドプランを選択するタイミング

狭いプランとワイドプランはどのように機能しますか?

幅の広いプランは狭いプランよりも高速ですか?

データがバッファープールにキャッシュされる

データがバッファープールにキャッシュされない

データがバッファー内にない場合、ワイドプランクエリは狭いクエリプランよりも常に高速ですか?

幅広いプランが適用されるシナリオ

クラスター化インデックス列に一意キーまたは主キーがあり、複数の行が更新されます

クラスターインデックス列がパーティション構成で指定されている

クラスター化インデックス列はパーティション構成の一部ではなく、パーティション構成列が更新されます

まとめ

フィードバック

その他のリソース

次の方法で共有

SQL Server の狭いプランと幅の広いプランに関する UPDATE のパフォーマンスに関する問題のトラブルシューティング

狭くて広いプランとは

SQL Server がワイド プランを選択するタイミング

狭いプランとワイドプランはどのように機能しますか?

幅の広いプランは狭いプランよりも高速ですか?

データがバッファー プールにキャッシュされる

データがバッファー プールにキャッシュされない

データがバッファー内にない場合、ワイド プラン クエリは狭いクエリ プランよりも常に高速ですか?

幅広いプランが適用されるシナリオ

クラスター化インデックス列に一意キーまたは主キーがあり、複数の行が更新されます

クラスター インデックス列がパーティション構成で指定されている

クラスター化インデックス列はパーティション構成の一部ではなく、パーティション構成列が更新されます

まとめ

フィードバック

その他のリソース

SQL Server がワイドプランを選択するタイミング

データがバッファープールにキャッシュされる

データがバッファープールにキャッシュされない

データがバッファー内にない場合、ワイドプランクエリは狭いクエリプランよりも常に高速ですか?

クラスターインデックス列がパーティション構成で指定されている