SQL Server におけるバックアップと復元のパフォーマンスの最適化
MicrosoftSQL Server には、バックアップ操作と復元操作の速度を向上する次の 2 つの手段が用意されています。
複数のバックアップ デバイスを使用すると、すべてのデバイスに並列的に書き込むことができます。バックアップ デバイスの速度はバックアップのスループットを高める上でボトルネックとなる可能性があります。複数のデバイスを使用すると、使用するデバイス数に比例してスループットを向上させることができます。同様に、バックアップは複数のデバイスから並列的に復元することもできます。詳細については、このトピックの「複数のメディアまたはデバイスの使用」を参照してください。
完全バックアップ、差分バックアップ、および (完全復旧モデルまたは一括ログ復旧モデルの) トランザクション ログ バックアップを組み合わせて使用し、復旧に必要な時間を最小限に抑えます。データベースの差分バックアップは、通常完全バックアップよりも作成時間が短くなります。また、データベースの復旧に必要なトランザクション ログの量が少なくなります。詳細については、「SQL Server データベースの完全バックアップおよび差分バックアップの作成」を参照してください。
複数のメディアまたはデバイスの使用
バックアップ デバイスからデータベースとトランザクション ログ ファイルへのデータとトランザクション ログのコピーは、リーダー スレッドとライタ スレッドによって実行されます。バックアップ デバイスごとに 1 つのスレッドが割り当てられます。バックアップ デバイスがデータを送る能力、またはデータベースとトランザクション ログ ファイルがデータを受け取る能力によって、パフォーマンスが制限されます。したがって、データベースまたはトランザクション ログがデータを受け取る最大スループットの限界に達するまでは、バックアップ デバイス数が増えるにつれてパフォーマンスは向上します。
バックアップ操作および復元操作に複数のバックアップ デバイスを使用すると、複数のバックアップ デバイスで同時に読み書きを実行できるため、SQL Server の並列 I/O が可能になり、バックアップ操作や復元操作の速度が速くなります。大きなデータベースを使用する企業では、多数のバックアップ デバイスを使用することにより、バックアップ操作および復元操作にかかる時間を大幅に短縮できます。SQL Server では、1 回のバックアップ操作で最高で 64 個のバックアップ デバイスを使用できます。
複数のバックアップ デバイスにバックアップを出力すると、複数の内部的な同期ポイントが発生します。最も重要な同期ポイントが発生するのは、データベース内のすべてのデータのバックアップが完了し、トランザクション ログのバックアップを開始する直前です。
.gif "重要な注意事項") 重要 |
|---|
複数のバックアップ デバイスを使用してバックアップ操作を行う場合、これらのバックアップ デバイスのバックアップ メディアを SQL Server のバックアップ操作以外に使用することはできません。詳細については、「バックアップ メディアの使用」を参照してください。 |
複数のバックアップ デバイスを使用してバックアップを作成し復元することは、1 つのデバイスを使用してバックアップを作成し復元することと変わりありません。唯一異なるのは、操作で使用するバックアップ デバイスを 1 つではなく、すべて指定する必要があることです。たとえば、\\.\TAPE0、\\.\TAPE1、\\.\TAPE2 などの 3 つのテープ バックアップ デバイスを使用してデータベース バックアップを作成する場合、後でバックアップを復元する際にこの 3 つをすべて使用する必要はありませんが、バックアップ操作時にはすべてのテープ デバイスを指定する必要があります。
リムーバブル メディアを使用して複数のバックアップ デバイスにバックアップを作成する場合、デバイスごとに動作速度が異なったり、メディア ボリュームごとに空き容量が異なったりすることがあります。バックアップ操作の間にバックアップ デバイスのメディア ボリュームの空き容量がなくなった場合、デバイスへの書き込みが停止し、新しいメディア ボリュームの挿入を求めるメッセージが表示されます。いっぱいになったメディア ボリュームを空のボリュームと交換するまでは、そのデバイスがブロックされます。その一方で、メディアの空き容量が残っているデバイスにデータの書き込みが続けられます。いっぱいになったメディア ボリュームを交換すると、デバイスが使用できるようになり、そのデバイスへのデータの書き込みが再開されます。ただし、デバイスがブロックされているときに内部同期ポイントが発生すると、そのデバイスが再度使用できるようになるまでバックアップ操作が全面的に一時停止します。
例
同一速度のテープ バックアップ デバイスを 3 つ使用してデータベースの完全バックアップを格納するシナリオを考えます。最初の 2 本のテープには 10 GB の空き容量がありますが、3 番目のテープは 5 GB しか空き容量がありません。20 GB のデータベースを 3 つのテープ バックアップ デバイスすべてに同時にバックアップする場合、3 番目のテープはバックアップの完了前にいっぱいになります。3 番目のテープに 5 GB のデータが書き込まれた時点で、3 番目のデバイスへの書き込みが停止します。このデバイスがブロックされ、新しいテープの挿入を求めるメッセージが表示されます。その一方で、残りの 2 つのバックアップ デバイスへはデータの書き込みが続行されます。ところが、3 番目のテープを交換する前に内部同期ポイントが発生します。このとき、3 番目のデバイスに新しいテープが取り付けられるまで、バックアップ操作が全面的に一時停止します。
完全バックアップと差分バックアップのパフォーマンスの最適化
完全バックアップまたは差分バックアップの作成は、次の手順で構成されています。
データベース ファイルからバックアップ デバイスにデータをコピーします。
トランザクション ログの一部を同じバックアップ デバイスにコピーします。このログは、データベースを一貫性のある状態にロールフォワードするために必要です。
差分バックアップの作成は、変更されたデータだけがコピーされる点を除き、完全バックアップの作成と変わりありません。データベース ファイルのバックアップは、ファイルからバックアップ デバイスへの単純なデータのコピーで構成されます。
データベースの格納に使用するデータベース ファイルは、ディスク デバイスごとに並べ替えられ、各デバイスにはリーダー スレッドが割り当てられます。リーダー スレッドではデータベース ファイルからデータが読み取られます。各バックアップ デバイスにはライタ スレッドが割り当てられます。ライタ スレッドでは、バックアップ デバイスにデータが書き込まれます。さらに多くの論理デバイス間にデータベース ファイルを分散させることで、並列読み取り操作を増やすことができます。同様に、さらに多くのバックアップ デバイスを使用することで並列書き込み操作を増やすことができます。
一般に、データベース ファイルまたはバックアップ デバイスのいずれかがパフォーマンスのボトルネックになります。読み取りスループットの合計がバックアップ デバイス スループットの合計を超える場合は、バックアップ デバイス側がボトルネックになっています。バックアップ デバイス (および必要に応じて SCSI コントローラ) を増やすとパフォーマンスを向上させることができます。ただし、バックアップ スループットの合計が読み取りスループットの合計を超える場合は、データベース ファイルやデバイス (または、RAID デバイスにディスク) を追加して、読み取りスループットを向上させます。
トランザクション ログ バックアップのパフォーマンスの最適化
トランザクション ログ バックアップの作成では、バックアップ デバイスにバックアップされていないログの一部が単純にコピーされます。トランザクション ログ ファイルは複数存在することもありますが、トランザクション ログは論理的には 1 つのスレッドによって順次読み込まれる 1 つのストリームです。
各バックアップ デバイスにはライタ スレッドが割り当てられます。バックアップ デバイスを増やすことで、パフォーマンスを向上できます。
相対速度やバックアップ デバイスの使用数に従って、トランザクション ログ ファイルが格納されたディスク デバイスやバックアップ デバイスが、パフォーマンスのボトルネックになることがあります。バックアップ デバイス数を増やすと、トランザクション ログ ファイルが格納されたディスク デバイスの容量に達するまで、パフォーマンスは向上していきます。一方、ディスク ストライピングなどを使用しても、トランザクション ログが格納されたディスク デバイスの速度を上げない限り、さらにパフォーマンスを向上させることはできません。
復元のパフォーマンスの最適化
データベース バックアップまたは差分バックアップの復元は、次の 4 つの手順で構成されています。
データベースとトランザクション ログ ファイルがまだ存在しない場合、作成します。
バックアップ デバイスからデータベース ファイルにデータをコピーします。
トランザクション ログ ファイルからトランザクション ログをコピーします。
トランザクション ログをロールフォワードし、必要に応じて復旧を再開します。
トランザクション ログ バックアップの適用は、次の 2 つの手順で構成されています。
バックアップ デバイスからトランザクション ログ ファイルにデータをコピーします。
トランザクション ログをロールフォワードします。
データベース ファイルの復元は、次の 2 つの手順で構成されています。
存在しないデータベース ファイルを作成します。
バックアップ デバイスからデータベース ファイルにデータをコピーします。
ファイルの初期化
データベースとトランザクション ログ ファイルが存在しない場合、データを復元する前に作成する必要があります。データベースとトランザクション ログ ファイルを作成し、ファイル内容を空に初期化します。個別のワーカー スレッドが並列的にファイルを作成し初期化します。データベースとトランザクション ログ ファイルは、ディスク デバイスごとに並べ替えられ、個別のワーカー スレッドが各ディスク ドライブに割り当てられます。ファイルの作成と初期化には非常に高いスループットが必要になるので、使用できる論理ドライブ間に均等にファイルを分散するとパフォーマンスが最高になります。
ファイルの瞬時初期化
SQL Server 2005 以降のバージョンでは、データベースやファイル グループの復元操作を高速に実行できるように、データ ファイルを瞬時に初期化できます。ファイルの瞬時初期化では、必要な領域を空にしないで、使用中のディスク領域が再要求されます。代わりに、新しいデータがファイルに書き込まれるときに、ディスクの内容が上書きされます。ログ ファイルの初期化ではそのまま空にする必要がありますが、初期化はバックアップからのデータの転送と同時に行われます。復元のロールフォワードの手順は、すべてのデータが転送され、ログ全体が初期化されるまで開始されません。
.gif "注意") 注意 |
|---|
ファイルの瞬時初期化は、Microsoft Windows XP、Windows Server 2003、またはそれ以降のシステムでのみ使用できます。 |
ファイルの瞬時初期化を使用するには、Windows アカウントで MSSQLSERVER サービス アカウントを実行する必要があります。また、Windows SE_MANAGE_VOLUME_NAME という特殊な特権をその Windows アカウントに割り当てる必要があります。この特権は、既定で Windows Administrators グループに割り当てられます。システム管理者の権限があれば、Windows アカウントをボリュームの保守タスクを実行セキュリティ ポリシーに追加して、この特権を割り当てることができます。ユーザー権利の割り当ての詳細については、Windows のマニュアルを参照してください。
テープ バックアップ デバイスのパフォーマンスの最適化
次に示すいくつかの要素は、テープ バックアップ デバイスのパフォーマンスに影響します。これらの要素により、SQL Server のバックアップ操作と復元操作のパフォーマンスは、テープ デバイスの増加とほぼ比例して向上させることができます。
ソフトウェアのデータ ブロック サイズ。
SCSI (Small Computer System Interface) バスを共有するテープ デバイス数。
テープ デバイスの種類。
ソフトウェアのデータ ブロック サイズは、SQL Server によって最適なパフォーマンスになるよう計算されます。変更しないでください。BLOCKSIZE の最大値は 64 KB です。
多くの高速テープ ドライブは、使用されるテープ ドライブごとに専用 SCSI バスを用意するとパフォーマンスが向上します。ネイティブ転送速度が SCSI バス速度の 50% を超えるドライブは、専用 SCSI バスに接続して、パフォーマンスの低下を回避する必要があります。テープ ドライブのパフォーマンスに影響を与える設定の詳細については、テープ ドライブ ベンダのドキュメントを参照してください。
| 重要 |
|---|
テープ ドライブは、ディスク ドライブや CD-ROM ドライブと同じ SCSI バスには決して接続しないでください。これらのデバイスのエラー処理は相互に互換性がありません。 |
挿入したテープに対して複数のバックアップ操作を実行する場合、NOREWIND を指定すると、パフォーマンスを向上できます。このオプションにより、SQL Server では、バックアップ操作の後テープが開いたままになります。NOREWIND を指定すると、暗黙的に NOUNLOAD も指定されます。
ディスク バックアップ デバイスのパフォーマンスの最適化
ディスク バックアップ デバイスの本来の I/O 速度はディスク バックアップ デバイスのパフォーマンスに影響します。また、SQL Server のバックアップ操作と復元操作のパフォーマンスは、ディスク デバイス数の増加とほぼ比例して向上させることができます。
ディスク バックアップ デバイスに RAID を使用する場合は、注意深く考慮する必要があります。たとえば、RAID 5 の書き込みのパフォーマンスは低く、単一ディスクとほとんど同じ速度です (パリティ情報保持のため)。さらに、ファイルへデータを追加するだけの速度はデバイス本来の書き込み速度よりはるかに低速です。
バックアップ デバイスのストライピングが進んでおり、バックアップ デバイスへの書き込み最大速度がファイルへデータを追加する速度をはるかに上回る場合、何台かの論理バックアップ デバイスを同じストライプ セットに置くと、パフォーマンスが向上することがあります。つまり、いくつかのバックアップ メディアを同じ論理ドライブに置くことで、バックアップのパフォーマンスが向上することがあります。ただし、この方法が個別の環境に適しているかどうかは、実際に試してみる必要があります。通常は、異なるディスク デバイスに各バックアップ デバイスを置いた方がパフォーマンスは向上します。
一般に、SCSI バスでは、2、3 台のディスクしか最高速度で動作できません。ただし、Ultra-wide バスおよび Ultra-2 バスでは、さらに多くのディスクを扱うことができます。ただし、パフォーマンスを最適化するには、ハードウェアの構成に注意する必要があります。
ディスクのパフォーマンスに影響を与える設定の詳細については、ディスク ベンダのドキュメントを参照してください。
データの圧縮
最近のテープ ドライブにはハードウェア データ圧縮機能が組み込まれており、ドライブへの実効データ転送速度を大幅に向上できます。データベースの実際のデータが圧縮できるかどうかは、データそのものと、使用されるテープ ドライブによって決まります。通常、ほとんどのデータベースでは、データの圧縮比率は 1.2:1 ～ 2:1 の範囲内です。大多数のビジネス アプリケーションではこの圧縮比率が一般的ですが、データベースの中にはこの比率を超える、または下回る圧縮比率のものもあります。たとえば、既に圧縮されているイメージ画像を多く含むデータベースは、テープ ドライブによって通常の比率でそれ以上圧縮できません。データ圧縮の詳細については、テープ ドライブ ベンダのドキュメントを参照してください。
既定では、SQL Server はハードウェアによる圧縮をサポートしています。ただし、3205 トレース フラグによってこの機能を無効にすることができます。非常にまれにではありますが、ハードウェアによる圧縮を無効にすることでバックアップ処理のパフォーマンスが向上することもあります。たとえば、データが既に限界まで圧縮されている場合には、ハードウェアによる圧縮機能を無効にすることで、テープ ドライブが無為にデータを圧縮するために時間を浪費することがなくなります。
トレース フラグの詳細については、「トレース フラグ (Transact-SQL)」を参照してください。
バックアップの圧縮
既定の設定では、バックアップの圧縮を使用してバックアップを行うと CPU 使用率が著しく増加し、圧縮処理に CPU が追加で消費されるために、同時に実行中の操作が悪影響を受ける可能性があります。このため、CPU の競合が発生したときは、リソース ガバナによって CPU 使用率が制限されるセッションで、優先度の低い圧縮されたバックアップを作成することが必要になる場合もあります。詳細については、「リソース ガバナーを使用してバックアップの圧縮による CPU 使用率を制限する方法 (Transact-SQL)」を参照してください。
テープに転送されるデータ量
データ バックアップまたは差分バックアップを作成しても、未使用領域はバックアップされず、実際のデータが入ったデータベース部分だけが保存されます。そのため、バックアップ処理の速度が高速になります。
SQL Server データベースは、必要に応じて自動的に拡張するよう構成できますが、データベース内の領域をいつでも使用できるようにするために、空き領域を保持し続けることができます。データベース内に空き領域を保持しても、バックアップ スループットやデータベースのバックアップに必要な時間に影響はありません。
ログ配布の同期の最適化
ログ配布の配布先を同期する場合、RESTORE LOG ステップの間に WITH STANDBY を使用する必要はありません。