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

[アーティクル]
03/24/2023

適用対象: 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 期間は、いくつかの要因によって異なります。場合によっては、狭いプランよりも時間がかかる場合があります。
トレースフラグ 8790 は、ワイドプランを強制します。トレースフラグ 2338 は狭いプランを強制します。

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

狭くて広いプランとは

SQL Serverワイドプランはいつ選ばれますか?

狭くて広いプランはどのように機能しますか?

狭いプランよりも広いプランの方が速いですか?

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

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

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

ワイドプランが適用されるシナリオ

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

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

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

まとめ

フィードバック

フィードバック

その他のリソース

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

狭くて広いプランとは

SQL Serverワイド プランはいつ選ばれますか?

狭くて広いプランはどのように機能しますか?

狭いプランよりも広いプランの方が速いですか?

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

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

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

ワイド プランが適用されるシナリオ

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

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

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

まとめ

フィードバック

フィードバック

その他のリソース

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

SQL Serverワイドプランはいつ選ばれますか?

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

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

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

ワイドプランが適用されるシナリオ

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

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