SQL Serverでのデータの信頼性を拡張するログ記録とデータ ストレージ アルゴリズムの説明
元の製品バージョン: SQL Server 2014、SQL Server 2012、SQL Server 2008、SQL Server 2005
元の KB 番号: 230785
概要
この記事では、Microsoft SQL Server ログとデータ アルゴリズムによってデータの信頼性と整合性がどのように拡張されるかについて説明します。
エンジンの基礎となる概念と、回復と分離の悪用セマンティクス (ARIES) のアルゴリズムの詳細については、次の ACM Transactions on Database Systems ドキュメント (ボリューム 17、Number 1、1992 年 3 月) を参照してください。
外部リンク: ARIES: Write-Ahead ログを使用した Fine-Granularity ロックと部分ロールバックをサポートするトランザクション回復方法
このドキュメントでは、エラーに関連するデータの信頼性と整合性を拡張するためのSQL Server手法について説明します。
キャッシュと代替エラー モードの説明の詳細については、Microsoft サポート技術情報の次の記事を参照することをお勧めします。
この記事で使用される用語
詳しい説明を開始する前に、この記事全体で使用される用語の一部を次の表で定義します。
| 用語 | 定義 |
|---|---|
| バッテリーバックアップ | データ損失を防ぐために、キャッシュ メカニズムによって直接使用可能で制御される個別のローカライズされたバッテリ バックアップ機能。 これは無停電電源装置 (UPS) ではありません。 UPS は書き込みアクティビティを保証せず、キャッシュ デバイスから切断できます。 |
| キャッシュ | 物理 I/O 操作を最適化し、パフォーマンスを向上させるために使用される中間ストレージ メカニズム。 |
| ダーティ ページ | 安定ストレージにまだフラッシュされていないデータ変更を含むページ。 ダーティ ページ バッファーの詳細については、「オンライン ブックでのページの記述」SQL Server参照してください。 コンテンツは、Microsoft SQL Server 2012 以降のバージョンにも適用されます。 |
| 失敗 | SQL Server プロセスの予期しない停止を引き起こす可能性のあるもの。 たとえば、停電、コンピューターのリセット、メモリ エラー、その他のハードウェアの問題、不良セクタ、ドライブの停止、システム障害などです。 |
| フラッシュ | キャッシュ バッファーを安定ストレージに強制する。 |
| ラッチ | リソースの物理的な整合性を保護するために使用される同期オブジェクト。 |
| 不揮発性ストレージ | システム障害が発生しても使用可能な媒体。 |
| ピン留めされたページ | データ キャッシュに残り、関連するすべてのログ レコードが安定したストレージの場所でセキュリティで保護されるまで、安定したストレージにフラッシュできないページ。 |
| 安定したストレージ | 不揮発性ストレージと同じです。 |
| 揮発性ストレージ | 障害が発生してもそのまま残らないメディア。 |
Write-Ahead ログ記録 (WAL) プロトコル
プロトコルという用語は、WAL を説明する優れた方法です。 これは、データが正しく格納および交換され、障害が発生した場合に既知の状態に復旧できるようにするために必要な、特定の定義済みの実装手順のセットです。 ネットワークには、一貫性のある保護された方法でデータを交換するための定義済みのプロトコルが含まれているのと同様に、WAL もデータを保護するためのプロトコルを記述します。
ARIES ドキュメントでは、WAL は次のように定義されます。
WAL プロトコルは、一部のデータに対する変更を表すログ レコードは、変更されたデータが不揮発性ストレージ内の以前のバージョンのデータを置き換えることを許可する前に、既に安定したストレージ内にある必要があることをアサートします。 つまり、ページの更新が安定ストレージに書き込まれるログ レコードの少なくとも元に戻す部分まで、更新されたページをページの不揮発性ストレージ バージョンに書き込むことが許可されません。
先書きログの詳細については、「オンライン ブック」の「先書きトランザクション ログ」トピックSQL Server参照してください。
SQL Serverと WAL
SQL Serverは WAL プロトコルを使用します。 トランザクションが正しくコミットされていることを確認するには、トランザクションに関連付けられているすべてのログ レコードを安定したストレージにセキュリティで保護する必要があります。
この状況を明確にするために、次の具体的な例を考えてみましょう。
注:
この例では、インデックスがなく、影響を受けるページが 150 ページであるとします。
BEGIN TRANSACTION
INSERT INTO tblTest VALUES (1)
COMMIT TRANSACTION
次に、次の表で説明するように、アクティビティを単純なログ記録手順に分割します。
| Statement | 実行されたアクション |
|---|---|
| BEGIN TRANSACTION | ログ キャッシュ領域に書き込まれます。 ただし、SQL Serverは物理的な変更を行っていないので、安定したストレージにフラッシュする必要はありません。 |
| TblTest に挿入する | 1. データ ページ 150 は、まだ使用できない場合SQL Serverデータ キャッシュに取得されます。 2. ページがラッチされ、ピン留めされ、ダーティマークされ、適切なロックが取得されます。 3. ログ レコードの挿入がビルドされ、ログ キャッシュに追加されます。 4. データ ページに新しい行が追加されます。 5. ラッチが解放されます。 6. トランザクションまたはページに関連付けられているログ レコードは、すべての変更が揮発性ストレージに残るため、この時点でフラッシュする必要はありません。 |
| COMMIT TRANSACTION | 1. コミット ログ レコードが形成され、トランザクションに関連付けられているログ レコードを安定ストレージに書き込む必要があります。 トランザクションは、ログ レコードが安定ストレージに正しく割り当てられるまでコミットされたとは見なされません。 2. データ ページ 150 はデータ キャッシュSQL Server残り、安定したストレージにすぐにフラッシュされません。 ログ レコードが正しくセキュリティで保護されている場合、必要に応じて、復旧によって操作をやり直すことができます。 3. トランザクション ロックが解放されます。 |
"ロック" と "ログ記録" という用語で混乱しないでください。重要ですが、WAL を処理する場合、ロックとログ記録は別の問題です。 前の例では、SQL Serverは通常、トランザクションの時間全体ではなく、ページで物理挿入の変更を実行するために必要な時間、ページ 150 のラッチを保持します。 必要に応じて、行、範囲、ページ、またはテーブルを保護するために、適切なロックの種類が確立されます。 ロックの種類の詳細については、SQL Serverオンライン ブックのロックに関するセクションを参照してください。
この例を詳しく見ると、LazyWriter または CheckPoint プロセスの実行時にどうなるかを確認できます。 SQL Serverは、ダーティおよびピン留めされたページに関連付けられているトランザクション ログ レコードに対して、すべての適切なフラッシュを安定ストレージに発行します。 これにより、関連するトランザクション ログ レコードがフラッシュされるまで、WAL プロトコル データ ページを安定ストレージに書き込むことができなくなります。
SQL Serverと安定したストレージ
SQL Serverでは、ディスク セクター サイズ (一般に 4,096 バイトまたは 512 バイト) の知識を含めることで、ログとデータ ページの操作が強化されます。
トランザクションの ACID プロパティを維持するには、SQL Serverが障害ポイントを考慮する必要があります。 障害が発生した場合、多くのディスク ドライブ仕様では、セクターの書き込み操作の数が限られているだけです。 ほとんどの仕様では、障害発生時に 1 つのセクター書き込みが完了します。
SQL Serverでは、セクター サイズの倍数で 8 KB のデータ ページとログ (フラッシュされた場合) が使用されます。 (ほとんどのディスク ドライブでは、既定のセクター サイズとして 512 バイトが使用されます)。障害が発生した場合、SQL Serverは、ログ パリティと破損した書き込み手法を使用することで、セクターよりも大きな書き込み操作を考慮できます。
ページの破損検出
このオプションを使用すると、SQL Serverは、電源障害やその他のシステム停止によって発生する不完全な I/O 操作を検出できます。 true の場合、ページがディスクに書き込まれるたびに、8 KB (KB) データベース ページ内の 512 バイト セクターごとにビットが反転されます。 ページが後でSQL Serverによって読み取られたときに、ビットが間違った状態にある場合、ページが正しく書き込まれなかった場合は、ページが破損することが検出されます。 誤って書き込まれたページは回復によって読み取られる可能性があるため、回復中に破損したページが検出されます。
SQL Serverデータベース ページは 8 KB ですが、ディスクは 512 バイトのセクターを使用して I/O 操作を実行します。 そのため、データベース ページごとに 16 個のセクターが書き込まれます。 オペレーティング システムが最初の 512 バイト セクターをディスクに書き込み、8 KB の I/O 操作が完了するまでにシステムが失敗した場合 (たとえば、電源障害が原因で) ページが破損する可能性があります。 障害が発生する前にデータベース ページの最初のセクターが正常に書き込まれた場合、ディスク上のデータベース ページは更新済みとして表示されますが、成功していない可能性があります。
バッテリーを使用したディスク コントローラー キャッシュを使用すると、データがディスクに正常に書き込まれるか、まったく書き込まれていないことを確認できます。 この状況では、ページの破損検出を "true" に設定しないでください。これは必要ないためです。
注:
SQL Serverでは、ページの破損検出は既定では有効になっていません。 詳細については、「 ALTER DATABASE SET Options (Transact-SQL)」を参照してください。
ログ パリティ
ログ パリティ チェックは、ページの破損検出に似ています。 各 512 バイト セクターにはパリティ ビットが含まれます。 これらのパリティ ビットは常にログ レコードで書き込まれ、ログ レコードが取得されたときに評価されます。 ログの書き込みを 512 バイトの境界に強制することで、SQL Serverは、コミット操作が物理ディスク セクターに書き込まれていることを確認できます。
パフォーマンスへの影響
すべてのバージョンのSQL Server Win32 CreateFile 関数を使用してログ ファイルとデータ ファイルを開きます。 dwFlagsAndAttributes メンバーには、SQL Serverによって開かれたときにオプションが含まれますFILE_FLAG_WRITE_THROUGH。
FILE_FLAG_WRITE_THROUGH は、中間キャッシュを介して書き込み、ディスクに直接移動するようにシステムに指示します。 システムは書き込み操作をキャッシュすることはできますが、遅延フラッシュすることはできません。
この FILE_FLAG_WRITE_THROUGH オプションを使用すると、書き込み操作で正常な完了が返されると、データが安定したストレージに正しく格納されます。 これは、データを保証する WAL プロトコルと一致します。
多くのディスク ドライブ (SCSI と IDE) には、512 KB、1 MB 以上のオンボード キャッシュが含まれています。 ただし、ドライブ キャッシュは通常、バッテリを使用したソリューションではなく、コンデンサに依存します。 これらのキャッシュ メカニズムでは、電源サイクルまたは同様の障害ポイント間での書き込みを保証することはできません。 これらは、セクター書き込み操作の完了のみを保証します。 これが特に、破損した書き込みおよびログ パリティ検出が SQL Server 7.0 以降のバージョンに組み込まれた理由です。 ドライブのサイズが増え続けるにつれてキャッシュが大きくなり、障害発生時に大量のデータが公開される可能性があります。
多くのハードウェア ベンダーは、バッテリを使用したディスク コントローラー ソリューションを提供しています。 これらのコントローラー キャッシュは、数日間キャッシュ内のデータを維持でき、キャッシュ ハードウェアを 2 台目のコンピューターに配置することもできます。 電源が正しく復元されると、書き込みされていないデータがフラッシュされてから、さらなるデータ アクセスが許可されます。 その多くは、最適なパフォーマンスを得るために、読み取りキャッシュと書き込みキャッシュの割合を設定できます。 一部には、大きなメモリストレージ領域が含まれています。 実際、市場の特定のセグメントでは、一部のハードウェア ベンダーは、6 GB のキャッシュを備えたハイエンドのバッテリバックアップ ディスク キャッシュ コントローラー システムを提供します。 これにより、データベースのパフォーマンスが大幅に向上する可能性があります。
高度なキャッシュ実装では、システム リセット、電源障害、またはその他の障害ポイントが発生した場合に真の書き換え機能を提供できるため、コントローラー キャッシュを無効にしないことによって要求が処理 FILE_FLAG_WRITE_THROUGH されます。
キャッシュを使用しない I/O 転送は、ドライブ ヘッド、スピン レート、およびその他の制限要因を移動するために必要な機械的時間が長くなる可能性があります。
セクターの順序付け
I/O パフォーマンスを向上させるために使用される一般的な手法は、セクターの順序付けです。 頭部の機械的な動きを避けるために、読み取り/書き込み要求が並べ替えられます。これにより、ヘッドのより一貫した動きがデータを取得または格納できます。
キャッシュは、複数のログとデータ書き込み要求を同時に保持できます。 WAL プロトコルと WAL プロトコルのSQL Server実装では、ページ書き込みを発行する前に、安定したストレージへのログ書き込みをフラッシュする必要があります。 ただし、キャッシュを使用すると、データが実際のドライブ (つまり、安定したストレージに書き込まれる) に書き込まれずに、ログ書き込み要求から成功が返される可能性があります。 これにより、データ ページの書き込み要求SQL Server発行される可能性があります。
書き込みキャッシュが関与すると、データは揮発性ストレージ内にあると見なされます。 ただし、Win32 API WriteFile 呼び出しから、アクティビティSQL Server正確に表示される方法により、正常なリターン コードが取得されました。 SQL Serverまたは WriteFile API 呼び出しを使用するプロセスは、データが安定したストレージを正しく取得した場合にのみ判断できます。
説明のために、データ ページのすべてのセクターが、一致するログ レコードのセクターの前に書き込まれるよう並べ替えられているとします。 これはすぐに WAL プロトコルに違反します。 キャッシュは、ログ レコードの前にデータ ページを書き込みます。 キャッシュが完全にバッテリでサポートされていない限り、エラーによって致命的な結果が発生する可能性があります。
データベース サーバーの最適なパフォーマンス要因を評価する場合は、考慮すべき多くの要因があります。 これらの中で最も重要なのは、"システムで有効な FILE_FLAG_WRITE_THROUGH 機能が許可されていますか?
注:
使用しているキャッシュは、バッテリバックアップソリューションを完全にサポートする必要があります。 他のすべてのキャッシュ メカニズムでは、データの破損やデータ損失が発生しやすくなります。 SQL Server を有効にすることで、WAL を確保するためにあらゆる努力を行FILE_FLAG_WRITE_THROUGHいます。
テストでは、多くのディスク ドライブ構成に、適切なバッテリ バックアップなしで書き込みキャッシュが含まれている可能性があることが示されています。 SCSI、IDE、および EIDE ドライブは、書き込みキャッシュを最大限に活用します。 SSD がSQL Serverと連携する方法の詳細については、次の CSS SQL Server Engineers ブログ記事を参照してください。
SQL Serverと SSD - RDORR の学習ノート - パート 1
多くの構成では、IDE または EIDE ドライブの書き込みキャッシュを正しく無効にする唯一の方法は、特定の製造元ユーティリティを使用するか、ドライブ自体にあるジャンパーを使用することです。 ドライブ自体の書き込みキャッシュが無効になっていることを確認するには、ドライブの製造元にお問い合わせください。
SCSI ドライブには書き込みキャッシュもあります。 ただし、これらのキャッシュは通常、オペレーティング システムによって無効にすることができます。 質問がある場合は、ドライブの製造元に問い合わせて適切なユーティリティを確認してください。
書き込みキャッシュスタッキング
書き込みキャッシュスタッキングは、セクターの順序付けに似ています。 次の定義は、主要な IDE ドライブの製造元の Web サイトから直接取得されました。
通常、このモードはアクティブです。 書き込みキャッシュ モードでは、バッファーがいっぱいになるまで、またはホスト転送が完了するまで、ホストがバッファーにデータを書き込みます。
ディスク書き込みタスクが開始され、ホスト データがディスクに格納されます。 ホスト書き込みコマンドは引き続き受け入れられ、書き込みコマンド スタックがいっぱいになるか、データ バッファーがいっぱいになるまで、データがバッファーに転送されます。 ドライブは、ドライブのスループットを最適化するために書き込みコマンドを並べ替える場合があります。
自動書き込み再割り当て (AWR)
データを保護するために使用されるもう 1 つの一般的な手法は、データ操作中に不良セクターを検出することです。 次の説明は、一流の IDE ドライブ製造元の Web サイトに由来します。
この機能は書き込みキャッシュの一部であり、遅延書き込み操作中のデータ損失のリスクを軽減します。 ディスク書き込みプロセス中にディスク エラーが発生した場合、ディスク タスクは停止し、疑わしいセクターはドライブの末尾にある代替セクターのプールに再割り当てされます。 再割り当て後、ディスク書き込みタスクは完了するまで続行されます。
これは、キャッシュにバッテリ バックアップが提供されている場合に強力な機能になる可能性があります。 これにより、再起動時に適切な変更が提供されます。 ディスク エラーを検出することをお勧めしますが、WAL プロトコルのデータ セキュリティでは、これを遅延方式ではなくリアルタイムで行う必要があります。 WAL パラメーター内では、AWR 手法は、セクター エラーが原因でログの書き込みが失敗したが、ドライブがいっぱいになっている状況を考慮できません。 データベース エンジンは、トランザクションを正しく中止し、管理者に警告を表示し、データをセキュリティで保護し、メディアエラーの状況を修正するための正しい手順を実行できるように、エラーをすぐに把握する必要があります。
データの安全性
データベース管理者は、データの安全性を確保するためにいくつかの予防措置を講じる必要があります。
- 致命的な障害から復旧するには、バックアップ戦略で十分であることを確認することをお勧めします。 オフサイト ストレージやその他の予防措置が適切です。
- セカンダリ データベースまたはテスト データベースのデータベース復元操作を頻繁にテストします。
- キャッシュ デバイスが、すべての障害状況 (停電、不良セクタ、不良ドライブ、システム停止、ロックアップ、電源スパイクなど) を処理できることを確認します。
- キャッシュ デバイスを確認します。
- バッテリーバックアップを統合しました
- 電源投入時に書き込みを再発行できる
- 必要に応じて完全に無効にすることができます
- 不良セクターの再マッピングをリアルタイムで処理する
- ページの破損検出を有効にします。 (これはパフォーマンスにほとんど影響しません)。
- 可能であれば、RAID ドライブを構成して、無効なディスク ドライブのホット スワップを許可します。
- OS を再起動せずにディスク領域を追加できる新しいキャッシュ コントローラーを使用します。 これは理想的なソリューションです。
ドライブのテスト
データを完全にセキュリティで保護するには、すべてのデータ キャッシュが正しく処理されていることを確認する必要があります。 多くの場合、ディスク ドライブの書き込みキャッシュを無効にする必要があります。
注:
代替キャッシュ メカニズムが複数の種類のエラーを正しく処理できることを確認します。
Microsoft では、ユーティリティを使用して、複数の SCSI ドライブと IDE ドライブのテストを SQLIOSim 実行しました。 このユーティリティは、シミュレートされたデータ デバイスとログ デバイスに対する非同期の読み取り/書き込みアクティビティの負荷をシミュレートします。 テスト パフォーマンス統計は、書き込みキャッシュが無効なドライブと 5,200 から 7,200 の RPM 範囲のドライブの 1 秒あたりの平均書き込み操作数を 50 から 700 の間で示します。
ユーティリティの SQLIOSim 詳細については、Microsoft サポート技術情報の次の記事を参照してください。
SQLIOSim ユーティリティを使用して、ディスク サブシステム上のSQL Server アクティビティをシミュレートする方法
多くのコンピューターメーカーは、書き込みキャッシュを無効にしてドライブを注文します。 ただし、テストでは、これが必ずしも当てはまるとは限らない可能性があることを示しています。 したがって、常に完全にテストします。
データ デバイス
ログに記録されていない状況では、SQL Serverではログ レコードのみをフラッシュする必要があります。 ログに記録されていない操作を実行する場合、データ ページも安定したストレージにフラッシュする必要があります。失敗した場合にアクションを再生成する個々のログ レコードはありません。
LazyWriter または CheckPoint プロセスによって安定ストレージにフラッシュされるまで、データ ページはキャッシュに残ります。 WAL プロトコルを使用してログ レコードが正しく格納されていることを確認すると、復旧によってデータ ページが既知の状態に回復できることを確認できます。
つまり、キャッシュされたドライブにデータ ファイルを配置することをお勧めします。 SQL Serverがデータ ページを安定ストレージにフラッシュすると、トランザクション ログからログ レコードを切り捨てることができます。 データ ページが揮発性キャッシュに格納されている場合は、エラーが発生した場合にページを回復するために使用されるログ レコードを切り捨てることが可能です。 データ デバイスとログ デバイスの両方が安定したストレージに正しく対応していることを確認します。
パフォーマンスの向上
あなたに発生する可能性がある最初の質問は、「私はキャッシュしていたIDEドライブを持っています。 しかし、無効にすると、パフォーマンスが予想よりも低くなりました。 なぜでしょうか?
Microsoft によってテストされる IDE ドライブの多くは 5,200 RPM、SCSI ドライブは 7,200 RPM で実行されます。 IDE ドライブの書き込みキャッシュを無効にすると、機械的なパフォーマンスが要因になる可能性があります。
パフォーマンスの違いに対処するために、従う方法は明確です。
多くのオンライン トランザクション処理 (OLTP) システムでは、高いトランザクション レートが必要です。 これらのシステムでは、書き込みキャッシュを適切にサポートできるキャッシュ コントローラーの使用を検討し、データの整合性を確保しながら目的のパフォーマンスを向上させます。
キャッシュ ドライブのSQL Serverで発生するパフォーマンスの大幅な変更を確認するために、トランザクションレートは小さなトランザクションを使用して増加しました。
テストでは、512 KB 未満または 2 MB を超えるバッファーの書き込みアクティビティが高いと、パフォーマンスが低下する可能性があることを示しています。
次の例について考えます。
CREATE TABLE tblTest ( iID int IDENTITY(1,1), strData char(10))
GO
SET NOCOUNT ON
GO
INSERT INTO tblTest VALUES ('Test')
WHILE @@IDENTITY < 10000
INSERT INTO tblTest VALUES ('Test')
次に、SQL Serverのサンプル テスト結果を示します。
SCSI(7200 RPM) 84 seconds
SCSI(7200 RPM) 15 seconds (Caching controller)
IDE(5200 RPM) 14 seconds (Drive cache enabled)
IDE(5200 RPM) 160 seconds
一連 INSERT の操作全体を 1 つのトランザクションにラップするプロセスは、すべての構成で約 4 秒で実行されます。 これは、必要なログ フラッシュの数が原因です。 1 つのトランザクションを作成しない場合、すべての INSERT トランザクションが個別のトランザクションとして処理されます。 したがって、トランザクションのすべてのログ レコードをフラッシュする必要があります。 各フラッシュのサイズは 512 バイトです。 これには、大幅な機械的なドライブの介入が必要です。
1 つのトランザクションを使用する場合は、トランザクションのログ レコードをバンドルし、1 つのより大きな書き込みを使用して、収集されたログ レコードをフラッシュできます。 これにより、機械的介入が大幅に減少します。
警告
トランザクション スコープを増やさないようにすることをお勧めします。 実行時間の長いトランザクションは、過剰で望ましくないブロックとオーバーヘッドの増加を引き起こす可能性があります。 SQL Server:D atabases SQL Serverパフォーマンス カウンターを使用して、トランザクション ログ ベースのカウンターを表示します。 具体的には、フラッシュされたログ バイト数/秒は、高い機械的ディスク アクティビティを引き起こす可能性がある多数の小さなトランザクションを示すことができます。
ログ フラッシュに関連付けられているステートメントを調べて、Log Bytes Flushed/sec 値を削減できるかどうかを判断します。 前の例では、1 つのトランザクションが使用されました。 ただし、多くのシナリオでは、これは望ましくないロック動作を引き起こす可能性があります。 トランザクションの設計を調べます。 次のコードのようなコードを使用してバッチを実行し、頻繁かつ小規模なログ フラッシュ アクティビティを減らすことができます。
BEGIN TRAN
GO
INSERT INTO tblTest VALUES ('Test')
WHILE @@IDENTITY < 50
BEGIN
INSERT INTO tblTest VALUES ('Test')
if(0 = cast(@@IDENTITY as int) % 10)
BEGIN
PRINT 'Commit tran batch'
COMMIT TRAN
BEGIN TRAN
END
END
GO
COMMIT TRAN
GO
SQL Serverでは、「SQL Server I/O 信頼性プログラムレビュー要件のダウンロード ドキュメント」で説明されているように、システムが安定したメディアへの確実な配信をサポートする必要があります。 SQL Server データベース エンジンの入力要件と出力要件の詳細については、「Microsoft SQL Server データベース エンジン入力/出力要件」を参照してください。
フィードバック
以下は間もなく提供いたします。2024 年を通じて、コンテンツのフィードバック メカニズムとして GitHub の issue を段階的に廃止し、新しいフィードバック システムに置き換えます。 詳細については、「https://aka.ms/ContentUserFeedback」を参照してください。
フィードバックの送信と表示