• [アーティクル]

SQL に関する Q&Aサイズの大きいトランザクション ログ、修復を使用するタイミングなど

Paul S. Randal

質問 バックアップ中に奇妙な動作が発生するのですが、この動作について教えてください。62 GB の運用データベースを頻繁にバックアップして、アプリケーション開発者が使用するデータを更新しています。その際には、古いバックアップを削除してから、新しいバックアップを復元しています。復元したバックアップのサイズは運用データベースのサイズと同じなのでデータは同じだと思うのですが、復元処理にはバックアップ処理の数倍の時間がかかります。何が起こっているのでしょうか。バックアップより復元にかかる時間の方がかなり長くなる理由を教えてください。

回答 奇妙なことが起こっているわけではありません。状況にもよりますが、これは一般に予測される動作です。バックアップと復元にかかる時間に違いが生じるのは、それぞれの処理で実行する必要がある手順に違いがあるからです。

データベースのバックアップでは、2 つの手順を実行します。基本的には、次のように、データベースの入出力データを読み取って、バックアップ デバイスに書き込んでいるだけです。

バックアップ手順 1. データ ファイルに保存されている全データを読み取って、バックアップ デバイスに書き込みます。

バックアップ手順 2. トランザクション ログを読み取って、バックアップ デバイスに書き込みます。

必要とされるトランザクション ログの量は状況によって大きく異なりますが、実際に必要となるのは復元したデータベースを特定の時点の運用データベースと一貫性のある状態に回復するのに必要とされる量です (詳細については、sqlskills.com/blogs/paul/2008/01/31/MoreOnHowMuchTransactionLogAFullBackupIncludes.aspx のブログ記事を参照してください)。

一方、データベースの復元では、最大 4 つの手順が実行されます。これらの手順で行う処理は、単なる入出力の読み取り/書き込みよりも複雑です。

復元手順 1. データベース ファイルがない場合は、作成します。

復元手順 2. バックアップからすべてのデータとトランザクション ログを読み取って、該当データベース ファイルに書き込みます。

復元手順 3. トランザクション ログに記載されている復旧の再実行フェーズを実行します。

復元手順 4. トランザクション ログに記載されている復旧の元に戻すフェーズを実行します。

バックアップの 2 つの手順にかかる時間は、復元手順 2. にかかる時間とだいたい同じです (ただし、ハードウェアが類似していて、サーバーでユーザーによる操作が行われていないことが条件となります)。データ ファイルのサイズが大きく、ゼロで初期化する必要がある場合は、復元の手順 1. に時間がかかることがあります (これは、SQL Server 2000 で発生する動作で、SQL Server 2005 では既定の動作となっています)。

手順 1. の処理に時間がかかるのを回避するため、復元を開始する前に既存のファイルを削除しないでください。または、瞬時初期化を有効にして、ファイルが高速に作成されるようにします (詳細については、msdn.microsoft.com/library/ms175935.aspx を参照してください)。

復元の手順 3. と手順 4. では、復元したデータベースで復旧処理を実行して、トランザクションの整合性を確保しています。これは、データベースのクラッシュ復旧で行われるのと同じ処理です。復旧にかかる時間は、処理する必要があるトランザクション ログの量に左右されます。たとえば、バックアップを作成しているときに、実行時間が非常に長いトランザクションがアクティブになっていた場合は、そのトランザクションの全トランザクション ログがバックアップに含まれ、そのトランザクションで行われた一連の操作のロール バックが必要になります。

質問 運用データベースの冗長なコピーを用意するために、ログ配布かデータベース ミラーリングのどちらかを使用することを検討しています。懸念事項は、サーバー間で送信する必要があるトランザクション ログの量で、特に、毎晩行っているインデックスの再構築操作のトランザクション ログの量が心配です。ミラーリングでは、トランザクション ログではなく実際の再構築コマンドが送信され、ミラー サーバーで再構築が行われると聞きましたが、これは本当ですか。それが本当なら、一括ログ復旧モデルを使用するログ配布よりも、ミラーリングの方が優れた解決策になりますよね。

回答 あなたが聞いたことは事実ではありません。データベース ミラーリングでは、プリンシパル データベースのトランザクション ログ レコードがミラー サーバーに送信され、ログ レコードがミラー データベースで再生されます。変換やフィルタ処理などが行われることはなく、ミラー データベースで T-SQL コマンドが妨害されることもありません。

データベース ミラーリングでは、完全復旧モデルしかサポートされていないので、インデックスの再構築操作では常に完全なログ記録が行われます。また、関連するインデックスのサイズによっては、大量のトランザクション ログが生成されることがあります。その結果、プリンシパル データベース上のログ ファイルのサイズが大きくなり、ログ レコードをミラー サーバーに送信するのに必要なネットワーク帯域幅が増加します。

データベース ミラーリングはリアルタイムのログ配布と考えることができます (実際に、この名前は SQL Server 2005 の開発初期段階で、この機能名として使用されていました)。一方、ログ配布では、プライマリ データベースのトランザクション ログのバックアップがセカンダリ サーバーに定期的に配布され、セカンダリ データベースで復元されます。

ログ配布では、完全復旧モデルと一括ログ復旧モデルがサポートされています。ログの配布先データベースでインデックスの再構築操作に完全復旧モデルが使用される場合は、ミラー データベースで生成されるのと同じ量のトランザクション ログが生成されます。ただし、ログ配布を使用したデータベースのシナリオでは、データが継続的に送信されるのではなく、ログ バックアップ (または一連のログ バックアップ) が冗長なデータベースに送信されます。

インデックスの再構築中に、ログの配布先データベースで一括ログ復旧モデルが使用される場合は、最低限の量のトランザクション ログだけが生成されます。ただし、最低限のログが生成されたインデックスの再構築操作で変更した、すべてのデータ ファイルのエクステントは、次回のトランザクション ログのバックアップにも含まれます。つまり、一括ログ復旧モデルを使用した場合のインデックスの再構築をカバーするログ バックアップのサイズは、完全復旧モデルを使用した場合のインデックスの再構築をカバーするログ バックアップのサイズとほぼ同じになります。

冗長なデータベースに送信する必要がある情報の量は、ミラー データベースでのインデックスの再構築とログの配布先データベースでのインデックスの再構築のどちらもほぼ同じです。この 2 つの間で異なるのは、情報が送信される方法 (継続的に送信するか、一括で送信するか) だけです。

この 2 つの手法のどちらかを選択する場合には、他にも考慮する必要がある要因がたくさんあります (要因が多すぎるため、1 回の SQL に関する Q&A コラムでは説明できませんが)。どちらの手法を使用するかを決める前に、これらのあらゆる要因がどの程度ご自分の要件を満たしているか (許容できるデータ損失の限度やダウンタイムなど) を確認する必要があります。

質問 SQL Server 2005 を実行していますが、データベースの 1 つのトランザクション ログのサイズが増加し続けています。このデータベースは完全復旧モードで、トランザクション ログのバックアップを行っています。この方法を使用すれば、トランザクション ログのサイズが増加しなくなると思っていたのですが、どんな問題が起こっているのでしょうか。

回答 ご指摘のとおり、完全復旧モードでトランザクション ログのバックアップを作成することは重要です。ただし、トランザクション ログのサイズを増加させる要因は他にもあります。最終的には、何がトランザクション ログを必要としているのか (具体的には、トランザクション ログをアクティブにすることを必要としているのか) によって決まります。トランザクション ログのバックアップがないこと以外で、この問題を発生させる他の一般的な要因としては、レプリケーション、データベース ミラーリング、およびアクティブなトランザクションがあります。

レプリケーションでは、トランザクション ログ レコードを非同期に読み取って、トランザクションを読み込み、別のディストリビューション データベースにレプリケートします。レプリケーション ログ リーダーのタスクで読み取られていないトランザクション ログ レコードは解放することができません。ワークロードで大量のトランザクション ログ レコードが生成される場合に、レプリケーション ログ リーダーを実行する頻度の間隔を長く設定していると、多くのレコードが蓄積され、トランザクション ログのサイズが増加することがあります。

非同期のデータベース ミラーリングを実行している場合は、プリンシパル サーバーからミラー サーバーに送信されていない、トランザクション ログ レコードのバックログが作成されることがあります (データベース ミラーリングの送信キューと呼ばれます)。トランザクション ログ レコードは、正常に送信されるまで解放することができません。トランザクション ログ レコードの生成率が高く、ネットワークの帯域幅が制限されている場合 (または、その他のハードウェアの問題がある場合) は、バックログのサイズが非常に大きくなり、トランザクション ログのサイズが増加することがあります。

最後に、ユーザーが (BEGIN TRAN ステートメントを使用するなどして) 明示的なトランザクションを開始して、(DDL ステートメントや挿入操作、更新操作、削除操作などにより) なんらかの変更を行う場合は、ユーザーがトランザクションをコミットまたはロールバックするまで、生成されたトランザクション ログ レコードを保持しておく必要があります。つまり、トランザクション ログを指定して解放することができないため、その後、他のトランザクションで生成されたトランザクション ログ レコードも解放することができないということになります。たとえば、ユーザーがトランザクションを完了せずに帰宅した場合は、生成されるトランザクション ログ レコードが増加しますが解放できないので、トランザクション ログのサイズが増加し続けます。

トランザクション ログを解放できない理由は、次のように sys.databases システム カタログ ビューに対してクエリを実行して、log_reuse_wait_desc 列を調べることで確認できます。

SELECT name AS [Database], 
  log_reuse_wait_desc AS [Reason]
FROM master.sys.databases;

原因がアクティブなトランザクションにあることが判明した場合は、DBCC OPENTRAN ステートメントを使用して、そのトランザクションに関する詳細な情報を取得できます。

DBCC OPENTRAN ('dbname')

質問 REPAIR_ALLOW_DATA_LOSS はデータの破損から回復する最終手段として使用し、通常は、バックアップからデータを復元する必要があると聞きました。SQL Server 2005 の修復オプションを使用すべきではない理由と、そのように危険な機能が製品に組み込まれている理由を教えてください。

回答 最初にお知らせしておきますが、実は私が SQL Server 2005 の修復機能を記述しました。REPAIR_ALLOW_DATA_LOSS (これ以降は単純に「修復」と呼びます) の問題点は、そのしくみが明らかになっていないことです。「修復オプション」という名前は、これを実行することによってデータベースのデータが失われる可能性があることを示唆するために採用されました。通常、この機能では、破損した構造を削除し、データベース内で削除した構造を参照していたもの、または削除した構造で参照されていたものをすべて修復することによって、破損したデータベース構造を修復します。修復は、データベースの構造を一貫性のある状態に戻す最終手段として用意されたもので、ユーザー データを保存することを目的とした機能ではありません。ユーザー データを削除するためにわざわざ使用する機能でも、ユーザー データを保存するために無理に使用する機能でもありません。

これは、修復方法としては頼りないと思うかもしれませんが、修復オプションを使用しなければならないような状況では、破損を修復する最も迅速で信頼できる手段となります。障害復旧時に最も重要になるのは速さですが、もちろん正確さも要求されます。あらゆる状況で迅速かつ正確に機能することが保証される、これより複雑な修復アルゴリズムを設計することはほぼ不可能です。たとえば、修復のコードでは、2 つのインデックスに同じページまたはエクステントが割り当てられた場合の問題を解決するための複雑なアルゴリズムが採用されていますが、アルゴリズムの処理内容のほとんどは修復と修正です。

ですが、修復については、注意しなければならない問題がいくつかあります。

  • 修復では、破損した構造を削除するときに外部キー制約が考慮されません。そのため、別のテーブルとの外部キーのリレーションシップがあるテーブルからレコードが削除される場合があります。このような状況が発生したかどうかを判断するには、修復を実行した後で DBCC CHECKCONSTRAINTS を実行する以外に方法はありません。
  • 修復では、アプリケーション レベルで定義された固有のビジネス ロジックやデータのリレーションシップが考慮されない (できない) ため、削除されたデータによって、このようなロジックやリレーションシップが破損する場合があります。これについても、破損しているかどうかを判断するには、アプリケーションに組み込まれているカスタムの整合性チェックを実行する以外に方法はありません。
  • 一部の修復操作はレプリケートできません。ピア ツー ピア トポロジのパブリッシャまたはノードで修復を実行すると、トポロジ内で不整合が発生する場合があります。この不整合は手動で修正する必要があります。

このような理由から、破損を修復する場合は、修復オプションではなく、バックアップを使用することをお勧めします。しかし、データベースが破損した場合にバックアップがなく、しかしデータベースを迅速にオンラインに復旧する方法が必要である、という状況が生じる可能性は常にあるため、SQL Server 2005 では修復オプションを用意しました。

質問 最近、転職した先の会社でデータベース管理者として働いていて、いくつかのアプリケーションとそのバックエンド データベースを担当しています。その中の 1 つのアプリケーションに、更新時のパフォーマンスが非常に低いものがあります。調査した結果、そのアプリケーションで使用しているテーブルに、膨大な数のインデックスが作成されていることが判明しました。周囲の人に聞き込み調査を行ったところ、前任のデータベース管理者には、各テーブル列にインデックスを追加し、さらに、そのいくつかを組み合わせる癖があったようです。これらのインデックスがすべて必要だとは思えません。削除しても問題がないインデックスを見分ける方法はありますか。実行しているのは SQL Server 2005 です。

回答 ご指摘のとおり、多数のインデックスがパフォーマンス低下の主な原因になっていることは間違いありません。というのも、テーブルで行が挿入、更新、または削除されるたびに、すべての非クラスタ化インデックスで対応する操作が行われる必要があるからです。その結果、入出力、CPU 使用率、およびトランザクション ログの量に関して、かなりのオーバーヘッドが生じます。

SQL Server 2000 では、プロファイルを使用してクエリ プランを確認することが、使用されているインデックスを特定する唯一の方法でした。SQL Server 2005 では、インデックスの使用状況を追跡する sys.dm_db_index_usage_stats という新しい動的管理ビュー (DMV) が提供されています。

DMV では、インデックスが作成されているデータベースが起動してから、インデックスが使用されるたびに、インデックスが作成されたことと、それがどのように使用されたのかを追跡します。SQL Server を終了すると、すべてのデータベースの統計が失われ、データベースを閉じるかデタッチすると、そのデータベースの統計が失われます。考え方としては、統計出力に含まれていないインデックスは、データベースが起動している間に使用されなかったということになります。

長期にわたってインデックスの使用状況を追跡する簡単な手法は、DMV の出力の定期的なスナップショットを作成し、そのスナップショットを比較することです。忘れられがちなことですが、ビジネス サイクル全体でインデックスの使用状況を追跡する必要があります。ある特定の日にスナップショットを作成した場合、使用されていないインデックスがいくつかあるかもしれません。ですが、たとえば、月末のレポート処理を桁違いに速く実行するために、それらのインデックスが使用されている場合、それらのインデックスは削除すべきではないでしょう。ビジネス サイクルを通じて使用されていないインデックスは、空きディスク領域を増やし、パフォーマンスを向上するために、削除して差し支えないでしょう。

私のブログ記事では、DMV の定期的なスナップショットを作成する際に使用できるいくつかの単純なコードを紹介しています。詳細については、sqlskills.com/blogs/paul/2007/10/05/IndexesFromEveryAngleHowCanYouTellIfAnIndexIsBeingUsed.aspx を参照してください。

Paul S. Randal は SQLskills.com の代表取締役であり、SQL Server MVP でもあります。また、『DBCC CHECKDB/repair for SQL Server 2005』を執筆し、SQL Server 2008 の開発時にはコア ストレージ エンジンを担当していました。Paul は障害回復、高可用性、およびデータベース メンテナンスの専門家であり、カンファレンスで定期的に発表を行っています。彼のブログは SQLskills.com/blogs/paul で公開されています。