SQL Serverの狭いプランとワイド プランに関する UPDATE パフォーマンスの問題のトラブルシューティング
適用対象: SQL Server
UPDATE
ステートメントは、場合によってはより速く、他の場合は遅くなる場合があります。 更新された行の数やシステム上のリソース使用量 (ブロック、CPU、メモリ、または I/O) など、このような差異につながる可能性のある多くの要因があります。 この記事では、分散の 1 つの具体的な理由として、SQL Serverによって行われたクエリ プランの選択について説明します。
狭くて広いプランとは
クラスター化インデックス列に対してステートメントをUPDATE
実行すると、SQL Server クラスター化インデックス自体だけでなく、クラスター化されていないインデックスもすべて更新されます。クラスター化されていないインデックスにはクラスター化インデックス キーが含まれているためです。
SQL Serverには、更新を実行するための 2 つのオプションがあります。
狭いプラン: クラスター化されていないインデックスの更新とクラスター化インデックス キーの更新を行います。 この簡単なアプローチは簡単に理解できます。クラスター化インデックスを更新し、クラスター化されていないすべてのインデックスを同時に更新します。 SQL Serverは 1 つの行を更新し、すべてが完了するまで次の行に移動します。 この方法は、狭いプランの更新または Per-Row 更新と呼ばれます。 ただし、更新される非クラスター化インデックス データの順序がクラスター化インデックス データの順序ではない可能性があるため、この操作は比較的コストがかかります。 更新に多数のインデックス ページが含まれている場合は、データがディスク上にあるときに、多数のランダムな I/O 要求が発生する可能性があります。
ワイド プラン: パフォーマンスを最適化し、ランダムな I/O を減らすために、SQL Server幅広いプランを選択できます。 クラスター化されていないインデックスの更新とクラスター化インデックスの更新は一緒に実行されません。 代わりに、最初にメモリ内のすべての非クラスター化インデックス データを並べ替え、その順序ですべてのインデックスを更新します。 このアプローチは、ワイド プラン (Per-Index 更新とも呼ばれます) と呼ばれます。
狭くて広いプランのスクリーンショットを次に示します。
SQL Serverワイド プランはいつ選ばれますか?
SQL Serverが広いプランを選択するには、次の 2 つの条件を満たす必要があります。
- 影響を受けた行の数が 250 を超えています。
- 非クラスター化インデックスのリーフ レベルのサイズ (インデックス ページ数 * 8 KB) は、最大サーバー メモリ設定の少なくとも 1/1000 です。
狭くて広いプランはどのように機能しますか?
幅の狭いプランと幅の広いプランの動作を理解するには、次の環境で次の手順に従います。
- SQL Server 2019 CU11
- 最大サーバー メモリ = 1,500 MB
次のスクリプトを実行して、41,501 行、列に 1 つのクラスター化インデックス、残りの列
c1
に 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) 未満です。より多くの行を挿入するか、条件を満たすために 最大サーバー メモリ を減らすと、プランが変更されます。 インデックスリーフレベルのサイズを 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 つの期間は非常に近く、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
には、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
期間は、いくつかの要因によって異なります。場合によっては、狭いプランよりも時間がかかる場合があります。