最近、SQL Server の Bulk Insert について、調査することが何回かあり、その中で「高速挿入」(Fast Inserts / Fast Load Context) の動作をどこまで見れるか、考えたことがあったので、その覚書を。
詳細な情報としては次の内容を参照してください。
- Possible increased unused disk space when running SCOM 2016 on SQL2016
- SQL Server 2016, Minimal logging and Impact of the Batchsize in bulk load operations
高速挿入について言及されているのは「TF692」の 次の説明 となります。
ヒープまたはクラスター化インデックスへのデータの一括読み込みの間に高速挿入を無効にします。 SQL Server 2016 (13.x) 以降では、新しいページに挿入されるレコードの挿入パフォーマンスを最適化するため、データベースが単純復旧モデルまたは一括ログ復旧モデルのときは、既定で、最小ログ記録を利用して高速挿入が有効になります。 高速挿入では、挿入のパフォーマンスを最適化するため、各一括読み込みバッチは新しいエクステントを取得し、使用可能な空き領域を持つ既存エクステントの割り当ての参照をバイパスします。
高速挿入では、小さいバッチ サイズの一括読み込みにより、オブジェクトによって消費される未使用領域が増加する可能性があるため、各バッチに大きいバッチ サイズを使ってエクステントを完全に埋めることをお勧めします。 バッチ サイズを大きくできない場合は、このトレース フラグを使うと、パフォーマンスは低下しますが、確保される未使用領域を減らすのに役立ちます。
注: このトレース フラグは、SQL Server 2016 (13.x) RTM 以降のビルドに適用されます。
スコープ: グローバル、セッション
Bulk Insert で一括でデータ挿入をする際に、パフォーマンスを向上させるため、既存のエクステントにデータを挿入するのではなく、新規のエクステントを取得してデータの挿入を行うことが「高速挿入」の動作となります。
SQL Server 2016 以降は、高速挿入が自動で有効になっており、トレースフラグ 692 で無効にすることができます。
SQL Server 2019 / SQL Database であれば、いくつかの DMV / DMF を組み合わせることで情報を詳細に確認することができます。
今回は次のようなクエリを使用しています。
select pa.extent_page_id , pa.allocated_page_page_id , pa.is_allocated , pi.page_type_desc , pi.slot_count , pi.reserved_bytes , pi.free_bytes from sys.dm_db_database_page_allocations(DB_ID(), OBJECT_ID('ORDERS3'), 1, 0, 'DETAILED') AS pa OUTER APPLY sys.dm_db_page_info(DB_ID(), 1, pa.allocated_page_page_id , 'DETAILED') AS pi select pa.page_type_desc , SUM(slot_count) AS slot_count , COUNT(*) AS page_count from sys.dm_db_database_page_allocations(DB_ID(), OBJECT_ID('ORDERS3'), 1, 0, 'DETAILED') AS pa OUTER APPLY sys.dm_db_page_info(DB_ID(), 1, pa.allocated_page_page_id , 'DETAILED') AS pi GROUP BY pa.page_type_desc SELECT used_page_count, reserved_page_count,row_count FROM sys.dm_db_partition_stats WHERE object_id = OBJECT_ID('ORDERS3')
ORDERS3 というテーブルに Bulk Insert をしており、その情報を取得しているものとなります。
では、トレースフラグ 692 を有効にして Bulk Insert によるデータ挿入をしてみたいと思います。
今回は、1000 件のデータを取得し、バッチサイズ = 100 に設定しデータ投入を行うということを 10 回繰り返しています。
最初の結果は、「エクステント内のページがどの程度埋まっているか」を表しているものです。
高速挿入を無効にした場合は、既存のエクステントが利用されますのでページは詰まった状態で格納されていますね。
2 , 3 番目のクエリで件数レベルで取得しても空きの領域がなく、ページが詰まった状態で格納されています。
それではトレースフラグが設定されていない、デフォルトの状態ではどうなるでしょうか?
先ほどとは異なり、エクステント内のページに空きが出ていることが確認できますね。
高速挿入は「新しいエクステント」に対して、データの書き込みを行いますので、
- バッチサイズ
- Bulk Insert を繰り返した回数
によっては、このようにエクステント内にかなりの空きが出てしまいます。
件数レベルで取得しても同じですね。
それでは、この状態で、バッチサイズを 100 → 1000 に変えるとどうなるでしょうか。
空きページの数が「660」→「20」に減っており、先ほどと比較して、データの格納効率が高くなっていますね。
空きとなっているページついては、通常の Insert 等の DML を行う場合は利用されますが、そのようなデータ投入を行わない場合は、空き領域のままですので、インデックスの再構築や再構成を行うことで、「エクステント内のすべてのページが未使用となっている状態」を作って解放してあげないと、空きが減らないかと。
高速挿入にかかわらず、Bulk Insert 系の処理については「データがどのように投入されているか」「どのように処理が行われて投入されるか」という点は意識しておくとよいかなと思いましたとさ。