バルク挿入時のSQL Serverインデックスの動作

bulkinsert indexing sqlbulkcopy sql-server

質問

SQL Serverに複数の行を一度に挿入するアプリケーションがあります。

私はSqlBulkCopyクラスまたはinsert into table_name(...) values (...)ステートメントに巨大なinsert into table_name(...) values (...)を生成する自己記述コードを使用します。

私のテーブルにはいくつかのインデックスとクラスタ化されたインデックスがあります。

問題は、それらのインデックスがどのように更新されているかです。挿入する行ごとに挿入しますか?各取引について?

多少奇妙な質問 - このシナリオの一般的な用語は、「一括挿入のインデックス作成の動作」ですか?私はいくつかのキーワードの組み合わせを試してみましたが、何も見つかりませんでした。私が尋ねる理由は、時にはPostgresと仕事をし、その振る舞いも知りたいからです。

私はこのトピックに関する記事を何度も見つけようとしています。

関連する章があるドキュメント、記事、または書籍を私に指摘できれば、それは素晴らしいことです

受け入れられた回答

クエリプランを調べることによって、インデックスの更新方法を確認できます。クラスタ化されていないインデックスのみを持つこのヒープテーブルを検討してください。

CREATE TABLE dbo.BulkInsertTest(
      Column1 int NOT NULL
    , Column2 int NOT NULL
    , Column3 int NOT NULL
    , Column4 int NOT NULL
    , Column5 int NOT NULL
    );
CREATE INDEX BulkInsertTest_Column1 ON dbo.BulkInsertTest(Column1);
CREATE INDEX BulkInsertTest_Column2 ON dbo.BulkInsertTest(Column2);
CREATE INDEX BulkInsertTest_Column3 ON dbo.BulkInsertTest(Column3);
CREATE INDEX BulkInsertTest_Column4 ON dbo.BulkInsertTest(Column4);
CREATE INDEX BulkInsertTest_Column5 ON dbo.BulkInsertTest(Column5);
GO

以下は、シングルトンINSERT実行計画です。

INSERT INTO dbo.BulkInsertTest(Column1, Column2, Column3, Column4, Column5) VALUES
     (1, 2, 3, 4, 5);

実行計画を挿入する

実行プランには表挿入演算子のみが表示されるため、新しい非クラスタ化索引行は、表の挿入操作中に本来挿入されます。シングルトンINSERTステートメントの大きなバッチは、各insertステートメントに対して同じプランを生成します。

行コンストラクタで指定された多数の行を持つ単一のINSERT文で同様の計画が得られます。唯一の違いは、行を出力するConstant Scan演算子が追加されていることです。

INSERT INTO dbo.BulkInsertTest(Column1, Column2, Column3, Column4, Column5) VALUES
     (1, 2, 3, 4, 5)
    ,(1, 2, 3, 4, 5)
    ,(1, 2, 3, 4, 5)
    ,...
    ,(1, 2, 3, 4, 5);

ここに画像の説明を入力

次に、T-SQL BULK INSERTステートメントの実行計画を示します(ダミーの空のファイルをソースとして使用します)。 BULK INSERTを使用すると、SQL Serverはインデックス挿入を最適化する追加のクエリプラン演算子を追加しました。行は表に挿入された後にスプールされ、スプールからの行はソートされ、一括挿入操作として別々に各索引に挿入されました。この方法は、大きな挿入操作のオーバーヘッドを削減します。 INSERT...SELECTクエリについても同様の計画が表示される場合があります。

BULK INSERT dbo.BulkInsertTest
    FROM 'c:\Temp\BulkInsertTest.txt';

BULK INSERT実行計画

私は、 SqlBulkCopy拡張イベントトレースを持つ実際のプランをキャプチャすることによって、T-SQL BULK INSERTと同じ実行プランを生成することを確認しSqlBulkCopy 。以下は私が使ったトレースDDLとPowerShellのスクリプトです。

トレースDDL:

CREATE EVENT SESSION [SqlBulkCopyTest] ON SERVER 
ADD EVENT sqlserver.query_post_execution_showplan(
    ACTION(sqlserver.client_app_name,sqlserver.sql_text)
    WHERE ([sqlserver].[equal_i_sql_unicode_string]([sqlserver].[client_app_name],N'SqlBulkCopyTest') 
        AND [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'insert bulk%') 
        ))
ADD TARGET package0.event_file(SET filename=N'SqlBulkCopyTest');
GO

PowerShellスクリプト:

$connectionString = "Data Source=.;Initial Catalog=YourUserDatabase;Integrated Security=SSPI;Application Name=SqlBulkCopyTest"

$dt = New-Object System.Data.DataTable;
$null = $dt.Columns.Add("Column1", [System.Type]::GetType("System.Int32"))
$null = $dt.Columns.Add("Column2", [System.Type]::GetType("System.Int32"))
$null = $dt.Columns.Add("Column3", [System.Type]::GetType("System.Int32"))
$null = $dt.Columns.Add("Column4", [System.Type]::GetType("System.Int32"))
$null = $dt.Columns.Add("Column5", [System.Type]::GetType("System.Int32"))

$row = $dt.NewRow()
[void]$dt.Rows.Add($row)
$row["Column1"] = 1
$row["Column2"] = 2
$row["Column3"] = 3
$row["Column4"] = 4
$row["Column5"] = 5

$bcp = New-Object System.Data.SqlClient.SqlBulkCopy($connectionString)
$bcp.DestinationTableName = "dbo.BulkInsertTest"
$bcp.WriteToServer($dt)

EDIT

SQL Serverのコストベースのインデックス保守戦略の詳細については、 Microsoft Data Platform MVP Paul Whiteのブログ記事を提供したVladimir Baranov氏の功績によるものです。

EDIT 2

実際の状況は、ヒープではなくクラスター化されたインデックスを持つテーブルであることが、あなたの修正された質問からわかります。計画は、表挿入の代わりにクラスター化インデックス挿入演算子を使用してデータが挿入されることを除いて、上記のヒープの例と似ています。

ORDERヒントは、クラスタ化インデックスを持つテーブルへの一括挿入操作中に指定できます。指定した順序がクラスタードインデックスの順序と一致する場合、SQL Serverは、ヒントごとにデータが既にソートされていると想定しているため、クラスタードインデックス挿入の前にソート演算子を削除できます。残念ながら、 SqlBulkCopyORDERヒントをサポートしていません。


人気のある回答

問題は、それらのインデックスがどのように更新されているかです。挿入する行ごとに挿入しますか?各取引について?

低レベルの観点から、索引は常に行ごとに更新されますが、これは索引の内部データ構造の結果です。 SQL ServerのインデックスはB +ツリーです。一度にB +ツリーインデックス内のいくつかの行を一括更新するアルゴリズムはありません。前の行を挿入して更新する前に、どこに行かれるかを事前に知ることができないため、それらを1つずつ更新する必要があります。

しかし、トランザクションの観点から言えば、インデックスは一度にすべて更新されます。これは、SQL Serverがトランザクションのセマンティクスを実装するためです。 READ COMMITTEDのデフォルトの分離レベルでは、トランザクションがコミットされるまで、一括挿入操作で挿入した行(索引または表の行)を別のトランザクションで見ることができません。つまり、行が一度に挿入されたように見えます。



ライセンスを受けた: CC-BY-SA with attribution
所属していない Stack Overflow
このKBは合法ですか? はい、理由を学ぶ
ライセンスを受けた: CC-BY-SA with attribution
所属していない Stack Overflow
このKBは合法ですか? はい、理由を学ぶ