一括インポートを最適化するためのガイドライン
このトピックでは、次の一括インポート シナリオのパフォーマンスを最適化するためのガイドラインについて説明します。
1 台のクライアント (またはストリーム) から空のテーブルへのデータのインポート
1 台のクライアント (またはストリーム) から、データが部分的に格納されている空でないテーブルへのデータのインポート
.gif "注意")
注意空でないテーブルへのデータのインポートは、増分一括インポートと呼ばれます。増分一括インポートの重要な問題は、インデックスを事前に削除する必要があるかどうかです。
テーブル レベルのロックと並行した、複数のクライアント (またはストリーム) からのデータのインポート
MicrosoftSQL Server インスタンス間でのデータのコピー
また、このトピックでは、一括インポート操作中のテーブル ロックとログ記録の概要についても説明します。
1 台のクライアント (またはストリーム) から空のテーブルへのデータのインポート
1 台のクライアント (またはストリーム) から空のテーブルにデータをインポートする場合、Microsoft では次の手順を実行することをお勧めします。
TABLOCK 修飾子 (3 つすべての一括インポート方法でヒントまたはオプションとして使用可能) を指定します。TABLOCK を使用すると、一括操作を実行している間にテーブル レベルのロックが取得され、個々の行をロックするオーバーヘッドが解消されます。詳細については、「一括インポートのロック動作の制御」を参照してください。
最小限のログを記録します。詳細については、「一括インポートで最小ログ記録を行うための前提条件」を参照してください。
インデックスを次のように処理します。
bcp、BULK INSERT、または INSERT ...SELECT * FROM OPENROWSET(BULK...) を使用するときに、空のテーブルにクラスタ化インデックスが設定され、データ ファイル内のデータがクラスタ化インデックス キー列と一致するように順序付けられている場合は、さらに次の操作を実行します。
既に設定されているクラスタ化インデックスを保持したままデータを一括インポートします。
TABLOCK ヒントだけでなく、ORDER ヒントも指定します。詳細については、「データの一括インポート時の並べ替え順の制御」を参照してください。
空のテーブルの場合、この方法では並べ替え手順が省略されるので、データのインポート後にクラスタ化インデックスを作成するよりも大幅に時間が短縮されます。
注意空でないテーブルにインデックスが設定されている場合、一括ログ復旧モデルであっても、一括インポートのログは完全に記録されます。インデックスを削除するかどうかを決定するには、インデックスが設定されていないテーブルに一括インポートする利点と、インデックスを削除して再作成するコストを比較検討してください。
インデックスが設定された空のテーブルにデータをインポートし、バッチ サイズを指定すると、最初のバッチ後にテーブルは空でなくなります。2 番目のバッチから、データはログに完全に記録されます。インデックス付きの空テーブルの場合は、1 つのバッチで一括インポートを実行することを検討してください。
注意バッチ サイズが指定されていない場合、既定では SQL Server クエリ オプティマイザによって、データ ファイルのサイズが既定サイズであると見なされます。パフォーマンスを向上させるには、データ ファイル内のおおよその行数についてオプティマイザに指定するヒントとして、ROWS_PER_BATCH 修飾子または KILOBYTES_PER_BATCH 修飾子を使用できます。詳細については、「一括コピー バッチ サイズの管理」を参照してください。
通常、インデックス付きのテーブルに一括インポートするよりも、インデックスなしのテーブルに一括インポートする方が短時間で済みます。したがって、空のテーブルにインデックスがある場合は、そのインデックスを削除してから、テーブルにデータをインポートし、後でインデックスを再作成してください。クラスタ化キー列でデータが並べ替えられていない場合や、テーブルが空である場合は、すべてのインデックスを削除してからデータをインポートし、新しいインデックスを作成してください。
1 台のクライアント (またはストリーム) から空でないテーブルへのデータのインポート
データが既に格納されているテーブル (空でないテーブル) へのデータのインポートは、増分一括インポートと呼ばれます。増分一括インポートの重要な問題は、インデックスを事前に削除する必要があるかどうかです。この場合、2 つの選択肢があります。インデックスを保持するか、または削除してから再作成することができます。
1 台のクライアント (またはストリーム) から空でないテーブルにデータをインポートする場合、インデックスを保持するかどうかは、テーブル内の既存データの量と比較した、インポートする新しいデータの量によって決まります。
既存データの量と比較して、インポートする新規データの量が少ない場合、インデックスを削除して再作成する方法は効率が悪い可能性があります。つまり、インデックスの再作成に必要な時間が、一括操作時に短縮される時間よりも長くなる可能性があります。
これに対して、比較的大量の新規データをインポートする場合は、テーブルのインデックスを削除してから一括操作を実行すると、インデックス作成に必要な時間があまり増加せずに、パフォーマンスを向上させることができます。
次の表は、インデックスを削除するかどうかの目安となる、テーブル内の新規データの最小量を示しています。テーブル内の総データ量が増えれば、最小量も増えます。最小量は、インデックスの種類と組み合わせによって異なります。新しいデータが、特定の種類のインデックスまたはインデックスの組み合わせについて次の表に示されている割合を超える場合は、インデックスを削除してから一括操作を実行し、後でインデックスを再作成することを検討してください。これらの数字は、既存のデータまたは読み込まれるデータのデータ パターンに依存します。このため、一般的なガイドラインとしてのみ提供しています。
インデックス |
新しいデータの相対量 |
|---|---|
クラスタ化インデックスのみ |
30% |
クラスタ化インデックスおよび 1 つの非クラスタ化インデックス |
25% |
クラスタ化インデックスおよび 2 つの非クラスタ化インデックス |
25% |
1 つの非クラスタ化インデックスのみ |
100% |
2 つの非クラスタ化インデックス |
60% |
テーブル レベルのロックと並行した、複数のクライアント (またはストリーム) からのデータのインポート
複数のプロセッサが搭載されたコンピュータで SQL Server を実行しており、テーブルに一括インポートするデータを個別のデータ ファイルに分割できる場合は、複数のクライアントから並列的にデータをテーブルにインポートすることによって、パフォーマンスを向上させることができます。複数のクライアントから 1 つのテーブルに一括インポートする場合、各クライアントに独自の入力データ ファイルが必要です。
複数のクライアントからテーブルにデータをインポートするときは、次のことに注意してください。
複数の一括インポート ストリームは相互にブロックされる可能性があります。
これを防止するために、SQL Server には、一括更新 (BU) ロックと呼ばれる特殊な内部ロックが用意されています。BU ロックを取得するには、他の一括インポート ストリームをブロックすることのないように、各一括インポート ストリームについて TABLOCK オプションを指定する必要があります。これにより、クライアント間のテーブル アクセスの競合を防ぐことができます。ただし、BU ロックが使用できるのは、テーブルが空であるかどうかにかかわらず、テーブルにインデックスがない場合に限られます。インデックス付きのテーブルで TABLOCK を指定する場合、並行一括インポートは使用できません。詳細については、「一括インポートのロック動作の制御」を参照してください。
テーブルにインデックスがある場合に BU ロックを使用するには、データを一括インポートする前にすべてのインデックスを削除します。その後、TABLOCK オプションを使用してデータを並行して一括インポートし、インデックスを再作成してください。また、空でないテーブルにインデックスが設定されている場合、一括ログ復旧モデルであっても、一括インポートのログは完全に記録されます。インデックスを削除するかどうかを決定するには、インデックスが設定されていないテーブルに一括インポートする利点と、インデックスを削除して再作成するコストを比較検討してください。
注意セカンダリ インデックスを削除する場合は、個別のクライアントから各セカンダリ インデックスを作成することによって、インデックスを並行して再作成することを検討してください。
TABLOCK ヒントを指定せずに並列インポートを実行すると、インデックスの削除と再作成を行わなくても済みます。ただし、この方法では、複数の一括インポート ストリームが相互にブロックされる可能性があり、一括ログを最適化できなくなります。より小さなバッチ サイズを指定し、ORDER ヒントを使用して一括インポート操作時の並べ替え手順を省略すると、ブロックを最小限に抑えることができます。
データを複数の入力ファイルに分割して、各クライアントに 1 つずつ配置する必要があります。CPU を最も効率よく使用するには、データ ファイルをほぼ同じサイズにしてください。
詳細については、「テーブルレベル ロックと並行したデータのインポート」を参照してください。
一括インポート中のテーブル ロックとログ記録
次の表は、一括インポート操作中に、ロックの種類がテーブル スキーマによってどのように決定されるかをまとめたものです。テーブルが空かどうか、操作に TABLOCK が設定されているかどうか、およびデータベースで一括ログ復旧モデルを使用している場合にどの種類のログ記録が行われるかについても示しています。
| 注意 |
|---|
最初にバッチが成功すると、テーブルは空ではなくなります。 |
一括インポート先のテーブル |
テーブルが空 |
TABLOCK が設定されている |
ロック |
一括ログ復旧モデルと単純復旧モデルで実行されるログ記録 |
|---|---|---|---|---|
ヒープ |
はい |
はい |
BU-Tab |
一括ログ記録 |
ヒープ |
はい |
いいえ |
IX-Tab |
完全ログ記録 |
ヒープ |
いいえ |
はい |
BU-tab |
一括ログ記録 |
ヒープ |
いいえ |
いいえ |
IX-Tab |
完全ログ記録 |
1 つの非クラスタ化インデックスを使用するヒープ |
はい |
はい |
SCH-M |
一括ログ記録 |
1 つの非クラスタ化インデックスを使用するヒープ |
はい |
いいえ |
IX-Tab |
完全ログ記録 |
1 つの非クラスタ化インデックスを使用するヒープ |
いいえ |
はい |
SCH-M |
|
1 つの非クラスタ化インデックスを使用するヒープ |
いいえ |
いいえ |
IX-Tab |
完全ログ記録 |
クラスタ化インデックス |
はい |
はい |
SCH-M |
一括ログ記録 |
クラスタ化インデックス |
はい |
いいえ |
IX-Tab |
完全ログ記録 |
クラスタ化インデックス |
いいえ |
はい |
X-TAB |
完全ログ記録 |
クラスタ化インデックス |
いいえ |
いいえ |
IX-Tab |
完全ログ記録 |
SQL Server インスタンス間でのデータのコピー
SQL Server のインスタンス間でデータを一括コピーするには、bcp を使用してテーブル データをデータ ファイルにエクスポートします。次に、いずれかの一括インポート方法を使用して、ファイルからテーブルにデータをインポートします。一括エクスポート操作と一括インポート操作はいずれも、ネイティブ形式または Unicode ネイティブ形式を使用して実行します。
| 注意 |
|---|
これらの形式の詳細については、「ネイティブ形式を使用したデータのインポートまたはエクスポート」および「Unicode ネイティブ形式を使用したデータのインポートまたはエクスポート」を参照してください。 |
インポート元のテーブルにクラスタ化インデックスが設定されているか、またはクラスタ化インデックスがあるテーブルにデータを一括インポートする場合は、次の操作を実行します。
SELECT ステートメントの query オプションを指定して bcp を使用し、適切な ORDER BY 句を使用して正しい順序付けられたデータ ファイルを作成することによって、インポート元テーブルからデータを一括エクスポートします。詳細については、「bcp ユーティリティ」を参照してください。
データを SQL Server に一括インポートする場合は、ORDER 修飾子を使用します。この修飾子は、bcp および BULK INSERT でのみサポートされています。詳細については、「データの一括インポート時の並べ替え順の制御」を参照してください。
詳細については、「サーバー間でのデータのコピー」を参照してください。