SQL に関する Q&A: トレースなし
バックアップと復元のプロセスや整合性チェックでは予期しない動作が発生することがありますが、このような現象が発生するのには理由があります。
Paul S. Randal
厳密な復旧計画
Q. 障害復旧計画の一環として、いくつかの SQL Server インスタンスのダウンタイム要件に取り組んでいます。バックアップの復元にかかる時間だけを考慮すれば十分でしょうか。
A. いいえ。考慮すべき点は他にもいくつかあります。まず、すべての必要なバックアップの復元にかかる総時間を考えます。これには、データベースの最新の完全バックアップ、最新の差分バックアップ、およびすべてのトランザクション ログのバックアップが含まれます。次の完全バックアップを作成する前にデータベースが破損するという最悪のシナリオを想定して、考え得る最大限の数のログ バックアップがあること場合を考えます。
次に、データとトランザクション ログ ファイルが存在していない場合、これらを作成するために完全バックアップを復元するのにかかる時間も加えて考えます。ファイルの瞬時初期化を有効にしている場合、データ ファイルは、ほぼ瞬時に作成されます。ただし、トランザクション ログ ファイルはゼロで初期化する必要があります。
数百 GB を超える大きなファイルの復元には、数時間かかることがあります。その後、差分バックアップを復元する必要がある場合には、再度トランザクション ログ ファイルを完全にゼロで初期化する必要があります。この時間も考慮に入れる必要があります。一時的に追加された (削除されていない) トランザクション ログ ファイルがある場合には、これらのファイルも同様にゼロで初期化する必要があります。つまり、ファイルを 2 回ゼロで初期化しなければならない可能性があります。
データ ベースの復元プロセスの最終手段は、クラッシュ回復を実行することです。このプロセスに必要な時間は、ロール バックする必要があるトランザクション ログ レコードの数によって異なります。トランザクション ログ レコードは、最後のログ バックアップが行われたときにコミットされていないトランザクションの一部です。データベースに実行時間の長いトランザクションがある場合は最悪の事態を想定する必要があります。考え得る最長のトランザクションをほぼすべてロール バックする必要があると考えてください。この時間をバックアップの復元にかかる総時間に追加する必要があります。
最後に、バックアップの復元を開始できるようにする物理サーバーの準備にかかる時間を考える必要があります。つまり、Windows のブート (POST の実行、メモリ確認など) とスタートアップにかかる時間です。これもダウンタイムに追加する必要があります。
これらすべてについて最悪の事態を想定すると、考え得る最長のダウンタイムがわかります。すべて盛り込むと、その時間の長さに驚くことになると思います。
中断しない
Q. 最近、興味深い問題に直面しました。通常より実行に時間がかかっている DBCC CHECKDB プロセスを中断しようとしましたが、中断することができず、プロセスが完了するまで長時間待つ必要がありました。何が起こっていたのか説明していただけますか。
A. この動作は想定されているものですが、直感的に理解できるものではありません。DBCC CHECKDB プロセスの開始時には、データベースの隠しスナップショットが作成されます。データベースのスナップショットは、トランザクションの一貫性が維持された不変のデータベースの状態を DBCC CHECKDB に提供するために必要です。このスナップショットにより、DBCC CHECKDB は、破損箇所を修復する必要がある静的データベースの一貫性を確認できます。
まず、このプロセスでは、データベースのチェックポイント処理を行うことで、データベースのスナップショットを作成します。次に、空のデータベースのスナップショットを作成し、データベースのトランザクション ログを使用して、このデータベースのスナップショットに対してクラッシュ回復を実行します。つまり、実際のデータベースに影響することなく、あらゆるアクティブなトランザクションをデータベースのスナップショットにロール バックします。その結果、データベースのスナップショットではトランザクションの一貫性が保たれます。
データベースのスナップショットを作成しながらクラッシュ回復を実行するのにかかる時間は、データベースのスナップショットの作成を開始するときに、データベースにあるコミットされていないトランザクションの量と長さに比例します。実行時間の長いトランザクションがある場合、ロール バックに時間がかかることがあります。つまり、データベースのスナップショットの作成と DBCC CHECKDB プロセスの実行に長い時間がかかります。
極端な場合、データベースのスナップショットの作成に通常より時間がかかっていることが原因で、DBCC CHECKDB プロセスの終了を実行したとしても、すぐには何も起こらないことがあります。データベース スナップショットのクラッシュ回復が完了するまで、プロセスは終了の要求に応じません。また、クラッシュ回復は中断できません。SQL Server では、予期しないシャットダウン後の実際のクラッシュ回復に使用するクラッシュ回復コードと、データベース スナップショットに使用するクラッシュ回復コードが区別されません。
この場合に使用できる唯一の代案は、SQL Server のインスタンスを再起動して、隠しデータベースのスナップショットを削除することです。この方法は、通常のデータベースで実行する実際のクラッシュ回復では機能しません。このような場合、インスタンスを再起動した後もクラッシュ回復のプロセスは継続して実行されます。
このシナリオを回避する方法はいくつかあります。データベースで実行時間が長いアクティブなトランザクションがないときにのみ、DBCC CHECKDB を実行します。DBCC CHECKDB の隠しデータベースのスナップショットを作成するプロセスの一環として、これらのトランザクションをロール バックする必要が生じます。他のサーバーにデータベースを復元して、復元したコピーの整合性を確認する整合性チェックのメカニズムも使用できます。このメカニズムを使用すると、実行時間が長いトランザクションに直面する可能性を回避できます。
正確な復元ポイントを見つける
Q. 先週、だれかが間違って削除したテーブルを復旧するために、バックアップを復元する必要がありました。既定のトレース ログでは、テーブルが削除された日時についての情報が失われていたため、戻すべき復元ポイントを見つけるのは面倒な作業でした。それほど時間をかけずに、正確な復元ポイントを見つける方法はありますか。
A. テーブルが削除された日時を特定するときには、既定のトレース ログ ファイルを確認します。既定のトレースは、データ定義言語 (DDL) のイベントについてのデータで構成されています。詳細については、「default trace enabled サーバー構成オプション」を参照してください。
既定のトレースに関する唯一の問題は、サイズに制限があることです。さらに、SQL Server 2012 では、この機能が非推奨になり、拡張イベントに置き換えられています。このため、SQL Server 2012 で多くのアクティビティが発生している場合、テーブルが削除された日時のレコードはトレース ログに残っていない可能性があります。
つまり、テーブルが削除された日時を特定する唯一の方法は、私が「トランザクション ログの地道な解析」と呼んでいる方法を行うことです。テーブルが存在していたことがわかっているポイントにデータベースのコピーを復元します。WITH STOPAT オプションと WITH STANDBY オプションを使用して、繰り返し特定のポイントへの復元を実行します。復元を実行するたびに少しずつ復元するポイントを前に進めます。テーブルが存在しないポイントを見つけたら、このポイントの直前のポイントにデータベースを復元して、テーブルのデータを復元できます。
このプロセスは非常に退屈な作業で、時間がかかります。WITH STANDBY オプションを使用してデータベースを復元するたびに、そのポイントでコミットされていないすべてのトランザクションは、UNDO ファイルにロール バックされます。少し先のポイントに復元する次の復元で、前の状態に戻すトランザクションを元に戻して、少しデータベースを復元し、コミットされていないトランザクションを UNDO ファイルに再度ロール バックします。正確なポイントを見つけるまでこのプロセスを繰り返し行う必要があります。
実際には、この操作を簡単に行う別の方法があります。トランザクション ログのバックアップにあるログ レコードを解析し、DROPOBJ という名前のトランザクションを見つけます。これには、ドキュメントに記載されていない fn_dump_dblog という名前のテーブル値関数を使用します。この関数は、より広く知られている fn_dblog 関数と同じように動作し、アクティブなトランザクション ログからログ レコードをダンプし、データベース バックアップに対して動作します。
この関数は、お探しのテーブルを削除したトランザクションを見つけるために使用できます。次に、トランザクションのログ シーケンス番号 (LSN) を使用し、WITH STOPBEFOREMARK = 'lsn:<トランザクションの LSN>' を実行して、指定した LSN の直前のトランザクション ログを復元できます。このオプションを使用すると、テーブルを削除したトランザクションの直前までトランザクション ログが復元されます。この方法を使用すると、先ほど紹介したような「トランザクション ログの地道な解析」を行う必要がなくなります。この関数の詳細と使用方法については、私のブログ (英語) を参照してください。
イベント フィルター処理
Q. SQL Server 2012 では、SQL トレースの使用が推奨されていないので、拡張イベントについて理解したいと思っています。拡張イベントが SQL トレースよりも軽量になった理由を説明していただけますか。
A. 2 つのメカニズムのパフォーマンスの違いの主な原因は、イベントをフィルター処理する方法にあります。トレースまたはイベント セッションを定義するとき、どちらの場合もさまざまなイベントの条件に基づいてイベントをフィルター処理できます。特定のデータベースをアクティビティでフィルター処理するのが、この良い例です。
SQL トレースの場合、イベントは常時生成されます。イベント コンシューマーによってフィルター処理が行われます。つまり、使用されないイベントがあるにもかかわらず、すべてのイベントを生成する負荷が SQL Server にかかります。このプロセスはとても非効率です。
拡張イベントの場合、SQL Server の拡張イベント エンジンがイベントのフィルター処理を行います。拡張イベント エンジンは、イベント セッションが定義されたときに指定された述語を評価します。つまり、イベントが発生したとき、ベースとなるイベントのデータを集める際に最小限の作業で済むので、拡張イベント エンジンでは述語を評価できるようになります。述語が false と評価されると、イベントは直ちに破棄されます。拡張イベント エンジンでは、これ以上の処理は行いません。SQL トレースと比較した場合、この仕様により、イベント収集のパフォーマンスのオーバーヘッドが最小限に抑えられます。
また、SQL トレースでは、イベントに関連付けられたすべての列を収集し、必要でない列は破棄されます。一方、拡張イベントでは、指定された列と他のデータのみを収集します。そのため、イベントを発生させるために必要な作業がさらに少なくなります。
拡張イベントはトラブルシューティングのデータの収集には優れたメカニズムですが、注意深くイベント セッションを構築しないと、SQL Server のパフォーマンスに悪影響を与えるおそれがあります。よくあるイベント (ロックの取得やスレッド待機など) が発生するたびにイベント セッションで T-SQL コール スタックを生成する必要がある場合、これは明らかにパフォーマンスに影響します。
どちらのメカニズムを使用する場合も、実際に運用する前にイベント コレクションのテストを実施して、ワークロードのパフォーマンスに悪影響を及ぼさないようにする必要があります。
.jpg)
Paul S. Randal は SQLskills.com の代表取締役であり、Microsoft Regional Director でもあり、SQL Server MVP でもあります。1999 年から 2007 年までは、マイクロソフトの SQL Server ストレージ エンジン チームに所属していました。また、SQL Server 2005 では DBCC CHECKDB/repair コードを記述し、SQL Server 2008 の開発時にはコア ストレージ エンジンを担当していました。Randal は障害回復、高可用性、およびデータベース メンテナンスの専門家であり、世界中のカンファレンスで定期的に講演を行っています。彼のブログは、SQLskills.com/blogs/paul (英語) で公開しており、Twitter は twitter.com/PaulRandal (英語) でフォローできます。