現象
一括読み込み操作を実行すると、データベース内のテーブルの未使用領域の増加が一貫して高くなる場合があります。 sp_spaceused コマンドを実行すると、テーブル内の未使用の領域が予約領域の大部分を占めている (テーブルに割り当てられた領域全体) 場合があります。
EXEC sp_spaceused 'Sales.Customer'
次に例を示します。
| テーブル | 予約済み (KB) | データ (KB) | インデックス (KB) | 未使用 (KB) |
|---|---|---|---|---|
| Sales.Customer | 800,000 | 50,000 | 10,000 | 740,000 |
原因
バッチ サイズが小さい一括読み込み操作を使用する場合、テーブルは、ほとんど使用されていないページ 拡張 を割り当てる可能性があります。
最小限のログ記録を使用する一括読み込み操作を実行すると、データが事前に並べ替えられたり、順番に読み込まれたりする場合に、インデックスのデータ読み込み操作のパフォーマンスを向上させることができます。 ただし、これらの操作で使用するバッチ サイズ (BULK INSERT でBATCHSIZEbcp ユーティリティの-bオプション) は、一方でパフォーマンスを向上させ、別の操作で効率的な領域使用を実現する上で重要な役割を果たします。 最小ログ モードでは、各一括読み込みバッチは、1 つ以上の新しいエクステントを割り当てるときに、使用可能な空き領域の検索をバイパスします。 SQL Server は、挿入のパフォーマンスを最適化するために、このキャッシュ参照をスキップします。 既存のエクステントに空き領域を探す代わりに、新しいエクステントを直接作成します。 そのため、小さなバッチ サイズ (バッチあたり 10 行など) を使用する場合、SQL Server では、10 レコードのバッチごとに新しい 64 KB のエクステントが予約されます。 これはほとんどの行サイズで無駄になります (一部の行は 1 ページに収まるほど幅が広く、その場合は 10 個のレコードが適切な場合があります)。 エクステント内の残りのページは未使用ですが、オブジェクト用に予約されています。 そのため、高速な負荷の最適化と小さなバッチ サイズの組み合わせにより、非効率的な領域の使用が発生します。
MSSQL Tiger Team のブログ サイト 次の表 は、この動作を示すいくつかの経験的証拠を示しています。
| バッチ サイズ | 予約済み (KB) | データ (KB) | インデックス サイズ (KB) | 未使用 (KB) | 未使用の割合 (%) |
|---|---|---|---|---|---|
| 10 | 6,472 | 808 | 8 | 5,656 | 87 |
| 100 | 1,352 | 168 | 8 | 1,176 | 86 |
| 1,000 | 264 | 128 | 8 | 128 | 49 |
解決方法
この問題を解決するには、次のガイドラインを検討してください。
挿入の数が少ないということは、一括読み込み操作がないことを意味します
挿入する行の数が比較的少ない場合、これらは "一括" 挿入ではありません。 バッチ サイズが小さい場合は、 最小ログの最適化で一括読み込み操作ではなく、完全にログに記録された通常の INSERT ステートメントを使用することをお勧めします。
一括読み込み操作のバッチ サイズ値の設定
一括読み込み操作の場合は、エクステントのサイズの倍数 (64 KB) で、平均行サイズに基づくバッチ サイズを選択します。 このようなバッチ サイズ値を使用すると、行はエクステント内の領域を効率的に埋めることができます。 たとえば、平均行サイズが 25 バイトの場合は、1 行あたり 64 KB を 25 バイトで除算して、バッチ サイズにパックできる行の数を決定します。 この場合、64 KB = 65,536 バイト/ 25 バイト/行 = 2,620 行。 そのため、この数値を囲むバッチ サイズを選択して、各データ ページのヘッダーにスペースを入れることもできます。 これは、たとえば 2,500 ~ 2,700 の範囲のバッチ サイズを使用してテストし、領域の使用状況を確認できます。 テーブル内の平均行サイズを調べるには、次のクエリを使用します。 ヒープ (クラスター化インデックスがないテーブル) の場合は、index_ID パラメーター (3 番目のパラメーター) に 0 を使用します。 クラスター化インデックスを持つテーブルの場合は、次の例に示すように 1を使用します。
SELECT
index_type_desc,alloc_unit_type_desc,
avg_record_size_in_bytes,
max_record_size_in_bytes,
avg_page_space_used_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(N'AdventureWorks2016'), OBJECT_ID(N'Production.ProductDocument'), 1, NULL , 'DETAILED')
Note
これを行うと大きな I/O 要求が急増する可能性があるため、バッチ サイズを大きな値に設定しないように注意してください。 詳細については、「 I/O 効果のバッチ サイズが非常に大きいを参照してください。
バッチ サイズの構成が一括読み込み操作のオプションでない場合
何らかの理由でバッチ サイズを変更できない場合、または通常の INSERTSを使用できない場合は、 trace フラグ 692 (TF 692) を使用して高速挿入 (最小ログ記録) 動作を無効にすることができます。 SQL Server 2016 以降では、製品では既定で高速挿入が有効になっています。 その結果、各一括読み込みバッチは新しいエクステントを割り当て、既定で既存のページで使用可能な空き領域の検索をバイパスします。 そのため、バッチ サイズが小さい一括読み込み操作では、オブジェクトの未使用領域が増加する可能性があります。 トレース フラグ 692 は、ヒープまたはクラスター化インデックスへの一括データの読み込み中に高速挿入を無効にします。 これにより、「 Symptoms 」セクションで説明されている未使用の領域の問題が最小限に抑えられます。
SQL Server がオンラインの間にトレース フラグを有効にするには、次のクエリを使用します。
DBCC TRACEON(692,-1)
または、
バッチ サイズが非常に大きい場合の I/O 効果
Bulk ログ復旧モデル (最小ログ記録) では、SQL Server はバッチをコミットするとすぐにデータ ページをフラッシュします (書き込みとも呼ばれます)。 これは、最小ログ記録は、個々のログ レコードがトランザクション ログに書き込まれず、エクステント割り当てのみがログに記録されることを意味するためです。 障害が発生してもデータが失われないように、SQL Server はデータが格納されているデータ ページをディスクに直ちに書き込みます。 そのため、大きなバッチ サイズを選択すると、書き込み I/O バーストが発生する可能性があります。 I/O サブシステムが書き込み I/O バーストを処理できない場合、一括読み込み操作のパフォーマンスと、その時点で SQL Server インスタンスで実行されている他のすべてのトランザクションに悪影響を及ぼす可能性があります。 言い換えると、バッチ サイズに関する利点が減少するポイントがあります。バッチ サイズが大きすぎると、メリットが減少します。
そのため、行の平均サイズに基づいてエクステントのサイズ (64 KB) の倍数であるバッチ サイズを選択することが重要です。 基になるディスク I/O のパフォーマンスに応じて、1 つのエクステント (64 KB) と 64 エクステント (4 MB) のサイズの任意の場所にあるバッチ サイズを選択できます。 この範囲は、効率的なスペース使用率と最適なバルクロード性能のバランスを取ります。