データ ローディング パフォーマンス ガイド
SQL Server 技術資料
著者: Thomas Kejser、Peter Carlin、および Stuart Ozer
**テクニカルレビューアーおよび寄稿者:**Sunil Agarwal 、 Ted Lee 、 David Schwartz 、 Chris Lee 、 Lindsey Allen 、 Hermann Daeubler 、 Juergen Thomas 、 Sanjay Mishra 、 Denny Lee 、 Peter Carlin 、 Lubor Kollar
協力者: Henk van der Valk (Unisys) 、 Alexei Khalyako 、および Marcel van der Holst
発行: 2009 年 1 月
対象: SQL Server 2008 および SQL Server 2005
要約 : このホワイト ペーパーでは、 SQL Server に大きなデータ セットを一括して読み込む技法について説明しています。また、一括読み込み処理のパフォーマンスをチューニングし、最適化するために利用できる技法や方法論についても取り上げます。
はじめに
このホワイト ペーパーでは、 Microsoft® SQL Server® のデータベースで高速なデータ変更を実現するための読み込み技法について説明します。
一括読み込み手法について詳しく説明する前の予備知識として、 " 最小ログ記録方式 " についても概説します。
次の「一括読み込み手法」と「その他の最小ログ記録操作とメタデータ操作」では、高速データ読み込みの鍵となる 2 つの関連概念として、一括読み込み操作とメタデータ操作について取り上げます。
その後、これらの予備知識を基に、一括読み込み操作とメタデータ操作をユーザー シナリオの問題解決に利用する方法を紹介します。一般的な設計パターンを示すスクリプトの例は、「一括読み込みによる標準的なシナリオの解決」にあります。同一テーブル内でデータの読み込みと読み取りを同時実行する場合は、特別な考慮事項が必要です。「一括読み込み、 NOLOCK クエリ、および Read Committed スナップショット分離」では、読み込みと読み取りの同時実行を実現するために使用できる手法を説明しています。
このホワイト ペーパー最後の「一括読み込みの最適化」では、トラブルシューティングのヒントを取り上げます。
最小ログ記録操作について
大量のデータを読み込むシナリオをサポートするため、 SQL Server では最小限の情報しかログに記録しない操作を実装しています。すべての行の変更を追跡するためにトランザクション ログを使用する完全ログ記録操作とは異なり、最小ログ記録操作は、エクステントの割り当てとメタデータの変更のみを追跡します。最小ログ記録操作では、トランザクション ログで追跡される情報量が圧倒的に少ないため、ログ記録がボトルネックになる場合は、通常、完全ログ記録操作よりも処理速度が速くなります。さらに、トランザクション ログへの書き込みが少ないため、必要な I/O が少なくなり、ログ ファイルのサイズも大幅に減少します。
しかし、一括読み込み操作では、この最小限の情報さえログに記録せずに操作を実行できます。たとえば、クラスター化インデックスやヒープにさえ、最小限の情報をログに記録しないで、データを一括して読み込むことができます。一般に、最小ログ記録によって処理速度が格段に速くなるメリットがありますが、最小ログ記録さえ行わない一括読み込みでは、従来の行単位のデータの挿入よりもオーバーヘッドが少なくなります。
SQL Server にまつわる通説に反して、最小ログ記録操作はトランザクションに含めることができます。アロケーション構造に行われた変更がすべて追跡されるため、最小ログ記録操作はロールバックできます。
最小ログ記録操作を使用できるのは、データベースが一括ログ復旧モデルまたは単純復旧モデルの場合に限られます。詳細については、「 最小ログ記録が可能な操作 」 (https://msdn.microsoft.com/ja-jp/library/ms191244.aspx) を参照してください。一括ログ復旧モデルのデータベースで一括操作を実行すると、そのデータベースのバックアップ方法に影響する点に注意が必要です。詳細については、「 一括ログ復旧モデルでのバックアップ 」 (https://msdn.microsoft.com/ja-jp/library/ms190692.aspx) を参照してください。
トレース フラグ 610
SQL Server 2008 で導入されたトレース フラグ 610 は、インデックス付きのテーブルへの最小ログ記録方式の挿入を制御します。このトレース フラグは、以下のいずれかの方法で有効にできます。
. –T610 をSQL Server のスタートアップ パラメーターに追加する
. 詳細については、SQL Server オンライン ブックの「 サーバーのスタートアップ オプションを構成する方法 (SQL Server 構成マネージャ) 」(https://msdn.microsoft.com/ja-jp/library/ms345416.aspx) を参照してください。
. DBCC TRACEON (610) を実行する
. これは、特定のセッションに対してトレース フラグを有効にします。インスタンスでの読み込みシナリオのサブセットのみにトレース フラグ 610 を有効にし、その読み込みを発行する Transact-SQL 接続に限定してフラグを適用する場合に効果があります。
. DBCC TRACEON (610, -1) を使用すると、そのトレース フラグが無効になるかサーバーが次回再起動されるまで、サーバーへのすべての接続に対してトレース フラグが有効になります。
. トレース フラグを有効にする DBCC ステートメントの詳細については、SQL Server オンライン ブックの「 DBCC TRACEON (Transact-SQL) 」(https://msdn.microsoft.com/ja-jp/library/ms187329.aspx) を参照してください。
トレース フラグ 610 を使用する場合は、前述の制限事項に注意してください。
トレース フラグ 610 が有効な場合にクラスター化インデックスに挿入される行のすべてが最小ログ記録操作になるわけではありません。一括読み込み操作によって新しいページが割り当てられるときは、その新しいページに順番に設定されるすべての行が、最小ログ記録操作になります。一括読み込み操作が行われる前に既に割り当てられていたページに挿入される行は、完全ログ記録操作のままです。また、読み込み時にページが分割された結果、移動される行も完全ログ記録操作になります。つまり、一部のテーブルについては、依然として、完全ログ記録操作で挿入が行われることがあります。
トレース フラグ 610 によって最小ログ記録操作が行われると、通常、パフォーマンスが向上します。ただし、トレース フラグを有効にする場合は、必ず、お使いの環境とワークロードに即したテストを行うようにしてください。
次の 2 つの例について考えてみましょう。
例 1: 0 ～ 16 の偶数のキー値でクラスター化されたテーブルがあります。そのテーブルには 4 つのリーフ ページがあり、それらのリーフ ページにはそれぞれ 2 行追加で格納できる余裕があります。
ここで 1 ～ 15 の奇数のキー値を持つ 8 行を新たに一括読み込みします。これらの新しい行は既存のページに収まります。テーブルが一括読み込みの前後でどのような構成になるかを次の図に示します。
図 1 : トレース フラグ 610 が有効な場合の完全ログ記録挿入操作
この例では、新しくページが割り当てられないため、トレース フラグ 610 が有効でも最小ログ記録操作は行われません。
例 2: 別のシナリオを考えてみましょう。この例のテーブルは最初 2 ページから構成されています。キー値 0 ～ 7 の行でどちらのページもいっぱいです。ここで 8 ～ 16 のキー値を持つ行の一括読み込みを実行します。
図 2: トレース フラグ 610 が有効な場合の最小ログ記録挿入操作
この例では、トレース フラグ 610 が有効な場合、キー値 8 ～ 15 の行を含むページ ( 図 2 の薄い青色の箇所 ) で最小ログ記録操作が行われます。
テーブルに新しく割り当てられるページ数を確認する方法の 1 つは、 sys.dm_db_partition_stats 動的管理ビューに照会する方法です。次のクエリでは、各インデックスおよびテーブルのページ数が一覧されます。
SELECT OBJECT_NAME(p.object_id) AS object_name
, i.name AS index_name
, ps.in_row_used_page_count
FROM sys.dm_db_partition_stats ps
JOIN sys.partitions p
ON ps.partition_id = p.partition_id
JOIN sys.indexes i
ON p.index_id = i.index_id
AND p.object_id = i.object_id
一括読み込み操作前後の結果を比較すれば、新しく割り当てられたページ数を確認できます。
トレース フラグ 610 を使用し、 BTree を設定している場合、できる限り大きい BATCHSIZE を使用するのがベスト プラクティスです。 SQL Server では、バッチあたり ( つまり、設定されるパーティションあたり ) に非常に多数のページを割り当てる可能性があります。場合によってはこの割り当てアクティビティにより、ページが後で割り当てを解除されるとしても、トレース フラグ 610 を有効にしない場合よりも、有効にした場合の方が、実際にはより全体的な I/O アクティビティが行われる可能性があります。ただし、少数のパーティションを大きなバッチ サイズで読み込むときは、トレース フラグ 610 を有効にすると、完全ログ記録の挿入操作に比べてスループットが大幅に向上します。トレース フラグ 610 が有効な場合の最小ログ記録の詳細については、 SQL Server のストレージ エンジンに関するブログ (https://blogs.msdn.com/sqlserverstorageengine/archive/2008/10/24/new-update-on-minimal-logging-for-sql-server-2008.aspx 、英語 ) を参照してください。
トレースフラグ 610 を有効にした場合の最小ログ記録操作の I/O への影響
最小ログ記録が行われた一括読み込みトランザクションをコミットするときは、コミットが完了する前に、読み込まれたすべてのページをディスクにフラッシュする必要があります。フラッシュされるページの中で、以前のチェックポイントの操作で検出されなかったページはいずれも、大量のランダム I/O を発生させる可能性があります。これは、ログの書き込み時の I/O がシーケンシャルに行われ、コミット時に読み込み済みページをディスクにフラッシュする必要のない完全ログ記録操作とは対照的です。
読み込みのシナリオが BTree での少量の挿入操作で、チェックポイントの境界を超えず、 I/O システムの速度が遅い場合、最小ログ記録を使用すると、実際には、挿入操作の速度が低下する可能性があります。
最小ログ記録の使用条件のまとめ
どの一括読み込み操作において最小ログ記録が行われるかを把握するには、次の組み合わせ表を参照してください。
テーブル インデックス |
テーブル内の行数 |
ヒント |
TF 610 オフ |
TF 610 オン |
同時実行可 |
ヒープ |
任意 |
TABLOCK |
最小 |
最小 |
可 |
ヒープ |
任意 |
なし |
完全 |
完全 |
可 |
ヒープ + |
任意 |
TABLOCK |
完全 |
プランによる (3) |
否 |
クラスター |
空 |
TABLOCK, ORDER (1) |
最小 |
最小 |
否 |
クラスター |
空 |
なし |
完全 |
最小 |
可 (2) |
クラスター |
任意 |
なし |
完全 |
最小 |
可 (2) |
クラスター |
任意 |
TABLOCK |
完全 |
最小 |
否 |
クラスター + |
任意 |
なし |
完全 |
プランによる (3) |
可 (2) |
クラスター + |
任意 |
TABLOCK |
完全 |
プランによる (3) |
否 |
表 1: 最小ログ記録の使用条件のまとめ
1 . INSERT … SELECT メソッドを使用している場合、ORDER ヒントを指定する必要はありませんが、行がクラスター化インデックスと同じ順序になっている必要があります。 BULK INSERT を使用している場合、ORDER ヒントを使用する必要があります 。
2 . 読み込みの同時実行は特定の条件下でのみ可能です。「適切なインデックスによる一括読み込み」を参照してください。また、新しく割り当てられたページに書き込まれる行だけが最小ログ記録操作になります。
3 . テーブルの非クラスター化インデックスが完全ログ記録されるか最小ログ記録されるかは、オプティマイザーが選択するプランによって異なります。
一括読み込み手法
データ挿入操作の速度を上げるため、 SQL Server には一括読み込みの標準手法がいくつか用意されています。ここでは、以下の手法について詳しく説明します。
.Integration Services のデータ変換先 – Integration Services の ETL ツールから一括読み込みを実行する手法。
. BCP – 一括読み込みを実行するコマンド ライン ユーティリティ。
.BULK INSERT – Transact-SQL で一括読み込みを実行する手法。
.INSERT … SELECT – ローカル クエリまたは任意の OLE DB ソースから SQL Server と同じプロセスで一括読み込みを実行する手法。この手法は、SQL Server 2008 で最小ログ記録操作としてのみ使用できます。
. SELECT INTO – クエリ結果を含む新しいテーブルを作成する手法。一括読み込みの最適化を利用します。
このホワイト ペーパーでの " 一括読み込み " という用語は、ここで説明するいずれかの手法を使用することを指します。 "BULK INSERT" は、文字通り、特定の Transact-SQL ベースの一括読み込み手法を指します。
これらの中から最適な手法を選択するには、個々の機能や制限事項を理解しておく必要があります。ここでは、 SQL Server のデータベース エンジンの内外からデータの一括読み込みを行う場合の選択肢について簡単に説明します。
また、 SQL Server のプログラミング インターフェイスでも、 ADO.NET の SQLBulkCopy クラス、 OLE DB の IRowsetFastload インターフェイス、 SQL Server Native Client の ODBC ライブラリなど、同様の一括読み込み技法が提供されています。このホワイト ペーパーでは、これらの技法を取り上げていませんが、使用可能な多くの設定やオプションとともに、これらのプログラミング インターフェイスを使用する際のベスト プラクティスは、このホワイト ペーパーで取り上げている手法の説明にある技法と変わりありません。
Integration Services のデータ変換先
SQL Server Integration Services では、 SQL Server へのデータの一括読み込みの際に、最も柔軟性の高い選択肢が提供されます。 Integration Services と互換性のあるデータ ソースならどのようなデータ ソースからでもデータを読み取り、メモリ内で転送と変換を行い、ディスクにステージングすることなく SQL Server に直接一括読み込みを行うことができます。 Integration Services は別のコンピューター上で実行できる独立したプロセスなので、 CPU を集中的に使用する変換処理を SQL Server からオフロードして、 Integration Services に移管することができます。その結果、一括操作をスケールアウトして、スループットを大幅に向上することができます。
最小ログ記録操作として SQL Server への一括読み込みを実行できるデータ変換先には、ネイティブ形式の SQL Server 変換先と、汎用形式の OLE DB 変換先があります。
SQL Server 変換先
SQL Server 変換先は、Integration Services から SQL Server へとデータが移動する一括読み込みを行う最速の方法です。この変換先は、 SQL Server のすべての一括読み込みオプション (ROWS_PER_BATCH を除く ) をサポートしています。
この変換先を使用するには SQL Server への共有メモリ接続が必要です。つまり、この変換先は、 Integration Services と SQL Server が同じ物理コンピューター上で実行されている場合にのみ使用できます。
OLE DB 変換先
OLE DB 変換先は、 SQL Server の一括読み込みオプションをすべてサポートします。ただし、順序を指定して一括読み込みを行う場合は、追加の構成が必要です。詳細については、「並べ替え済みの入力データ」を参照してください。一括 API を使用するには、 " 高速読み込み " になるように OLE DB 変換先を構成する必要があります。
OLE DB 変換先では、 SQL Server への接続に TCP/IP 接続と名前付きパイプ接続の両方を使用できます。つまり、 SQL Server 変換先とは異なり、 OLE DB 変換先は一括読み込みの対象ではない別のコンピューター上で実行できます。 OLE DB 変換先を使用する Integration Services パッケージは、 SQL Server コンピューター自体で実行されていなくてもかまわないため、専用のサーバーに ETL 処理をスケールアウトできます。
BCP
BCP ( Bulk Copy Program: 一括コピー プログラム ) は、 SQL Server からのデータ抽出と、 SQL Server へのデータのインポートの両方に使用されるコマンド ライン ツールです。このツールは一括 API を使って作成されていて、テキスト ファイルから SQL Server への直接データ挿入をすばやく実行できます。また、 SQL Server のテーブルやクエリからテキスト ファイルにデータをエクスポートできるようにします。
BCP は、テキスト ファイルから SQL Server ネイティブ形式を読み取ることができます。そのため、テキスト ファイルからの入力の解析を最小限に抑える、非常に高速なオプションです。
BULK INSERT
BULK INSERT コマンドは、テキスト ファイルから SQL Server にデータを移動するインプロセス手法です。このコマンドは Sqlservr.exe と同じプロセスで実行されるため、 SQL Server へのデータ ファイルの読み込みが非常に高速です。
BULK INSERT ではデータのエクスポートには使用できず、インポートのみ行うことができます。しかし、この制限事項を別にすれば、 BCP と同じ機能があります。 BULK INSERT は Transact-SQL から呼び出されるため、ストアド プロシージャ、 Transact-SQL ベースの ETL 、および SQL Server エージェントのジョブでの使用に最適です。
SELECT INTO
SELECT INTO ステートメントは、SELECT ステートメントの結果を基に新しいテーブルを作成します。新しく作成される行は最小ログ記録操作となるため、新しいデータの読み込みが非常に高速になります。このステートメントには BATCHSIZE または ROWS_PER_BATCH を制御する方法はありません。
SELECT INTO ステートメントのもう 1 つの制限事項は、読み込み先テーブルが既定のファイル グループに存在していなければならない点です。 SELECT INTO ステートメントを実行中に既定のファイル グループを一時的に変更することはできますが、一括読み込みのシナリオでは実行できません。
INSERT … SELECT
SQL Server 2008 では最小ログ記録の挿入操作を実行する新しい方法が導入され、 Transact-SQL ベースの INSERT ステートメントが特定の条件下で最小ログ記録操作になることを可能にしています。
この機能にはいくつか制限事項があります。まず、コミット サイズ、 CHECK 制約、トリガの起動といった一括読み込みパラメーターは一切使用できません。次に、ここで説明した他の手法 (Integration Services 、 BCP 、および BULK INSERT) では一括更新 (BU) ロックが発行されますが、この手法では読み込み先のテーブルが排他 (X) ロックされます。排他 (X) ロックは他の排他 (X) ロックと同時にはかけられないため、複数の挿入操作を同時に実行することはできません。一括更新 (BU) ロックおよび排他 (X) ロックの詳細については、 SQL Server オンライン ブックの「 ロック モード 」 (https://msdn.microsoft.com/ja-jp/library/ms175519.aspx) を参照してください。
挿入操作自体はシングル スレッド処理ですが、 INSERT ... SELECT ステートメントは高い並列性を実現するために使用できます。このステートメントの SELECT 部分は、 SQL Server データベース エンジンによって完全に並列処理されます。この手法は転送中にデータを変換できるため、 SELECT ステートメントの速度の影響を受ける操作では、 Transact-SQL ベースの変換の高速化を実現するために、 SQL Server データベース エンジンのオプティマイザーを利用できます。
クラスター化インデックスへの INSERT ... SELECT 操作が最小ログ記録操作になるかどうかは、トレース フラグ 610 の状態によって決まります。一方、ヒープへの INSERT ... SELECT 操作は、トレース フラグ 610 の状態とは無関係に最小ログ記録操作になります。
ヒープ
SELECT 操作から行を受け取り、ヒープにこれらの行の挿入する INSERT ステートメントは、挿入先のテーブルで WITH (TABLOCK) ヒントが指定されている場合に最小ログ記録操作になります。
ヒープへの読み込みで最小ログ記録操作を実現するには、次の構文を使用します。
INSERT INTO <DestinationTable> (<Columns>) WITH (TABLOCK)
SELECT <Columns> FROM <SomeStatement>
読み込み先のヒープは空でなくてもかまいません。読み込み先のテーブルがどのファイル グループにも ( パーティション構成上にさえ ) 存在できるというメリットもあり、この技法は SELECT INTO ステートメントよりも高い柔軟性を備えていると言えます。
クラスター化インデックス
クラスター化インデックスに INSERT ... SELECT 操作を実行する場合、トレース フラグ 610 を有効にすれば最小ログ記録操作になります。 TABLOCK ヒントが指定されていなくても、最小ログ記録操作が使用される点に注意してください。つまり、テーブルへの挿入に複数の INSERT ... SELECT ステートメントを、すべて最小ログ記録モードで同時実行できます。この機能にはいくつか制限事項があります。詳細については、「適切なインデックスによる一括読み込み」を参照してください。
また、以下の条件に両方とも該当する場合、トレース フラグ 610 を使用しなくても、一括読み取り操作が最小ログ記録操作になります。
- WITH (TABLOCK) ヒントが読み込み先のテーブルに指定されている
- 読み込み先のテーブルが空
1 つ目の条件では、読み込み先に、同時実行を妨げるスキーマ修正 (Sch-M) ロックがかけられますが、これは順序が指定された挿入の場合です。詳細については、「並べ替え済みの入力データ」を参照してください。
INSERT ... SELECT によるテキストファイルまたは OLE DB データの読み取り
INSERT ... SELECT コマンドを使用して、 OLE DB 対応のあらゆるデータ ソースから SQL Server にデータを読み取ることができます。これは、ソース SELECT ステートメントとして OPENROWSET コマンドを使用することで実現されます。
このような方法で OPENROWSET コマンドを使用すると、 INSERT ... SELECT ステートメントで BULK INSERT コマンドを代用することができます。この方法は、受け取ったデータを挿入前に結合したり、フィルター処理したりする場合にも使用できます。
BULK INSERT では同一ヒープへの最小ログ記録操作のストリームを複数実行できますが、 INSERT ... SELECT ステートメントでは単一ストリームしか実行できません。
また、 OPENROWSET では、挿入先に対する多くのバッチ パラメーターを制御することもできます。 OPENROWSET を INSERT ... SELECT の入力として使用するときは、以下のパラメーターを制御できます。
. ORDER
. ROWS_PER_BATCH (BATCH_SIZE は除く)
. IGNORE_CONSTRAINTS および IGNORE_TRIGGERS
. FIRSTROW および LASTROW
. KEEPDEFAULTS および KEEPIDENTITY
詳細については、 SQL Server オンライン ブックの「 OPENROWSET (Transact-SQL) 」 (https://msdn.microsoft.com/ja-jp/library/ms190312.aspx) を参照してください。
独自の手法の作成
ODBC、OLE DB、.NET、および DB-Library for SQL Server のいずれを使用しても、独自の一括読み込み手法を作成できます。このホワイト ペーパーでは、一括読み込み API とその機能を詳しく説明していません。
独自の一括読み込み手法の作成に関する詳細については、次の資料を参照してください。
.SqlBulkCopy クラス (.NET Framework クラス ライブラリ) (https://msdn.microsoft.com/ja-jp/library/system.data.sqlclient.sqlbulkcopy.aspx)
.一括コピー操作の実行 (https://msdn.microsoft.com/ja-jp/library/ms130809.aspx)
.行セットを一括コピーする方法に関するトピック (OLE DB) (https://msdn.microsoft.com/ja-jp/library/ms403307.aspx)
.一括コピー操作の実行 (ODBC) (https://msdn.microsoft.com/ja-jp/library/ms130792.aspx)
.SQL Server ODBC ドライバで一括コピーを行う方法に関するトピック (ODBC) ( https://msdn.microsoft.com/ja-jp/library/ms403302.aspx )
一括読み込み手法の選択
次の表に、 SQL Server および Integration Services で使用できるさまざまな一括手法をまとめました。
|
機能 |
Integration Services |
BULK INSERT |
BCP |
INSERT … SELECT |
|
|
SQL 変換先 |
OLE DB 変換先 |
||||
|
プロトコル |
共有メモリ |
TCP/IP 名前付きパイプ |
インメモリ |
TCP/IP 共有メモリ 名前付きパイプ |
インメモリ |
|
速度 |
より高速 / 最高速 (4) |
高速 / 最高速 (1) |
最高速 |
高速 |
低速 / 最高速 (2) |
|
データ ソース |
任意 |
任意 |
データファイルのみ |
データファイルのみ |
任意の OLE DB |
|
一括
API
の
|
ネイティブ以外 |
ORDER 以外 ネイティブ以外 |
すべて |
すべて |
ヒント不可 |
|
ヒープの
TABLOCK |
BU |
BU |
BU |
BU |
X |
|
転送時変換の可否 |
可 |
可 |
否 |
否 |
可 |
|
I/O 読み取りブロック サイズ |
状況による (3) |
状況による (3) |
64 KB |
64 KB |
最大 512 KB |
|
SQL Server の バージョン |
2005 および 2008 |
2005 および 2008 |
7.0、2000、2005、および 2008 |
6.0、7.0、2000、2005、および 2008 |
2008 |
|
呼び出し元 |
DTEXEC / BIDS |
DTEXEC / BIDS |
Transact-SQL |
コマンド |
Transact-SQL |
表 2: 一括読み込み手法の選択
(1) SQL Server とは別のサーバーで DTEXEC を実行すると、Integration Services によって、データベース エンジンからデータ変換がオフロードされ、非常に高速な処理を実現できます。
(2) INSERT … SELECT では 、1 つのテーブルへの挿入を同時実行することはできません。 1 つのテーブルにデータを設定するために使用する場合は、Integration Services では複数のストリームを並行実行できるため、多くの場合、処理が高速になります。
(3) 読み取りブロック サイズは、変換元によって異なります。変換元がテキスト ファイルであれば、ブロック サイズとして 128 KB が使用されます。
(4) SQL Server 変換先では、 BULK INSERT よりも多くの CPU サイクルが使用されるため、最高速度が制限されます。ただし、データ変換がオフロードされるため、単一ストリームの挿入のスループットは BULK INSERT よりも高速です。
BATCHSIZE と ROWS_PER_BATCH について
BATCHSIZE と ROWS_PER_BATCH という 2 つの一括読み込みパラメーターについては、もう少し説明が必要です。
BATCHSIZE は、一括操作時に一度にコミットされる行数を示します。この値を既定値のまま使用すると、一括操作全体が 1 つの大きなトランザクションとしてコミットされます。ただし、この値を 0 より大きくすると、指定した行数がサーバーに送信されるたびに新しいトランザクションが作成され、コミットされます。通常、 BATCHSIZE パラメーターは、処理が失敗した場合にバッチがすべて再起動されるのを防ぐために、行を段階的にコミットするために使用します。ただし、自動的に再起動されるわけではないことに注意してください。既に挿入された行数を追跡するかどうかは、アプリケーション プログラマーが決定します。 BATCHSIZE の値が小さいと、トランザクションをコミットするためにオーバーヘッドが追加され、パフォーマンスに影響します。図 3 は、 1 つの一括ストリームで読み込んでいる 17 GB のテーブルへの影響を示しています。値が 10,000 行を超えると、パフォーマンスはほとんど向上していません。
インデックス付きテーブルに挿入する際は、 BATCHSIZE の値を 0 以外にすれば最も効果があります。データが順序付けされていない場合、各バッチのインデックスごとに、内部で 1 回 SORT 操作を実行しなければなりません。そのため、 BATCHSIZE により小さい値を設定すれば、ディスクへの書き込みが行われずに、メモリ内で並べ替えを実行できます。ただし、この場合、並べ替えた後のインデックスで多くの断片化が生じ、順序を指定した範囲スキャンが一般的な場合は、クエリのパフォーマンスに影響することがあります。「並べ替え済みの入力データ」で説明しますが、挿入されるデータが事前に並べ替えられていれば、クラスター化インデックスへの挿入時に断片化の発生を防ぐことができます。
特定のシナリオ向けにさまざまな BATCHSIZE 設定をテストして、読み込みパフォーマンスや断片化への影響を確認することをお勧めします。また、 TABLOCK を使用していない場合にロックのエスカレーションを回避するには、 BATCHSIZE の設定を 0 以外にすると有効です。これは、読み込み時に同時実行リーダーのクエリを想定しているような状況で、それらのクエリをブロックしないようにする場合によく使用されます。他の技法 ( たとえば、 ALTER TABLE) を使用してロックのエスカレーションを回避する場合でも、大量の読み込みで、ロックによって使用されるメモリが過多にならないようにするには、 BATCHSIZE の設定を 0 以外にする必要があります。
図 3: 最小限ログ記録での BCP バッチ サイズの影響
ROWS_PER_BATCH パラメーターは、一括読み込み操作全体の合計行数の見積もりを示します。このパラメーターは、 BATCHSIZE を指定しない場合にクエリ オプティマイザーへのヒントとして使用されます。行数を見積もることができれば、オプティマイザーがそのバッチに適量のメモリ リソースを割り当てることができます。
一括読み込み手法で BATCHSIZE に 0 以外の値を使用する場合は、 ROW_PER_BATCH の設定を空 ( ゼロ ) のままにしておく必要があります。
BATCHSIZE と ROWS_PER_BATCH について混乱を生じる問題は、使用する一括手法によって、異なる名前が使用される点です。次の表は、こうした名前の対応表です。
一括手法 |
BATCHSIZE |
ROWS_PER_BATCH |
Integration Services OLE DB 変換先 |
「 Integration Services のバッチサイズ」を参照 |
バッチごとの行数 |
Integration Services SQL Server 変換先 |
「 Integration Services のバッチサイズ」を参照 |
該当なし |
BCP |
-b <X> (1) |
-h “ROWS_PER_BATCH = <X>” |
BULK INSERT |
BATCHSIZE = X |
ROWS_PER_BATCH = <X> |
INSERT … SELECT (2) |
N/A |
ROWS_PER_BATCH (3) |
表 3: さまざまな一括手法の BATCHSZIE と ROWS_PER_BATCH
(1) BATCH_SIZE が指定されていなければ、 BCP は既定値として 1,000 を使用します。
(2) INSERT … SELECT では、 BATCHSIZE と ROWS_PER_BATCH を制御できません。
(3) ソースとして OPENROWSET と一括ヒントを使用する必要があります。
Integration Services のバッチサイズ
Integration Services では、一括読み込み手法とは異なる方法でバッチサイズが処理されます。
既定では、 Integration Services により、パイプライン バッファーごとに 1 つのバッチが作成されます。 バッファーがフラッシュされてから、バッチがコミットされます。この動作は、データの変換先で [ 挿入コミット サイズの最大値 ] の値を変更することで無効にできます。
次の表は、挿入コミットサイズの最大値 (MICS : Maximum Insert Commit Size ) の動作をまとめたものです。
挿入コミット サイズの最大値 (MICS) |
効果 |
MICS > バッファー サイズ |
設定は無視されます。バッファーごとに 1 つのコミットが発行されます。 |
MICS = 0 |
1 つの大きなバッチとしてバッチ全体がコミットされます。 BATCHSIZE = 0 と同様の動作です。 |
MICS < バッファー サイズ |
MICS 分の行数が送信されるたびに、コミットが発行されます。 各バッファーの最後でもコミットが発行されます。 |
バッファー サイズよりもバッチ サイズを大きくするには、データ フローのプロパティでバッファーのサイズを変更するしかありません。
データ圧縮と一括読み込み
テーブルまたはパーティションが圧縮されている場合にデータの一括読み込みが行われるときは、通常、一括読み込み時にページ レベルと行レベルの両方の圧縮が実行されます。
ただし、例外に注意してください。ページ圧縮されたヒープに一括読み込みを行うときは、 TABLOCK ヒントを使用してページ圧縮を実行する必要があります。 TABLOCK ヒントを使用しないと、ヒープでは行レベルの圧縮のみが実行されます。
一般に、一括読み込み先でページ圧縮が行われていると、一括読み込みの速度が低下します。これは特に、 I/O システムが CPU の能力を上限まで使用している場合に顕著に現れます。
次の図は、 I/O に左右されないサーバーで 5,000 万行のテーブルが圧縮されている場合の影響を示しています。
図 4: BULK INSERT とデータ圧縮
赤い折れ線グラフは一括読み込み後のテーブル サイズを示し、青い棒グラフは一括読み込みにかかった時間を示します。
その他の最小ログ記録操作とメタデータ操作
最小限の情報しかログに記録しない操作には、挿入操作だけでなく、他にも一連の操作があります。このような他の操作を挿入ステートメントと組み合わせると、データ ウェアハウスの読み込みシナリオの多くを完全な最小ログ記録操作として実装できます。
一括読み込み操作の多くは最小ログ記録操作ですが、例外もあります。たとえば、インデックスとデータが既に格納されたテーブルにデータの一括読み込みを行うと、依然として高速の一括読み込みであったとしても、最小ログ記録操作は実行されません。このホワイト ペーパーでは、すべての種類の一括読み込み、つまり最小記録操作の一括読み込みと完全ログ記録操作の一括読み込みの両方について説明します。
ここでは、メタデータのみの操作についても説明します。これは、 SWITCH や TRUNCATE など、実際にはトランザクション ログ ファイルやデータ ファイルがほんの少し変更される場合でも、大量のデータが変更されるように感じられる操作です。
DROP TABLE
テーブルの削除は、メタデータのみの操作です。テーブルを削除すると、 DELETE ステートメントによるログ記録のオーバーヘッドを発生させずに多数の行を非常にすばやく削除できます。
MERGE
Transact-SQL の MERGE コマンドを使用すると、挿入対象の行で最小ログ記録が行われます。ヒープで MERGE コマンドを実行する場合を除き、この最小ログ記録機能は、トレース フラグ 610 が有効な場合にのみ使用されます。
TRUNCATE TABLE
DROP TABLE と同様に、TRUNCATE TABLE もメタデータのみの操作です。 TRUNCATE TABLE には一定の制限事項が適用されることに注意してください。以下に、この制限事項を示します。
** .** 外部キー制約で参照されているテーブルは切り詰めることができない
** .** 切り詰めることができるのはテーブル全体だけで、パーティションを 1 つだけ切り詰めることはできない ( 「パーティションまたはテーブルからの全行削除」を参照してください )
CREATE INDEX 、DROP INDEX 、および REBUILD INDEX
インデックスを作成、削除、および再構築する際に実行する書き込み操作は、最小ログ記録操作です。データ ファイルに大量のデータが書き込まれる場合でも、トランザクション ログに書き込まれるデータはほとんどありません。
SQL Server Enterprise Edition でのインデックスの作成は、並列処理されます。ただし、インデックスを並列処理で作成すると、データの断片化が発生する場合があることに注意してください。
パーティションの SWITCH
SQL Server 2005 から、パーティションとパーティションの切り替えが導入されました。パーティションの切り替えは、データが物理的に移動されないメタデータのみの操作です。パーティションを切り替えると、多くの類似するデータ ストリームを並列に複数のテーブルに読み込むことができます。読み込みが完了したら、 SWITCH コマンドを発行して、 1 つの大きなテーブルを作成します。
最小ログ記録モードでは、テーブルを更新することも、行のサブセットを削除することもできません。しかし、後で「一括読み込みによる標準的なシナリオの解決」で説明するように、 SWITCH コマンドを使用してこのような操作の一部のエミュレーションを行うことができます。
メイン テーブルとの間で相互に切り替えを行うことができる切り替え先の作成は、複雑になることがあります。 SWITCH コマンドを使用するには、双方のテーブルのメタデータと割り当てが正確に一致している必要があります。テーブルのメタデータを一致させるには、次の手順を実行する必要があります。
1 . 列数が正確に一致するテーブルのコピーを作成します。
この手順を実行する明確な方法は、切り替え元テーブルの定義と同じ CREATE TABLE ステートメントを使用することです。
次の技法を使用して、テーブル定義を複製することもできます。
SELECT TOP(0) *
INTO <TargetTable>
FROM <SourceTable>
ただし、新しく作成されるテーブルは既定のファイル グループに割り当てられます。クラスター化インデックスをテーブルに作成し、再度インデックスを削除すると、テーブルを移動できます。
CREATE CLUSTERED INDEX IX_temp
ON <TargetTable> (<Col>) ON [<FileGroup>]
DROP INDEX <TargetTable>.IX_Temp
2 . 切り替え先のインデックスを一致させます。
切り替え元テーブルの定義をコピーしたら、切り替え元と切り替え先のすべてのインデックスを一致させる必要があります。次のインデックスの設定が一致している必要があります。
- 圧縮の設定
- ファイル グループの割り当て
3 . 切り替え先に制約を作成します。
最後の要件は、切り替え先に制約を作成することです。この制約は、切り替え先のパーティション関数の要件と一致している必要があります。この作業を行う際は、注意が必要です。
テーブルの最初のパーティション ( 最も値が小さいパーティション ) には、次のテンプレートを使用します。
ALTER TABLE <TargetTable>
ADD CONSTRAINT CK_<X>
CHECK (<PartCol> < <LeftValue>)
最初と最後のパーティションの間にあるパーティションの制約の形式は次のとおりです。
ALTER TABLE <TargetTable>
ADD CONSTRAINT CK_<X>
CHECK (<PartCol> IS NOT NULL
AND <PartCol> >= <LeftValue>
AND <PartCol> < <RightValue>)
このテンプレートの IS NOT NULL に注目してください。最初のパーティションには、常に、 NULL 値が含まれていますが、 NULL 値どうしは比較できないため、制約で明示的に NULL 値を削除する必要があります。
最後のパーティションには、次のテンプレートを使用します。
ALTER TABLE <TargetTable>
ADD CONSTRAINT CK_<X>
CHECK (<PartCol> IS NOT NULL
AND <PartCol> >= <LeftValue>)
上記の例はすべて、パーティション関数で RANGE LEFT が使用されていると想定しています。
4 . SWITCH コマンドを実行します。
これで、 SWITCH コマンド自体を実行できるようになります。
ALTER TABLE <SourceTable>
SWITCH PARTITION <X> TO <TargetTable>
上記の手順すべてを実行する際には、CodePlex ツールのSQL Server Partition Management Tool (CodePlex) (https://www.codeplex.com/sql2005partitionmgmt、英語) を使用すると役立ちます。
一括読み込みによる標準的なシナリオの解決
ここでは読み込みの標準的なシナリオをいくつか検討し、こうしたシナリオの解決に必要なテンプレートを提供します。例として任意の Sales ファクト テーブルを使用します。ここでは、以下のシナリオについて説明します。
- パーティション分割されていない空のテーブルへの一括読み込み
- パーティション分割されていない既にデータが存在するテーブルへの一括読み込み
- パーティション分割されたテーブルへの一括読み込み
- パーティションまたはテーブルからの全行削除
- パーティションまたはテーブルからの多数の行の削除
- パーティションまたはテーブル内の大部分のデータの更新
それぞれのシナリオでは、操作速度を最大限まで速めるために考慮する必要があるトレードオフについて見ていきます。
この例では、説明のために BULK INSERT 手法を使用しますが、他の一括読み込み手法にもこのガイダンスが当てはまります。
パーティション分割されていない空のテーブルへの一括読み込み
パーティション分割されていないテーブルへのデータの読み込みは単純な操作ですが、いくつかの方法で最適化が可能です。
空のテーブルにデータを読み込む前に、そのテーブルのすべてのインデックスを必ず削除または無効にすることをお勧めします。読み込みが完了してから、インデックスを再作成または有効にします。
読み込み元のデータが SQL Server 内のテーブルであれば、SELECT INTO や INSERT … SELECT を使用すると簡単かつ迅速にインデックスを再作成できます。読み込み元のデータが SQL Server 外にある場合は、必要に応じて BCP、BULKINSERT、INSERT…SELECT、または Integration Services を使用します。
選択した一括操作手法がテーブルに一括更新 (BU) ロックをかける場合に限り、ヒープに対して複数の挿入操作を同時実行できます。 2 つの一括更新 (BU) ロックを同時にかけることができるので、同時に 2 つの一括操作を実行できます。
I/O システムが高速であれば、複数の一括挿入操作を並列に実行することを検討してください。 1 つの一括操作では完全に 1 つの CPU コアのみが使用されます。他のシナリオでも明らかになるように、同時実行される一括操作ストリームを複数利用することが一括読み込みのスケーラビリティを高める鍵になります。
このシナリオでは INSERT … SELECT と SELECT INTO に問題があります。どちらの操作も、読み込み先にテーブル レベルの排他 (X) ロックをかけます。つまり、一定期間に 1 つの一括読み込み操作しか実行できず、スケーラビリティが制限されます。ただし、 BCP 、 BULK INSERT 、および Integration Services の場合は、 TABLOCK ヒントを指定すれば一括更新 (BU) ロックをかけることができます。
以下の形式で複数の一括読み込み操作を実行します。 BULK INSERT Sales FROM 'Sales<X>.csv' WITH ( FIELDTERMINATOR = ';' , ROWTERMINATOR = '\n' , TABLOCK ) |
1 つのヒープに一括読み込み操作を並列実行すると、比較的スムーズにスケール変換されます。 ETL World Record (https://blogs.msdn.com/sqlperf/archive/2008/02/27/etl-world-record.asp 、英語 ) のパフォーマンス テストでは次のような曲線が示されています。
図 5: 1 つのヒープへの一括読み込みのスケーラビリティを示す曲線
データ サイズにもよりますが、最大で 16 の同時一括操作まで、ほぼ線形にスケーラビリティが向上しています。それ以上の一括操作では SQL Server の内部データ アロケーション構造がボトルネックになり始めます。これは sys.dm_os_latch_stats では ALLOC_FREESPACE_CACHE の待機時間として表れます。上記の値は挿入される行のサイズによって変化します。
それでもスケーラビリティを線形に向上させる必要がある場合は、テーブルをパーティション分割するとスループットが向上することがあります。パーティション分割を使用すると、同時に複数のテーブルを読み込み、「パーティション分割されたテーブルへの一括読み込み」で説明するようにパーティションの切り替えによってテーブルを再結合できます。
並べ替え済みの入力データ
ここでは説明が必要な特殊なケースを示します。最終的には読み込み先にクラスター化インデックスが必要で、読み込み元のデータがクラスター化インデックスと同じキーで並べられていれば、一括読み込み時に ORDER ヒントを指定して、そのテーブルのクラスター化インデックスを保持できます。 ORDER ヒントを使用すると、クラスター化インデックスの読み込み時に内部での並べ替え手順を省くことができるため、一括読み込みの速度が大幅に向上します。ただし、これが有効なのは読み込み元から取得するデータが読み込み時点で既に並べ替えられている場合のみで、挿入前に並べ替えを実行しても速度は向上しません。
空のテーブルから操作を始めると、最小限の情報しかログに記録されません。テーブルに既にデータが存在すると、トレース フラグ 610 を使用しない限り、操作がログに記録されます。
ORDER ヒントと TABLOCK を同時に指定できますが、同じテーブルへの複数のストリームを実行できません。複数のストリームを実行する場合は、スキーマ修正 (Sch-M) ロックをかけます。ただし、単一ストリームの方がはるかに高速になります。おそらく、単一ストリームによるヒープの一括読み込みに匹敵します。また、読み込み後に CREATE INDEX 処理 ( コストのかかる並べ替えが必要 ) を個別に実行する必要がありません。
単一ストリームの場合、以下の一括操作がヒープと OrderDate 上のクラスター化インデックスに対して同程度のパフォーマンスを示しました。
BULK INSERT Sales FROM 'C:\temp\Sales200401'
WITH (
FIELDTERMINATOR = ';'
, ROWTERMINATOR = '\n'
, TABLOCK
, ORDER(OrderDate)
)
この場合、クラスター化インデックスとヒープへの挿入に同等のパフォーマンスが確認されました。トレース フラグ 610 を有効にしている場合を除いて、このような操作を最小ログ記録操作にする唯一の方法は、読み込み先のテーブルが空の状態にし、 BATCHSIZE に 0 を指定することです。 BATCHSIZE に 0 以外の値を指定すると、最初のバッチ ( コミット ) しか最小ログ記録操作になりません。
並べ替え済みの入力データをクラスター化インデックスに挿入することによって断片化が最小限に抑えられます。ただし、データの並べ替えには大量のメモリが使用されます。そこで、断片化を最小限に抑えると同時に必要な並べ替えによるメモリの使用量も最小限に抑えるには、以下の方法を使用できます。
1 . 最終の読み込み先と同じスキーマを持つ一時テーブルを作成します。
2 . この一時テーブルには、必ず、最終の読み込み先のインデックスと一致するクラスター化インデックスを設定します。
3 . ステージング テーブルにデータの一括読み込みを行います。パフォーマンス上のメリットがあれば、必要に応じて、トレース フラグ 610 を使用します。
a . これでステージング テーブル内に一括読み込みが行われたデータが存在することになります。このデータは並べ替え済みですが、断片化している可能性があります。
4 . 一括読み込みに INSERT … SELECT 手法を使用して、ステージング テーブルから最終の読み込み先にデータを移動します。
a . データはステージング テーブル内で既に並べ替えられた状態なので、最終の読み込み先にもあらかじめ並べ替えられた状態で移動されます。
b . その結果、断片化が最小限に抑えられます。
上記の方法では、読み込み先にデータを直接読み込むだけの処理に比べて多くの I/O が行われます。それでも断片化が最小限に抑えられることで、範囲スキャンやテーブル スキャンで負荷の高いワークロードの読み取り速度を向上することができます。
メモリに収まる可能性が高い小さいバッチを使用してクラスター化インデックスの一括読み込みを行うと、 SQL Server は、可変長の列には平均半分のサイズしかデータが設定されないという前提で、並べ替えに必要なメモリ サイズを見積もります。見積もりサイズよりも大きいデータ サイズのバッチがあると、並べ替えによってディスクへの書き込みが行われ、パフォーマンスが低下します。 SQL Server Profiler を使用して Sort Warning イベント クラスを追跡することで、並べ替えによるディスクへの書き込みイベントを検出できます。
列の半分しかデータが設定されないという見積もりの代わりに、 varchar 列のほぼ最大長までデータが設定される傾向にある場合は、以下の回避策を使って SQL Server の並べ替えによるディスクへの書き込みを回避できます。
上記の手順 1 で一時テーブルを作成するときに、可変長列のサイズを 3 倍にします。パーマネント テーブルの列長の設定は変更しません。その結果、並べ替え操作に割り当てられるメモリが、必要なサイズよりも大きくなります。
並べ替え済み入力データ向けの Integration Services OLE DB 変換先の構成
GUI では直接公開されませんが、並べ替え済み入力データのストリームをサポートするように 、 Integration Services の OLE DB 変換先を構成できます。これはコンポーネントの詳細設定プロパティで行います。
図 6: 並べ替え済み入力データ向けの OLE DB 変換先の構成
上図のように、 ORDER ヒントを [FastLoadOptions] プロパティに追加します。
パーティション分割されていない既にデータが存在するテーブルへの一括読み込み
読み込むテーブルにインデックスが設定されていない場合は、前述のガイダンスが適用されます。 SELECT INTO 手法では既存のテーブルを読み込めません。
インデックスが設定されたテーブルに読み込む場合には特に注意が必要です。 まず、 SQL Server オンライン ブックの「 一括インポートを最適化するためのガイドライン 」 (https://msdn.microsoft.com/ja-jp/library/ms177445.aspx) を参照してください。このトピックには、テーブルへの一括読み込み前にインデックスを削除するタイミングについてのガイドラインが含まれています。ただし、この SQL Server オンライン ブックのガイドラインは単一ストリームの一括読み込み操作にのみ当てはまります。同時実行数やコア数の増加にともない、インデックスの削除、インデックス再読み込みの同時実行、さらにインデックスの再構築という一連の操作を行う方が既存のインデックスにデータを挿入するよりも高速になる可能性があります。また、テーブルが空の場合、またはトレース フラグ 610 を使用している場合は、クラスター化インデックスへの一括読み込みでは最小限の情報しかログに記録されません。つまり、複数の一括読み込みを同時実行すると、最小ログ記録操作にはなりません。
テストするシナリオは、インデックスの削除と再作成、および適切なインデックスによるデータの読み込みの 2 つです。テーブル全体の再読み込みを同時実行する場合は、前のセクションのガイダンスが適用されます。
適切なインデックスによる一括読み込み
既にインデックスが設定されているテーブルへのデータの一括読み込みは、インデックスのないヒープへの一括読み込みとは大きく異なる操作です。
1 つ以上のインデックスが設定されたテーブルへの一括読み込みでは、以下の事項を考慮する必要があります。
インデックスが設定されたテーブルでは一括更新 (BU) ロックがサポートされない
インデックスが設定されたテーブルでは一括更新 (BU) ロックがサポートされません。代わりに、排他 (X) ロックがかけられます。一括更新 (BU) ロックとは異なり、排他 (X) ロックは同じ種類のロックを同時にかけることができません。インデックスが設定されたテーブルへの一括読み込みでは、排他 (X) ロックが行レベルでかけられます。インデックスが設定されたテーブルへの一括読み込み時に TABLOCK ヒントを発行することもできますが、これではテーブル レベルですべてに排他 (X) ロックがかかることになり、同じテーブルに対する他の一括読み込みスレッドがブロックされます。そのため、インデックスが設定されたテーブルへの一括読み込みを同時実行するには、 TABLOCK ヒントを使用しないようにします。
適切な BATCHSIZE を見つける
一括操作のスループットを最大限まで高めるには、 BATCHSIZE を指定したテストを行います。インデックスのないヒープへの挿入では BATCHSIZE = 0 に設定すると効果がありますが、インデックスが設定されたテーブルでは 0 以外の値を指定します。入力データがあらかじめ並べ替え済み ( かつ、 ORDER ヒントを指定している ) の場合を除いて、オプティマイザーではバッチを挿入する前に入力データを並べ替えます。この並べ替えをメモリ内で処理できなければ、 tempdb で I/O 操作が行われ、挿入速度が低下します。 BATCHSIZE を小さくすると、並べ替えがメモリ内で行われる可能性が高まりますが、受け取るデータが並んでいなければ、ページの分割やインデックスの断片化が生じるという短所があります。 BATCHSIZE は、同一テーブルに対するクエリでのデータ読み込みの同時実行機能にも影響を与えます。
ロックのエスカレーションを避ける
行レベルの排他 (X) ロックの数が増加し、しきい値を超えると、テーブル レベルへのロックのエスカレーションが行われる可能性があります。エスカレーションが行われると、同時実行操作が単一スレッドの挿入に低下します。 SQL Server のバージョンによってその技法は異なりますが、このようなエスカレーションは以下のように防ぐことができます。
. SQL Server 2005 の場合、5000 個のロックが割り当てられた時点でロックのエスカレーションが行われます。ただし、例外もあります。詳細については、 MSDN のブログ (https://blogs.msdn.com/sqlserverstorageengine/archive/2006/05/17/Lock-escalation.aspx、英語) を参照してください。
. BATCHSIZE を 5000 未満の値に設定すると、エスカレーションが無効になります。
. または、トレース フラグ 1211 を使用すると、サーバーでのロックのエスカレーションを完全に無効にできます。
. SQL Server 2008 の場合、テーブル レベルでのロックのエスカレーションを完全に無効にすることもできます。
重なり合わない入力ストリームを使用する
クラスター化インデックスが設定されたテーブルに対し複数の一括操作ストリームを実行するときは、入力データのキー列の範囲が重なり合わないようにします。一括読み込み操作では低レベルのロックが使用されるため、値が重なり合う 2 つのストリームがあると、相互にブロックし、同時実行のパフォーマンスが低下する可能性があります。
トレース フラグ 610 を使用する
トレース フラグ 610 を有効にすると、インデックスが設定されたテーブルへの挿入操作は一般に最小ログ記録操作になります。複数のストリームに含まれるデータが重なり合っていなければ、クラスター化インデックスが設定されたテーブルでは、複数の一括読み込みストリームの同時挿入操作がサポートされます。データの重なりが検出されると、ストリームがブロックされます ( ただし、デッドロック状態にはなりません ) 。
以下に、例として Sales テーブルを使い、クラスター化インデックスへの複数ストリーム処理で BULK INSERT を使用してスループットを最大限に高める方法を示します。
1 . テーブルでのロックのエスカレーションを無効にします。
ALTER TABLE Sales SET (LOCK_ESCALATION = DISABLE)
SQL Server 2005 では BATCHSIZE を 5000 に設定することでロックのエスカレーションを無効にできます。また、トレース フラグ 1211 を実行することもできます ( この方法ではサーバーでのロックのエスカレーションが完全に無効になります ) 。トレース フラグ 1211 の詳細については、 MSDN のブログ (https://blogs.msdn.com/sqlserverstorageengine/archive/2006/05/17/Lock-escalation.aspx 、英語 ) を参照してください。
2 . 範囲が重ならないようにして、複数の BULK INSERT コマンドを開始します。
日付列の範囲が重なり合わないように入力データを分けます。 BULK INSERT Sales FROM '200101.csv' WITH ( FIELDTERMINATOR = ';' , ROWTERMINATOR = '\n' ) BULK INSERT Sales FROM '200102.csv' WITH ( FIELDTERMINATOR = ';' , ROWTERMINATOR = '\n' ) ... など。 個別のストリームに TABLOCK ヒントを発行しません。 |
最適なスループットを実現できるよう、 I/O サブシステムやメモリ リソースを考慮して、 BATCHSIZE にさまざまな値を設定してテストします。
3 . ロックと同時実行の状態をチェックします。
これで、ブロックされない複数の一括読み込みのストリームを確認できるようになります。これらの各ストリームが、低レベルの排他 (X) ロックをかけます。以下のようなクエリを実行してください。
SELECT lck.resource_type, lck.request_mode
, lck.request_status , lck.request_session_id
, COUNT(*) number_locks
FROM sys.dm_tran_locks lck
INNER JOIN sys.partitions par
ON lck.resource_associated_entity_id = par.hobt_id
INNER JOIN sys.objects obj
ON par.object_id = obj.object_id
WHERE obj.name = 'Sales'
GROUP BY lck.resource_type, lck.request_mode
, lck.request_status, lck.request_session_id
結果は、次のようになります。
図 7: 一括読み込み操作によってかけられたロックの一覧
resource_type に KEY と表示されている行の X ロックと、新たに割り当てられるデータ ページのインテント排他 (IX) ロックを確認できます。
パーティション分割されたテーブルへの一括読み込み
パーティション分割されたテーブルへの一括読み込みは、最も高速になる可能性のある一括読み込み操作です。調整のオーバーヘッドが最小限に抑えられ、多くの一括読み込みを並列に同時実行できます。データ読み込み完了後のパーティションの切り替えは、メタデータのみの操作です。
パーティション分割されたテーブルの一括読み込みを実行するときは、一般に、一括操作を開始する前に、操作対象のパーティションから別のパーティションに切り替える ( スイッチアウトする ) のが最適です。スイッチ アウトには、次のような大きなメリットがいくつかあります。
- スイッチ アウトしたオブジェクトのみに一括更新 (BU) ロックをかけて、同時に複数の一括操作ストリームを実行できます。
- 再度スイッチ イン 操作対象のパーティションに切り替えること 状態に戻す前に、スイッチ アウトしたパーティションのインデックスを削除して再構築することができます。これによって一括読み込みの速度が向上します。
- テーブルにインデックスが設定されていない場合、または受け取ったデータに ORDERED ヒントが設定されている場合でも、パーティション分割されたテーブルに直接一括読み込みを行うと、並べ替え処理が開始されます。このような並べ替え処理によって、オプティマイザーでは、負荷の高い挿入操作で連続的に新しいパーティションの開閉を繰り返すオーバーヘッドが取り除かれます。
パーティションがスイッチ アウトされた後は、標準のテーブルとまったく同じように動作し、前のセクションのガイダンスが適用されます。ただし、一括読み込み操作を開始する前に、必ず、読み込み先のパーティションと同じ制約を追加してください ( 読み込みオプションの CHECK CONSTRAINTS も有効にします ) 。テーブルを読み込んでから CHECK 制約を追加することもできますが、テーブル スキャンが必要になります。制約を追加しないと、元のパーティションに切り替えて戻すことができません。
ただし、考慮が必要な特殊なケースがあります。つまり、同時に複数のパーティションにデータを挿入する場合、さらに最適化することができます。この場合、各パーティションを独自のテーブルにスイッチアウトします。 これらのテーブルのそれぞれに 1 つ以上の一括読み込み操作を同時実行します。以下に、この手順を説明します。
1 . ステージング用に一時テーブルを作成します。
切り替え先の一時テーブルを作成します。実行手法は「パーティションの SWITCH 」で説明しています。 データをこの一時テーブルに切り替えます。 ALTER TABLE Sales_P SWITCH PARTITION <X> TO Sales_200<X> |
2 . 個別のテーブルに一括読み込みの最適化を適用します。
BULK INSERT Sales_200<X> FROM 'Sales200<X><Y>.csv' WITH ( FIELDTERMINATOR = ';' , ROWTERMINATOR = '\n' ) …など。 |
3 . すべてのテーブルをメイン テーブルに切り替えて戻します。
ステージング テーブルから切り替えてメイン テーブルに戻します。 ALTER TABLE Sales_200<X> SWITCH TO Sales_P PARTITION <X> |
4 . ステージング テーブルをクリーンアップします。
ステージング用の一時テーブルを削除します。 DROP TABLE Sales_200<X> |
挿入シナリオのまとめ
さまざまな一括読み込み操作シナリオの解決に役立つフローチャートを以下に示します。
図 8: 一括読み込み手法を決めるフローチャート
パーティションまたはテーブルからの全行削除
このシナリオは単純ですが、見落とされがちです。テーブルからすべてのデータを削除する最も高速な方法は、 TRUNCATE ステートメントを実行することです。
TRUNCATE TABLE Sales
パーティションからデータを削除する場合は、もう少し複雑です。
1 . パーティションのデータを保持するために、空の一時テーブルを作成します。
切り替え先の一時テーブルを作成します。実行手法は、「パーティションの SWITCH 」で説明しています。 |
読み込み元のテーブルにインデックスが設定されている場合は、同じインデックスを一時テーブルにも再作成します。
2 . データを削除するパーティションから一時テーブルに切り替えます。
ALTER TABLE Sales_P SWITCH PARTITION 1 TO Sales_Temp |
これで切り替え元のパーティションが空になります。
3 . 一時テーブルを削除または切り詰めます。
一時テーブルが必要でなくなったら、次のように削除します。 DROP TABLE Sales_Temp または、次のようにテーブルを切り詰め、次のパーティションの削除に再利用します。 TRUNCATE TABLE Sales_Temp |
パーティションまたはテーブルからの多数の行の削除
ここまでは、最小ログ記録方式と一括読み込み方式で、データを挿入したり、ある程度削除したりする方法について説明してきました。
しかし、パーティション全体で TRUNCATE を使用しないで、そのパーティションに含まれている多数の行を削除したい場合があります。DELETE ステートメントは完全ログ記録操作です。ただし、一括読み込みでも、DELETE ステートメントと同じ効果を得ることができます。 このシナリオについて、以下に説明します 。
1 . メイン テーブルのコピーを作成します。
切り替え先の一時テーブルを作成します。要件については、「パーティションの SWITCH 」で説明しています。 |
2 . 保持する行を一時パーティションに一括で読み込みます。
一括挿入を実行するには、 2 つの手法のうちのいずれかを使用します。 INSERT … SELECT 手法では次のようになります。 INSERT INTO Sales_Temp WITH (TABLOCK) SELECT * FROM Sales WHERE OrderDate >= '20010101' AND OrderDate < '20020101' AND <保持条件>
もう 1 つ は、Integration Services を使用して、多数のストリームをSales_Tempテーブルに同時実行する手法です。 |
3 . スイッチアウトして、古いデータを切り詰めます。
この操作をトランザクションとして実行する場合は、ここでトランザクションを開始します。 BEGIN TRAN 「パーティションまたはテーブルからの全行削除」で説明した技法を使用して、メイン テーブルから行を削除します。 |
4 . インデックスを再構築して SWITCH を使用します。
Sales_Temp のインデックスと制約がメイン テーブルと一致するように再構築します。「パーティションの SWITCH 」で説明しているパーティション管理ツールが、この作業の実行に役立ちます。 次のように Sales_Temp にスイッチインします。 ALTER TABLE Sales_Temp TO Sales SWITCH PARTITION 1 |
5 . 一時テーブルを削除します。
次のように一時テーブルを削除します。 DROP TABLE Sales_Temp トランザクションを使用している場合は、ここでコミットします。 COMMIT TRAN |
パーティションまたはテーブル内の大部分のデータの更新
ここまでは、最小ログ記録方式でデータを挿入したり、ある程度削除したりする方法について説明してきました。
DELETE と UPDATE はどちらも最小ログ記録操作にはできないため、テーブルやパーティション内の既存の行を最小ログ記録操作で変更することはできません。ただし、一括読み込みを使用して、 UPDATE ステートメントと DELETE ステートメント両方のシミュレーションを行うことができる場合もあります。
たとえば、テーブルまたはパーティション内の多数の行を削除または更新する必要があるとします。このとき、推定では、この操作で変更される行の総数がテーブルの合計サイズの 10 ～ 20% を超えます。このような場合は、次の技法が役に立ちます。
1 . 変更ログを一時テーブルに書き込みます。
切り替え先の一時差分テーブルを作成します。実行手法は、「パーティションの SWITCH 」で説明しています。 更新するレコードを Sales_Delta に一括で読み込みます。変更後の新しい値を書き込みます。
|
2 . テーブルがパーティション分割されている場合は、変更するパーティションにスイッチアウトします。
古いレコードを保持するために切り替え先一時テーブルを作成します。実行手法は、「パーティションの SWITCH 」で説明しています。 次のように、変更するパーティションにスイッチアウトします。 ALTER TABLE Sales_P SWITCH PARTITION 1 TO Sales_Old これで、 Sales_DeltaテーブルとSales_Oldテーブルを作成したことになります。 |
テーブルがパーティション分割されていない場合は、この手順を省略し、次の手順のマージ元の 1 つとして Sales_New ではなく元のテーブルを使用します。
** 3.** 変更ログを元のテーブルとマージし、一時テーブルに一括で読み込みます。
マージの実行 |
切り替え先の一時テーブルを作成します。 実行手法は、「 パーティションの SWITCH 」で説明しています。 古いレコードと差分レコードをマージします。これは、 Integration Services の merge- または参照コンポーネントを使用して簡単に実行できます。 または、 Transact-SQL と次のようなステートメントを使用して、マージすることもできます。 SELECT o.PrimaryKey , COALESCE(d.Col1, o.Col1) , COALESCE(d.Col2, o.Col3) ... etc... FROM Sales_Old o LEFT JOINSales_Delta d |
この手順が完了したら、 Sales_New テーブルにはデータが設定され、適切な行が含まれます。
Sales_New にすべてを挿入する複数の同時実行マージ操作を開始することによって、高い並列性を実現することもできます。このとき、各操作は、 Sales_Delta テーブルのそれぞれの重複しないサブセットで機能します。
4 . 変更ログと元のデータを削除します。
この変更をトランザクション処理する場合は、ここでトランザクションを開始します。 BEGIN TRAN 手順 1. で生成した差分テーブルを削除します。 DROP TABLE Sales_Delta 手順 2. で作成した、元のデータのスイッチアウト テーブルを削除します。 DROP TABLE Sales_Old パーティション分割されていないテーブルを使用している場合は、 Sales_Old を削除する代わりに、 Sales を切り詰めます。 |
5 . マージした結果にスイッチインします。
次のように SWITCH コマンドを使用して、新しいバージョンのテーブルを古いバージョンのテーブルに移動します。 ALTER TABLE Sales_New SWITCH TO Sales_P PARTITION 1 Sales_New テーブルを削除してクリーンアップします。 DROP TABLE Sales_New この変更をトランザクション処理する場合は、ここでコミットします。 COMMIT TRAN |
これで終了です。最小ログ記録方式で大量の更新を実行されました。
一括読み込み、 NOLOCK クエリ、および Read Committed スナップショット分離
ロックをかけて読み込み操作を実行している可能性のあるテーブルからデータを読み取る一般的な方法は、 NOLOCK ヒントを指定してクエリを発行することです。 " ダーティ リード " とも呼ばれる NOLOCK クエリは、 Read Uncommitted 分離レベルで実行され、不完全な結果セットや一貫性のない結果セットが返される危険があります。しかし、 NOLOCK クエリは共有ロックをかけないため、ほとんどのライターではブロックされません。
Read Committed スナップショット分離 (RCSI: Read Committed Snapshot Isolation ) は、データを読み取るクエリと、同じテーブルのデータを変更する他のクエリが互いをブロックしないようにする新しいメカニズムとして、 SQL Server 2005 で導入されました。完全でトランザクションとして一貫性のあるデータのビューが保証され、特別なヒントを必要としないため、 RCSI は NOLOCK に代わる強力な機能です。本来 RCSI は OLTP ワークロードに共通するシナリオを対象としていましたが、この機能は、大規模な一括挿入操作に関連するデータ ウェアハウスのワークロードやシナリオで非常に強力なツールになります。
RCSI は、データベース全体の設定として有効になります。 RCSI を有効にすると、リーダー クエリで行、ページ、またはテーブルに共有ロックがかけられなくなるため、他のクエリでかけられた X ロックや BU ロックによってブロックされることがありません。代わりに、テーブルの新しい行または変更された行には 17 バイトのバージョン ID が設定され、トランザクション処理で変更 ( 更新または削除 ) されたすべての行の変更前イメージは、 SQL Server の行のバージョン管理メカニズムを使用して tempdb にコピーされます。リーダー クエリでは、クエリの開始時にコミットされていた行のみを対象とします ( 以降のバージョン番号は無視され、 tempdb で行の適切な以前のバージョンが参照されます ) 。
RCSI でのトランザクションの考え方は、通常の Read Committed 分離の場合と同じです。ただし、リーダー クエリでは、競合するライターが完了するまでブロックされるのではなく、必ずクエリ開始時のデータを参照する点が異なります。このため、 RCSI を有効にして実行する場合でもアプリケーションを変更する必要がなく、動作は同じです。しかし、 REPEATABLE READ が必要なトランザクション、または HOLDLOCK を指定する SELECT が必要なトランザクションにはロックが要求され、同時実行しているライターによってブロックされることがあります。また、 SQL Server 2005 で導入されたスナップショット分離レベル機能でも行のバージョン管理を使用しますが、この機能と RCSI を混同しないでください。
大量の UPDATE 処理や DELETE 処理が発生する可能性のあるデータベースの場合、 RCSI によって tempdb の競合と帯域幅のニーズが増加することがあります。しかし、 RCSI を有効にして INSERT (BULK INSERT など ) を実行しても tempdb には影響せず、各行に 17 バイトずつ追加される以外はオーバーヘッドが増加しません ( この 17 バイトは圧縮もできないことに注意してください ) 。
RCSI が有効な場合、リーダーは X ロックの影響を受けませんが、 SQL Server 2008 では、次の 2 とおりの一括読み込みの状況で RCSI クエリ ( および NOLOCK クエリ ) がブロックされます。
a . BULK INSERT、INSERT-SELECT などの一括読み込み操作を使用し、 TABLOCK を指定してヒープにデータを挿入する場合。これは、ヒープ読み込みによって BULK OPERATION インテント排他 (IX) ロックがかけられ、NOLOCK リーダーまたは RCSI リーダーによって BULK OPERATION 共有 (S) ロックがかけられるためです。RCSI または NOLOCK を使用する場合でも、ヒープを一括読み込みし、リーダーの同時実行を 許可するには、TABLOCK ヒントを削除して最小ログ記録を断念する必要があります。
b . BULK INSERT、INSERT…SELECT などの一括読み込み操作を使用し、TABLOCK を指定して、空のクラスター化インデックスにデータを設定する場合。これは、読み込みによってスキーマ修正 (Sch-M) ロックがかけられ、リーダーによってスキーマ安定度 (Sch-S) ロックがかけられるためです。代替方法は、トレース フラグ 610 を使用して最小ログ記録を実行し、TABLOCK ヒントを使用しないことです。
前述の RCSI が有効な 2 つの例外を除いて、 (INSERT…SELECT を含む ) BULK INSERT は TABLOCK ヒントと共に使用することも、通常のロックのエスカレーション規則と共に使用することもできます。いずれの場合でもリーダーはブロックされません。クエリを NOLOCK ヒントと組み合わせて実行する場合とパフォーマンスは同様ですが、 NOLOCK の場合と異なり、トランザクションとして一貫性のあるテーブルのデータのビューがリーダーに提供されるというメリットがあります。 1 つのテーブルに対して多数のリーダー クエリが長時間同時実行されている場合でも、新しい INSERT 操作がリーダーによってブロックされることはありません。
RCSI をデータベースで有効にするには、変更するデータベースで他に有効な接続がない状態で、次のコマンドを実行する必要があります。
ALTER DATABASE <dbname> SET READ_COMMITTED_SNAPSHOT ON
RCSI が既存のデータベースで有効になっているかどうか確認するには、次のコードを使用します。
SELECT name, is_read_committed_snapshot_on
FROM sys.databases
WHERE name = '<dbname>'
ワークロードで RCSI を使用することを決定する前に、必ず UPDATE 操作と DELETE 操作の頻度を十分に把握し、ワークロードの該当部分を処理するように tempdb が構成されるようにしてください。また、リーダーとライターとのブロックが減少すると、新しく同時実行されるリーダーとライターのクエリ間で発生する I/O の競合が増加することがあるため、データ ドライブの I/O 帯域幅を十分に用意するようにしてください。
一括読み込みの最適化
ここからは、一括読み込み処理を最適化するために使用する技法について、さらに深く掘り下げて説明します。 SQL Server では、一括読み込み中に非常に高いスループットを実現でき、 そのような状況下でも、すべてのリソースを適切に管理できます。
一括読み込みを最適化できる分野はいくつかあります。何よりもまず、 CPU コア数にほぼ正比例してスケール変換できるように一括読み込みを最適化するには、並列処理が重要です。
パフォーマンスのトラブルシューティングでの根本的な原因の分析に必要なデータ ポイントには、待機の統計とパフォーマンス カウンターがあります。代表的なパフォーマンスのボトルネックについては、「関連性の高い待機の種類」と「パフォーマンス カウンター」で説明します。
パフォーマンスのチューニングやボトルネックの解消という一部の分野については、より詳しく説明します。 このような状況については、「ネットワークを最適化するパフォーマンス カウンター」、「スケジューラの競合」、および「 PFS の競合」で説明します。このセクションの最後では、重要な「 I/O およびファイル レイアウトの最適化」についても説明します。
並列処理と入力データのパーティション分割
一括操作時のスループットを最適化するには、並列処理がきわめて重要です。複数の一括読み込みコマンドを同時に実行して、並列処理を行うことをお勧めします。それぞれの一括操作はシングル スレッド処理になるため、すべての CPU コアを利用するには複数のコピーを実行する必要があります。実行する一括コマンドごとに、 1 つの CPU コアの負荷を 100% まで活用できます。つまり、すべての CPU コアの使用率が 100% になれば最高のパフォーマンスを実現できるので、一括操作に使用できる CPU コア数と同数の一括コマンドを並列して実行します。
並列操作を設計する際は、別の操作より前に終了する操作の影響について検討することも必要です。理想的には、入力データをそれぞれ同じサイズのパーティションに分割します。そうすると、すべての並列ジョブがほぼ同時に終了します。
次の図は、入力データのパーティション サイズが異なると、実行時間がどの程度長くなる可能性があるかを示しています。
図 9: パーティション サイズのバランス
関連性の高い待機の種類
一括操作のスループットを向上させる必要がある場合は、 sys.dm_os_wait_stats からデータを収集し、なんらかのリソースによって CPU がブロックされているかどうかを判断します。まず、最も長い待機時間に重点を置き、徐々にボトルネックを減らしていきます。この作業を行うには、 TechNet スクリプトの「 Retrieve Waitstat Snapshots 」 ( https://www.microsoft.com/technet/scriptcenter/scripts/sql/sql2005/waitstats/sql05vb048.mspx?mfr=true 、英語 ) が役に立ちます。
また、 1 つのテーブルへの多数の一括スレッドを実行する場合は特に、 sys.dm_os_latch_stats からデータを収集すると有効です。
次の表に、一括読み込み時の一般的な待機、その待機の原因、およびボトルネックを調査して解消するために可能な措置を示します。
待機の種類 |
一般的な原因 |
調査方法と解決策 |
LCK_<X> |
あるプロセスが別のプロセスをブロックしている |
入力ストリームは重なり合っていないか。 TABLOCK を正しく使用しているか。 最上位でブロックしているプロセスを検索します。 |
PAGEIOLATCH_<X> |
ディスク システムの速度が遅すぎる |
ディスクを追加するか、 I/O をチューニングします。 「 I/O およびファイル レイアウトの最適化」を参照してください。 |
IMPROV_IO |
テキスト ファイル データ ドライブも速度が遅すぎる |
入力ファイル用ドライブの I/O を最適化します。 |
PAGELATCH_UP |
PFS ページで競合が発生している |
ディスク システムの速度が十分速いことを確認する。 「 PFS の競合」を参照してください。 –E フラグを指定して実行します。 |
ASYNC_NETWORK_IO |
ネットワーク速度が間に合わない |
「ネットワークを最適化するパフォーマンス カウンター」を参照してください。 |
WRITELOG |
トランザクション ログが間に合わない |
最小ログ記録操作を使用していることを確認する。 トランザクション ログを高速ディスクに作成します。 |
OLEDB |
入力データが遅すぎる |
入力データ ソースの速度を最適化します。 |
SOS_SCHEDULER_YIELD |
スケジューラで競合が発生している |
「スケジューラの競合」を参照してください。 |
ALLOC_FREESPACE_CACHE |
ヒープの割り当てで競合が発生している (sys.dm_os_latch_stats のみで見つかる ) |
ヒープに一度に挿入するスレッド数が多すぎないか。テーブルをパーティション分割して挿入先のヒープを増やすことを検討します。 |
PREEMPTY_COM_<X> |
なし |
通常の想定内の待機です。無視できます。 |
表 4: 一括操作の一般的な待機の種類
パフォーマンス カウンター
一括読み込み中には、次のパフォーマンス カウンターを追跡すると役に立ちます。
パフォーマンス オブジェクト |
カウンター |
用途 |
Logical Disk |
Disk Write Bytes Bytes/sec |
ドライブへの効率的な書き込み速度を計測します。 |
Logical Disk |
Disk Read Bytes/sec |
読み込み元からデータを読み取る速度を計測します。 |
Logical Disk |
Avg Disk Bytes/Write |
ディスク ブロック サイズの大きさを示します。 「 I/O およびファイル レイアウトの最適化」を参照してください。 |
Processor |
% Processor Time (Total) |
ボトルネックがない場合に、一括読み込みタスクごとに、 1 CPU の使用率 100 ％ を想定できる時間。 |
MSSQL::Databases |
Bulk Copy Rows/sec |
データベースに挿入される行数を計測します。最適化すると、増加します。 |
Network Interface |
Bytes Total/sec |
サーバーの NIC から取得する帯域幅を計測します。 |
SQL:Databases |
Log bytes Flushed/sec |
トランザクション ログへのスループットを計測します。 |
表 5: ネットワークを最適化するパフォーマンス カウンター
Integration Services または BCP を使用して、ネットワーク経由でデータの一括読み込みを行う場合は、スループットを適切に構成することが非常に重要です。
ネットワーク部門と連携して、ネットワークのインフラストラクチャおよび構成を理解してください。また、待機時間ができる限り短くなるように構成してください。最適化できる設定には次のようなものがあります。
. 高速の NIC とスイッチを使用する。
. 最新の NIC 用認定ドライバーをインストールする。
. 全二重を有効にする。
. Jumbo Frame のサポートを有効にする。
. TCP Chimney オフロードを使用する。
. Receive Side Scaling (RSS) を使用する。
次のリンク先のページでは、 NIC の主な機能の概要、および NIC と Windows® オペレーティング システムがどのように相互作用するかについて説明しています。
. High Performance Network Adapters and Drivers (https://www.microsoft.com/whdc/device/network/NetAdapters-Drvs.mspx、英語)
. Scalable Networking Pack: Frequently Asked Questions (https://www.microsoft.com/technet/network/snp/faq.mspx、英語)
. Introduction to Receive-Side Scaling (https://msdn.microsoft.com/en-us/library/ms795616.aspx、英語)
一括読み込みを行うクライアント自体で、 SQL Server 接続のネットワーク パケット サイズのパラメーターを変更して、ネットワーク スループットを最適化できます。次に示すように、 Integration Services では、この作業を接続マネージャーのプロパティで行います。
図 10: ネットワーク パケット サイズの構成
BCP を使用する場合は、 -a スイッチでパケット サイズを指定できます。ネットワーク パケット サイズをチューニングすると、 SQL Server ネットワーク スタックが必要とする読み取りと書き込みの操作数が減少します。各操作にはセットアップに必要な CPU オーバーヘッドがあるため、実行する I/O 操作の数を少なくすると、パフォーマンスが向上します。次の表は、 ETL の世界記録を達成した単一ストリームでの最適化を示しています。
ネットワーク パケット サイズ |
SQL Server でのネットワーク読み取り数 |
SQL Server でのディスク書き込み数 |
Integration Services からのディスク読み取り数 |
Integration Services でのネットワーク書き込み数 |
実行時間 |
4096 ( 既定値) |
465K |
8K (256 KB) |
14K (128 KB) |
465K |
2 分 56 秒 |
32K |
58K |
8K (256 KB) |
14K (128 KB) |
58K |
2 分 36 秒 |
表 6: ネットワーク パケット サイズの効果
適切な帯域幅を実現するために複数のネットワーク カードを使用している場合、割り込みアフィニティ向けに Windows を構成してメリットが得られることもあります。詳細については、「 Scaling Heavy Network Traffic with Windows 」 (https://blogs.msdn.com/sqlcat/archive/2008/09/18/scaling-heavy-network-traffic-with-windows.aspx 、英語 ) を参照してください。
1 ギガビットの NIC 1 枚で、一括読み込みを行うデータ量に応じて、 1 秒あたりおよそ 70 ～ 100 MB の処理を実現できます。これを診断するには、 Network Interface パフォーマンス カウンターを計測します。
図 11: ネットワーク スループットの監視例
スケジューラの競合
1 つの CPU サイクルを奪い合う 2 つの一括読み込み操作が、最終的に同じ SQLOS スケジューラで同時に実行されると、スケジューラの競合が発生します。通常、この問題は、多くの CPU コアが搭載されているコンピューターで発生します。この問題が発生すると、 CPU と同数の読み込みスレッドしかない場合でも、 SOS_SCHEDULER_YIELD という待機が発生します。
この問題を解決する 1 つの方法は、Non-Uniform Memory Access (ソフト NUMA) を使用するように SQL Server を構成することです。つまり、 CPU ごとに 1 つのソフト NUMA ノードを構成します。その後、ソフト NUMA ノードごとに個別の TCP/IP ポートを割り当てます。その結果、 SQL Server への接続時にポートを指定することで、どの CPU で処理を実行するかを選択できます。ソフト NUMA の設定を変更するには、 SQL Server を再起動する必要があることに注意してください。
次の図は、この原理を示しています。
図 12: ソフト NUMA と一括接続
このようにソフト NUMA を構成する方法については、「 ソフト NUMA を使用するように SQL Server を構成する方法 」 (https://msdn.microsoft.com/ja-jp/library/ms345357.aspx) および「 NUMA ノードに TCP/IP ポートをマッピングする方法 」 (https://msdn.microsoft.com/ja-jp/library/ms345346.aspx) を参照してください。
各ノードのポートを構成すると、 SQL Server ログ ファイルに TCP ポートが示されます。
図 13: SQL Server ログ ファイルでのソフト NUMA 構成の確認
Bulk Insert の同期ワークロードに参加していない通常の接続で、すべての CPU にマッピングされる別のポートを同時に管理することもできます。
スケジューラの競合を回避するには、 " 終了して再接続 " という別の技法を使用することもできます。この技法については、テクニカル ノートの「 Resolving scheduler contention for concurrent BULK INSERT 」 (http://sqlcat.com/technicalnotes/archive/2008/04/09/resolving-scheduler-contention-for-concurrent-bulk-insert.aspx 、英語 ) を参照してください。
PFS の競合
一括読み込みでログに記録される情報がほとんどない場合でも、なんらかの割り当てによるボトルネックが発生する可能性があります。 SQL Server では、データ ファイル内の Page Free Space (PFS) という特殊なページを使用して、使用済みのページを追跡します。
一括操作では新しいページをすばやく割り当てる必要があるため、新しい領域を割り当てるために PFS ページにアクセスすることがボトルネックになる可能性があります。このボトルネックが発生すると、 PAGELATCH_UP を待機します。待機しているページが PFS ページであることを確認するには、 sys.dm_os_buffer_descriptors に照会します。影響するファイル グループにデータ ファイルを追加すれば、 PAGELATCH_UP のボトルネックを解消できます。「 SQL Server での一括操作の対象」で説明するように、ファイル グループ内のデータ ファイルが多いと、効率の低い I/O パターンが作り出される可能性があります。
PFS ページおよび PFS ページによってボトルネックが発生するしくみの詳細については、テクニカルノートの「How many files should a database have? -Part 1: OLAP workloads」(http://sqlcat.com/technicalnotes/archive/2008/03/07/How-many-files-should-a-database-have-part-1-olap-workloads.aspx、英語) および SQL Server 2008 オンラインブックの「エクステント割り当てと空き領域の管理」(https://msdn.microsoft.com/ja-jp/library/ms175195.aspx) を参照してください。
I/O およびファイルレイアウトの最適化
多くのストリームを並列に一括で読み込むときは、 I/O サブシステムのレイアウトを慎重に検討します。読み取りおよび書き込みの速度を上がるにつれて、ディスクの待機時間が最適化の重要なポイントになります。
一括読み込み処理では、読み込み元からデータを読み取る処理と、読み取ったデータをデータベース エンジンに一括で読み込む処理が同時に行われる必要があります。つまり、発行される I/O パターンは、その性質上、ほとんどの場合にシーケンシャルです。このことを利用すると、パフォーマンスが大きく向上する可能性があります。たとえば、 ETL の世界記録を達成したテストでは、一括読み込みをサポートするようにディスク レイアウトを変更することで、 I/O の 2 つの要因に向上が見られました。
多くの場合、読み込み先とは異なるディスク セットに読み込み元データを格納すると、メリットが得られます。読み取りアクティビティと書き込みアクティビティとを分離することで、 2 つの異なるシーケンシャル ストリームをそれぞれ専用のスピンドルに分割します。
Windows の観点からは、使用するパーティションが 64 KB の NTFS クラスター サイズでフォーマットされ、セクター境界に揃えて適切に整列されるようにします。
読み込み元と読み込み先はそれぞれ独自に最適化できます。これについては、この後説明します。
データ ソース
一括読み込みのパフォーマンスを最大限に高めるため、一括読み込み操作を行うたびに、データ ソースから複数の同時実行読み取りを発行しているとします。
データ ソースがパーティション分割されていて、シーケンシャル読み取りを実行していれば、各読み込み元データのパーティションを専用のスピンドルのセットに配置してデータ ソースを最適化できます。しかし、読み込み元のパーティションが多くなるにつれ、すぐに管理の負担が増加します。
参考になる例として、テキスト ファイルをデータ ソースに使用し、それらのテキスト ファイルに対し BULK INSERT コマンドの 64 個の同時実行コピーを実行しているとします。
ディスク構成の 2 つの優れた方法について説明しましょう。
. Just a Bunch Of Disks (JBOD): この構成では、シーケンシャル ストリームごとに 1 つのディスク (または RAID 1 や RAID 10 の LUN) が割り当てられます
. Stripe And Mirror Everything (SAME): この構成では、1 つの大規模な LUN が割り当てられます
Just a Bunch Of Disks (JBOD)
パフォーマンスの点では、通常、 JBOD の I/O レイアウトが最も高速です。この場合、テキスト ファイルごとに 1 つのディスクが割り当てられます。次の図に、この構成を示します。
図 14: JBOD のディスク レイアウト
BULK INSERT コマンドを実行するとファイルがシーケンシャルに読み取られるため、このディスクの I/O パターンは完全にシーケンシャルで、ディスク シーク操作の待機時間がほとんど発生しません。最近のハード ドライブは、シーケンシャル読み取りで 100 MB/ 秒を超えるデータを容易に配信できます。これは、 BULK INSERT コマンドを実行するのに十分すぎるくらいです。ただし、このレイアウトの管理は容易でしょうか。入力ファイルを保持するためだけに、オペレーティング システムに 32 個の LUN を公開する必要があります。これらの各 LUN を、 SAN 構成または SCSI コントローラー レベルでそれぞれ構成する必要があります。それだけでなく、パーティション分割、ファイル システム管理、バックアップ作業などを考えると、ユーザーの作業が急激に増加します。この設定を自動化し、ファイルの配置と取得についての高度なメカニズムを考案しない限り、人為的エラーが発生する可能性が高くなります。
Stripe And Mirror Everything (SAME)
もう一方のディスク構成は、非常に管理しやすい構成になっています。この構成では、すべてのデータ ファイル用の多数のディスクを含む、 1 つの大きな LUN を RAID 構成内に割り当てます。
このシナリオは、管理しやすく、大量のストレージをすばやくサーバーに公開できるので、よく使用されます。
図 15: SAME のディスク レイアウト
ただし、同じスピンドルで複数のシーケンシャル読み取りが同時実行されると、いわゆる I/O ウィービングが発生します。 (SQL Server の観点から ) ワークロードは実際にはシーケンシャルですが、ディスクの観点からはランダムと判断されます。ランダム I/O の実行により、ディスク シークのオーバーヘッドが生じるため、必要なスループットを得るために桁違いに多くのスピンドルが必要です。
一部の SAN システムや SCSI コントローラーでは、独自のキャッシュ メカニズムを使用して、複数のストリームによって生成されるランダム I/O パターンをシーケンシャル パターンに変換できます。この I/O パターンについては、ストレージ ベンダーと協力して、ストレージ・システムの限界をテストすることをお勧めします。
環境に最適な構成は、 JBOD 戦略と SAME 戦略の中間にある可能性があります。 JBOD のパフォーマンスと SAME の管理容易性のバランスを慎重に取る必要があります。また、 JBOD では、一括読み込みが本質的にシーケンシャルになる可能性が高いことを利用している点に注意してください。シーケンシャルとランダムが混在するワークロードに合わせてチューニングすると、 SAME 戦略ではスループット全体が向上する可能性があります。
SQL Server での一括操作の対象
読み込み元データと同様に、一括読み込みの対象となるテーブルでも I/O を最適化するとメリットが得られます。テーブルに一括読み込みを行う際、書き込みの I/O パターンはシーケンシャルになる可能性が高くなります。これをメリットとして利用できます。
まず、 SQL Server によってどのようにファイルに書き込みが行われるかを理解します。 2 つのファイルを含むファイル グループについて考えてみましょう。
図 16: ファイル グループおよびファイル
SQL Server によってこのファイル グループに割り当てられたテーブルに一括読み込みが行われる際、エクステントがラウンド ロビン方式で割り当てられます。既定の構成では、 SQL Server によって各ファイルに 1 つのエクステントが割り当てられてから、チェーン内の次のファイルに移動します。割り当てられるエクステントの数は、 –E 起動フラグを使用して 64 個に増やすことができます。
ファイル |
既定で割り当てられるエクステント |
–E 起動フラグを使用した場合 |
ファイル 0 |
エクステント 1 |
エクステント 1～64 |
ファイル 1 |
エクステント 2 |
エクステント 65～128 |
ファイル 0 |
エクステント 3 |
エクステント 129～192 |
ファイル 1 |
エクステント 4 |
エクステント 192～256 |
… など .. |
… など… |
… など… |
表 7: -E フラグの効果
各エクステントのサイズは 64 KB なので、これが一括読み込み時のファイルへの最小書き込みサイズになります。そのため、一目見て、 64 KB ブロックのシーケンシャル ストリームが各 LUN に書き込まれることを予想しますが、実際にはもう少し効率のよい処理が行われます。
SQL Server では、 Windows のスキャッター / ギャザー I/O 最適化を使用します。この最適化では、複数の I/O 要求をグループ化して、 1 つの大きなブロック要求を作成できます。たとえば、 64 KB の 4 つの I/O 要求を 256 KB の 1 つの要求にまとめることができます。 1 つの大きな I/O 要求は、複数の小さな要求よりも迅速に発行できるため、このスキャッター / ギャザーによって書き込み操作の速度が向上します。このことにより一括操作のスループットが高くなるメリットが得られます。通常、 I/O 要求ごとに最大 256 KB の大規模ブロック書き込みが実行されます。
ファイル グループに含まれるファイル数が多いほど、 SQL Server から書き込み用の最初のファイルが " 返される " のに時間がかかります。このため、スキャッター / ギャザー最適化では、 SQL Server では 256 KB の制限まで I/O バッファーがいっぱいになる前に、 I/O バッファーをフラッシュすることがあります。 Logical Disk オブジェクトの Avg. Disk Bytes/Write パフォーマンス カウンターを計測すると、スキャッター / ギャザーによって書き込みバッファーがどのように使用されているかを特定できます。
ファイル グループに含まれるファイル数が少ないほど、スキャッター / ギャザーによって大きな I/O 要求が作成される可能性が高くなります。 –E フラグを指定して SQL Server を実行すると、書き込みブロック サイズのグループ化にさらに良い影響を与えます。
ETL の世界記録の場合、ファイル グループごとのファイル数は少ないままでした。そのため、純粋な 256 KB のブロック サイズというパターンを取得できました。
図 17: ブロック サイズの計測
ただし、既に説明したように、ファイル数が少なすぎると PFS の競合 が発生する可能性があることに注意してください。
ファイルと LUN の関係の最適化
一括読み込み時に、各データベース ファイルは、ブロック サイズが 64 KB ～ 256 KB の書き込み I/O シーケンシャル ストリームを受け取ります。
「データ ソース」で説明したように、シーケンシャルになる可能性の高いワークロード パターンを実現するように I/O システムを最適化すると、大きなメリットが得られます。ここでも、 JBOD と SAME の 2 つの優れた方法について考え、 1 つの大きな LUN を作成するのと、複数の小さな LUN を作成するのとではどちらが適切かを検討してください。
大まかに説明すると、 LUN ごとに 1 つのデータベース ファイルを割り当てることで、パフォーマンスの高い構成が実現されます。その後、 RAID 構成内の複数のディスクに各 LUN を作成できます。各一括入力ファイルで独自の LUN を必要とした読み取りの最適化とは異なり、 SQL Server を利用して、一括読み取りの対象となる複数のテーブルを同じファイルにシーケンシャルにストリーミングします。 SQL Server では、一括読み込み時にファイルへの書き込みをシーケンシャルに行うので、パフォーマンスを最適化するために、一括ストリームごとに 1 つの LUN を割り当てる必要はありません。つまり、共有されないスピンドルに大規模 LUN を作成する必要はありません。各 LUN で、 1 つのデータベース ファイルにサービスを提供します。つまり、 LUN とデータベース間の比率をほぼ 1:1 に維持します。もちろん、 SAN またはコントローラー キャッシュで I/O ウィービングを処理できる場合は、 1 つの LUN で複数のファイルにサービスを提供することもできます。展開前にテストを行い、同じ LUN への複数のシーケンシャルな書き込みストリームが I/O ストリームにどのような影響を与えるかを把握してください。
上記の考慮事項は、データベース内のファイル グループごとに 1 つの大きな LUN が存在する SAME 構成で機能します。ただし、「 PFS の競合」で既に説明したように、ファイル数が少なすぎると、 ( 比率を 1:1 に維持する必要があるので LUN も少なくなり ) 、 PFS ページでボトルネックが生じることがあります。そのため、ファイル グループごとに複数のデータベース ファイルまたは LUN を割り当てたくなるかもしれません。 たとえば、 ETL の世界記録の場合、対象となるコンピューターの 4 基のコアごとに、 1 つのデータベース ファイル /LUN を使用しました。
導入事例 : ETL の世界記録
ETL の世界記録を達成したテストでは、 56 個の一括挿入タスクを並列実行しました。そのときの平均速度は 1 秒あたり 600 MB で、ピーク時には最大 1 秒あたり 850 MB になりました。
使用した SQL Server 2008 データベース サーバーは、次のとおりでした。
** .** 32 基のデュアル コア 3 GHz Intel 7140M CPU を搭載した Unisys ES7000/one Enterprise Server
. 合計 64 基のコア
. 8 x 4 ギガビット Emulex HBA
. 8 x 1 ギガビット Intel Pro/1000MT ネットワーク カード
. 256 GB RAM (ただし、一括挿入操作で使用されるのは 30 GB のみ)
こうした状況における最適な SQL Server ファイル レイアウトは、次のとおりでした。
** .** 合計 165 個のスピンドルを搭載した EMC Clariion CX3-80 SAN に SQL Server データ ファイルを配置
. 16 個の Meta-LUN
. 各 Meta-LUN は、4 x (1 + 1R1) として合計 8 個のスピンドルで構成されます。
. 図 18: "4 x RAID 1" 個の LUN を含む Meta-LUN 構成: 4 x (1+1R1)
. 133 GB または 4 ギガビットの 15K RPM のスピンドル
. 56 個のデータ ファイル (各 LUN には最大 4 個のデータ ファイルを配置)
. SQL Server ログ ファイル
. 単一の 4 x (1 + 1R1) Meta-LUN
. 133 GB または 4 ギガビットの 15K RPM のディスク
読み取り用のファイル レイアウトは、次のとおりでした。
. 2 x EMC Clariion CX600 SAN
. RAID 5 で構成された 10 個の LUN (各 CX600 に 5 個)
. LUN ごとに 7 x 36 GB の 15K RPM のスピンドル
. 各 LUN では、入力ファイル 5 ～ 6 個分のテキスト ファイルを保持
詳細については、次のトピックを参照してください。
. ETL の世界記録に関するお知らせ (https://blogs.msdn.com/sqlperf/archive/2008/02/27/etl-world-record.aspx、英語)
. Unisys ES7000 Enterprise servers and BI solutions (http://www9.unisys.com/products/enterprise\_\_servers/business\_\_intelligence/index.htm 、英語 )
I/O の関連情報
I/O の詳細については、次の資料を参照してください。
. Storage Top 10 Best Practices (https://www.microsoft.com/technet/prodtechnol/sql/bestpractice/storage-top-10.mspx、英語)
. SQLIO ダウンロード ページ (https://www.microsoft.com/downloads/details.aspx?familyid=9a8b005b-84e4-4f24-8d65-cb53442d9e19\&displaylang=en、英語)
. SQLIOSim ツールに関するサポート技術情報の記事 (https://support.microsoft.com/kb/231619)
. SQL Server ストレージ エンジン チームのブログ (https://blogs.msdn.com/sqlserverstorageengine/、英語)
. SQL Server 2000 I/O Basics(https://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx、英語)
. Predeployment I/O Best Practices (https://www.microsoft.com/technet/prodtechnol/sql/bestpractice/pdpliobp.mspx、英語)
. Disk Subsystem Performance Analysis for Windows (https://www.microsoft.com/whdc/archive/subsys\_perf.mspx、英語)
まとめ
すべてのデータを読み込む処理のバッチ期間が足りない場合、または大量のデータを読み込むときに最大スループットを実現したい場合は、このホワイト ペーパーを参考にして、パフォーマンスの調査と最適化を行ってください。
このホワイト ペーパーでは、ユーザーのシナリオにとって最適な一括読み込み手法を選択する方法について説明しています。一括読み込み時にはいくつか典型的なシナリオがあります。ここでは、 作業を開始するのに役立つ解決策とサンプル スクリプトを用意しました。
すべてのワークロードに高いスループットを生み出すと同時に、ボトルネックを解消することが、最適なパフォーマンスを実現するためには重要です。「一括読み込みの最適化」では、一括読み込みが高速になるように SQL Server の構成を最適化する方法についてのガイドラインを提供しました。必要に応じて、関連資料へのリンクも記載しています。
SQL Server と Integration Services を組み合わせて使用すると、非常に高速にデータを読み込むことができます。 ETL の世界記録を達成したテストでは、このホワイト ペーパーで説明したガイダンスを使用しました。ここで説明した最適化を実装することで、インストール時の既定の設定をそのまま使用する場合と比較してスループットが 3 倍に増加しました。さらに、 Windows Server® 2008 Datacenter Edition を実行する 64 コア Unisys ES7000/one システムでは、 1 ～ 64 個の同時実行一括読み込み操作までは、ほぼ線形的にスケーラビリティが向上しました。
関連情報:
https://www.microsoft.com/japan/sqlserver/: SQL Server Web サイト
http://sqlcat.com (英語): SQL Server Customer Advisory Team Web サイト
https://technet.microsoft.com/ja-jp/sqlserver/: SQL Server TechCenter
https://msdn.microsoft.com/ja-jp/sqlserver/: SQL Server デベロッパー センター
このホワイト ペーパーはお役に立ちましたか ? フィードバックをお寄せください。 1 ( 役に立たなかった ) ～ 5 ( 非常に役に立った ) の 5 段階で評価してください。また、その評価の理由もお知らせください。以下に例を示します。
評価が高いのは、例が適切、図がわかりやすい、説明が明快といった理由からですか。
評価が低いのは、例が少ない、図がわかりにくい、説明があいまいといった理由からですか。
このようなフィードバックをお寄せいただくと、今後のホワイト ペーパーの品質向上につながります。