SQL に関する Q&A: 破損を元に戻す
クエリ プランの選択、非常に低速なバックアップ、レプリケーション データベースの修復について掘り下げてみましょう。
Paul S. Randal
クエリ プランを作成する
Q. SQL Server でクエリを実行するクエリ プランを選択するとき、その時点でメモリにあるデータは考慮されますか。
A. 端的にお答えすると、クエリ プランを選択するときに、クエリ オプティマイザーでバッファー プールのコンテンツが考慮されることはありません。クエリ オプティマイザーでは、選択候補を絞るときに、さまざまなクエリ プランを評価し、妥当な時間内に特定できる最適なプランを探します。クエリ オプティマイザーでは、必ずしも最善のプランが特定されるとは限りません。プランのコンパイルに多くの時間をかけられないため、十分だと思われるプランが選択されます。
クエリ オプティマイザーでは、クエリに関連するさまざまなテーブルについて把握している必要があります。この情報は、関連するテーブルのリレーショナル メタデータで確認します。リレーショナル メタデータでは、テーブルの列、インデックス、制約、さまざまな列の値分布 (この統計が作成されている場合のみ) を定義しています。ストレージ メタデータでは、テーブルとインデックスが、どのようにデータ ファイルに格納されているのかを定義していますが、クエリ オプティマイザーでは、この情報をプランの決定に使用しません。
SQL Server では、ある特定の時点で、メモリに保持されているテーブルのデータやインデックスを把握していません。バッファー プールでは、メモリに保持されているデータベースのデータ ファイル ページを把握していますが、SQL Server のどのコンポーネントでも、何かを自動的に収集することはありません。たとえば、テーブル X のインデックス 2 の 50% がメモリに保持されているのに対して、テーブル X のインデックス 3 の 5% しかメモリに保持されていないことは判断できません。
クエリ オプティマイザーでは、メモリには何も保持されていないことを想定しているので、物理 I/O が最も少ないクエリ プランを選択する可能性が高いと言えます。物理 I/O が多いクエリ プランは、コンパイルに時間がかかり、コストが高くなります。ある SELECT 文に対応できる 2 つの非クラスター化インデックスが作成されたテーブルについて考えてみましょう。1 つ目のインデックスには、必要な列がすべてあります。2 つ目のインデックスには、必要なすべての列に加えて、いくつかの追加の列があります。
1 つ目のインデックスのインデックス レコードの数は、2 つ目のインデックスより少なくなります。つまり、データ ファイル ページあたりのインデックス行が多くなります。クエリ オプティマイザーでは、このインデックスを選択します。1 つ目のインデックスでは、クエリに対応するのに必要なインデックス レコードにアクセスするために、物理 I/O を使用してメモリに読み込む必要があるデータ ファイルのページ数が 2 つ目のインデックスを使用した場合より少なく済みます。2 つ目のインデックスは、1 つ目のインデックスよりレコードの数が多く、データ ファイル ページあたりのレコード数が少なくなります。この論理的な根拠は、コスト ベースの最適化と呼ばれ、SQL Server のクエリ プロセッサの設計のベースになっています。
ただし、2 つ目の広範なインデックスの大部分がメモリに保持されていて、1 つ目のインデックスがまったくメモリに保持されていない場合は、どうでしょうか。クエリでは、選択したインデックスをメモリに読み込むために物理 I/O が必要になります。これは、既にメモリに保持されている広範なインデックスを使用するよりも処理が大幅に遅くなります。このような場合、クエリ オプティマイザーで選択されたクエリ プランは、次善のものになります。ただし、クエリ オプティマイザーでは、メモリに保持されているものを特定できず、これは単なる例に過ぎません。
このことを考慮して、クエリ オプティマイザーでメモリに保持されているものが認識され、既にメモリに読み込まれているからという理由で、非効率なインデックスを使用するプランを生成したら、どうでしょうか。そのプランは、その状況が維持されている場合にのみ最適なものになります。別のクエリのために効率的なインデックスがメモリに読み込まれた場合、そのクエリでは次善のプランを使用することになります。クエリ プランを再コンパイルできるように、プランを無効にするには、どうしたらよいでしょうかという声が聞こえてきそうですね。
私は、以前、SQL Server チームで働いていたソフトウェアエンジニアとして、クエリ プランの選択と無効化のために、メモリに保持しているテーブルとインデックスの集計ビューを維持する設計を実現することは複雑で、困難を極めることを知っています。このような設計を実現した場合、ときどき発生するメリットのためだけに、望ましくないパフォーマンス上のオーバーヘッドが生じることになり、そのようなメリットを実際に享受できることはおそらくありません。
バッファー プールを確認することに興味がある場合は、動的管理ビュー (DMV) sys.dm_os_buffer_descriptors と SQLskills ブログのバッファー プールのセクション (英語) で紹介しているさまざまなクエリを参照してください。
時間がかかるバックアップ
Q. レポートで使用するセカンダリ データベースの提供にログ配布を使用しています。セカンダリ データベースにログ バックアップを適用すると、通常より大幅に時間がかかるという問題がどきどき発生します。この問題の原因がわかれば教えてください。
A. はい。この現象は何度か見たことがあります。レポートで使用するためにログ配布によるセカンダリ データベースを使用している場合、セカンダリ データベースでログ バックアップを復元しているときには WITH STANDBY オプションが使用されます。これは次の 3 つの手順で実行されます。
- バックアップの全ログ レコードをデータベース ログ ファイルに書き込みます。
- 復元のやり直しの部分を実行します (コミットされたトランザクションの全操作がデータベースに存在するようにするための処理です)。
- 復元の元に戻す部分を実行します (コミットされていないトランザクションの全操作がデータベースに存在しないようにするようにするための処理です)。
手順 3. では、元に戻す操作で生成されたログ レコードを UNDO ファイルと呼ばれる特別なファイルに書き込みます。つまり、データベースは読み取り専用モードになります。トランザクションの整合性も取れているので、ユーザーがデータベースにアクセスすることは可能です。ログ レコードは、データベースのトランザクション ログが変更されないようにするために、UNDO ファイルに書き込まれています。このようにすると、後続のログ バックアップを復元できるようになります。
セカンダリ データベースで復元プロセスが開始されたときに、UNDO ファイルが存在している場合には、上記の 3 つの手順の前に別の手順が実行されます。この最初の手順では、UNDO ファイルに含まれる全ログ レコードを取得して、その影響を元に戻します。この手順により、データベースは、手順 2. の処理が完了した時点の状態になります。このデータベースの状態は、WITH STANDBY オプションではなく、WITH NORECOVERY オプションを使用して 1 つ前のログ バックアップが復元された場合と同じになります。
ここで発生している問題は、復元しているログ バックアップが、実行時間の長いトランザクションを含み、ログ バックアップの末尾に到達するまでに、そのトランザクションがコミットされなかった場合に発生します。つまり、そのトランザクションは、ログ バックアップの復元プロセスで完全に元に戻す必要があります。その結果、大きな UNDO ファイルが作成され、ログ バックアップの復元に時間がかかることがあります。復元中のログ バックアップにも、コミットされていない実行時間の長いトランザクションが含まれている場合は、最悪の状況です。手順 3. の実行にも時間がかかります。
この問題は、プライマリ データベースでインデックス メンテナンスが実行され、実行時間の長い大規模なクラスター化されたインデックスの再構築の終盤にログ バックアップが完了した場合に発生することがあります。このようなログ バックアップをセカンダリ データベースで初めて復元するときには、手順 3. が復元処理になるため、通常よりも完了までに大幅に時間がかかります。
プライマリ データベースの次のログ バックアップは、インデックスの再構築が終わる直前に完了します。このログ バックアップをセカンダリ データベースに復元するときには、UNDO ファイルに記録されている操作をすべて元に戻す必要があります。その際、ログの復元が行われ、2 つ目のコミットされていないインデックスの再構築の操作を元に戻すために別の大きな UNDO ファイルが生成されます。
レポートを作成するためにセカンダリ データベースに 24 時間アクセスできる必要がある場合は、この可能性を把握しておく必要があります。その場合は、ログ バックアップを使用して、プライマリ データベースのインデックス メンテナンスの操作を注意して増強するようにします。このようにすると、セカンダリ データベースに復元しているログ バックアップには、コミットされた完全なインデックスの再構築の操作のみが含まれるようになります。
もう 1 つの方法として、ログ配布からデータベース ミラーリングに移行することもできます。データベース ミラーリングでは、ログ レコードは、プライマリ データベースからミラー データベースに絶えず送信されます。ログに記録された操作を何度も元に戻す追加の手順は発生しません。このアプローチには、レポートの作成にデータベース スナップショットを使用しなければならないという難点があります。これは複雑なトレードオフです。
修復のためのレプリケート
Q. ファイルが破損することが往々にしてあります。バックアップが破損し、修復処理が必要になることもあります。先週、レプリケーション パブリケーション データベースを修復する必要がありました。SQL Server オンライン ブックには、パブリケーション データベースの修復後には、すべてのサブスクライバーを再初期化する必要があると記載されています。この理由を説明していただけますか。
A. データベースの整合性チェック (DBCC) CHECKDB の REPAIR_ALLOW_DATA_LOSS オプション (以降、"修復" と言います) の使用を検討している場合、修復するデータベースがレプリケーション パブリケーション データベースであるかどうかをよく考える必要があります。可能であれば、修復を実行するのではなく、バックアップを使用してください。
マージ レプリケーションを使用している場合、データ操作言語 (DML) トリガーでは、パブリケーション データベースへの変更をキャプチャして、論理操作に変換します。トランザクション レプリケーションを使用している場合、データベース トランザクションのログ分析によって、パブリケーション データベースの変更内容がキャプチャされます。ログに記録された物理操作は、論理操作に変換されます。どちらの場合も、論理操作は、レプリケーション サブスクリプション データベースに適用されます。
どちらのメカニズムでも修復処理はキャプチャできません。修復処理では、データベース構造に直接的かつ物理的な変更が行われます。このような変更は、データベース構造 (データベース ページ、テーブル レコード、2 つのページ間のリンケージなど) の一貫性に関する問題を修復するために必要なものです。クエリによって、テーブルに対して挿入、更新、または削除の操作が行われるため、物理的なデータベースの変更とは異なります。
これらの修復処理を、レプリケーション サブスクライバーに適用できる論理操作に変換することはできません。修復処理で行う直接的な構造の変更に相当する変更については、Transact-SQL を使用して表現できる論理操作はありません。修復処理で、一意なクラスター化されたインデックスからデータ ページを強制的に 1 つ削除した場合 (つまり、いくつかのテーブル レコードを削除した場合) を想像してみてください。サブスクリプションは再初期化されておらず、それらのレコードは、レプリケートされたテーブルのコピーに存在しています。
後続の挿入操作によって、修復処理で削除されたレコードに対応するクラスター キーの値を持つレコードが挿入された場合、ディストリビューション エージェントでは、レプリケートされたテーブルのコピーに挿入操作を適用しようとしたときにエラーが発生します。修復処理は、サブスクリプション データベースに適用されていないので、レプリケートされたテーブルに挿入操作を適用しようとすると重複キー違反のエラーが発生します。つまり、修復処理でレプリケーション パブリケーションに含まれるテーブルが変更されると、レプリケーション サブスクリプションは無効になるため、レプリケーション サブスクリプションの再初期化が必要になります。
修復処理がレプリケーション メタデータ テーブルに影響する場合、レプリケーション パブリケーション全体の整合性が損なわれます。レプリケーションは、完全に削除して、再構成する必要があります。これがサブスクリプションを単に再初期化するよりも侵略的な処理であることは明らかです。端的に言うと、レプリケーション パブリケーション データベースの修復は可能な限り避けることをお勧めします。
.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 (英語) でフォローできます。