SQL に関する Q&A: インデックスの断片化の解消、同期中と同期済みなど

[アーティクル]
08/25/2016

SQL に関する Q&Aインデックスの断片化の解消、同期中と同期済みなど

Paul S. Randal

Q インデックスの断片化を解消すると統計にどのような影響が及ぶのかを把握できず困っています。インデックスを再構築した後で統計を作成し直すという指示もあれば、作成し直すべきではないという指示もあり、クラスタ化したインデックスを再構築すると他のインデックスにも影響が及ぶということを聞いたこともあります。知らないうちにパフォーマンスを低下させないようにしたいので、これらの点について解説をお願いします。

A 確かにこれはとても頭を混乱させる問題です。ご指摘のように、データベースのメンテナンスを包括的に行うには、インデックスの断片化を解消したり、統計を更新したりする必要があります。このコラムでは、インデックスの断片化を解消するタイミングと理由の詳細については説明しません。詳細については、「効果的なデータベースメンテナンスのヒント」を参照してください。また、どのインデックスが断片化されているのかを特定できているという前提で解説します。

混乱が生じる 1 つ目の点は、どのインデックスの断片化を解消すると統計に影響が及ぶのかということです。(ALTER INDEX … REBUILD、DBCC DBREINDEX、CREATE INDEX … WITH DROP_EXISTING を使用する) インデックスの再構築では、統計に対してフルスキャンを実行した場合と同等の更新が行われますが、(ALTER INDEX … REORGANIZE、DBCC INDEXDEFRAG を使用する) インデックスの再編成では、統計は更新されません。ただし、インデックスの断片化の解消はどちらの操作でも行われます。

再構築では統計が更新され、再編成では更新されない理由は、これらの操作で使用されるアルゴリズムに関係しています。インデックスの再構築では、インデックスを完全に把握しているため、統計を正確に更新できます。一方、インデックスの再編成では、一度に数ページ分のインデックスしか操作しないので、インデックス全体の統計を正確に更新することができません。

混乱が生じる 2 つ目の点は、インデックスが再構築された際に、どの統計が更新されるのかということです。テーブルには、一般的に、インデックスが設定された列とインデックスが設定されていない列という 2 種類の統計があります。インデックスを再構築する操作では、再構築するインデックスの統計だけが更新されます。インデックスが設定されていない列の統計については、メンテナンスプランにより手動で更新する必要があります。さらに、「効果的なデータベースメンテナンスのヒント」でも解説していますが、インデックスを再構築した後にインデックス統計を手動で更新しないように注意してください。インデックスの再構築ではフルスキャン (100%) と同等のサンプルレートが使用されるのに対して、手動の更新では 100% 未満のサンプルレートが使用される可能性があるからです。つまり、このような処理を行うと、フルスキャンした統計がサンプル統計で上書きされてしまうことがあります。

混乱が生じる 3 つ目の点は、インデックスの再構築が他のインデックスにどのような影響を与えるのかということです。インデックスの再構築の影響が及ぶのは、当該のインデックスとその統計だけです。これは、どのインデックスについても言えることです。ただし、SQL Server 2000 における一意ではないクラスタ化インデックスは例外で、この種のインデックスを再構築するとテーブル内すべての非クラスタ化インデックスが再構築されますが、この問題は SQL Server 2005 以降で解決されています。この点に関する詳しい情報は、私のブログ投稿サイト「Indexes from Every Angle」(さまざまな角度から見たインデックス) を参照してください。

要約すると、インデックスと統計のメンテナンスでは、次の処理を行う必要があります。

インデックスの再構築または再編成を行い、断片化を解消する
再構築されなかったインデックスの統計を更新する
インデックスが設定されていない列の統計を更新する

Q SQL Server 2008 を試しているときに奇妙な動作を発見しました。運用データベースのデータ圧縮を有効にして、そのデータベースのバックアップを作成し、これを Standard Edition のインスタンスで復元すると復元が失敗しました。これは想定されている動作でしょうか。もしそうであれば、これはデータ圧縮に限ったことなのでしょうか。それとも他の機能にも影響があるのでしょうか。また、すぐ失敗するのではなく、エラーが発生するまで、データベース全体の復元処理が行われているように見えるのは、なぜでしょうか。

A お気付きの動作は仕様どおりのものです。SQL Server のエンタープライズ規模の組織を対象とした機能の多くは、使用できるエディションが限られており、Enterprise Edition、Enterprise Evaluation Edition、および Developer Edition でしか使用できません。しかし、一部の機能には、その機能を使用したデータベースのバックアップを復元できる SQL Server のエディションが限られるものがあります。ご質問のデータ圧縮という機能は、この制限がある SQL Server 2008 の機能の 1 つです。

ご質問の動作については、SQL Server 2008 に限ったものではありません。SQL Server 2005 で、データベース内にパーティションテーブルやパーティションインデックスがある (明示的にパーティション分割機能が使用されている) 場合、そのデータベースのバックアップを復元できるのは前述した 3 つのエディションに限られます。ただし、SQL Server 2005 の場合には 2 つの問題があります。それは、データベース内にパーティションテーブルやインデックスが存在するかどうかを判断するのが難しい場合があることと、復元の失敗は復元操作がほぼ完了という段階まで起こらないことです。

つまり、復元が失敗するかどうかは、復元操作全体が完了するくらいまで (おそらく長時間) 待たなければ、わからないということです。その原因は、復元時の復旧処理が完了するまではデータベースのトランザクションが一貫性のある状態になっていないからで、復旧処理中にパーティションテーブルやインデックスが追加または削除される可能性があります。データベースを早急に復元できる唯一のサーバーが Standard Edition である場合、障害回復で、このような状況に遭遇すると非常に困ります。

SQL Server 2008 では、このような動作を引き起こすエンタープライズ規模の組織を対象とした機能は、データ圧縮、Change Data Capture、透過的なデータ暗号化、パーティション分割の 4 つに増えました。つまり、ここで取り上げている問題に遭遇する人が増えることになります。このため、新しい DMV (動的管理ビュー) である sys.dm_db_persisted_sku_features が追加され、データベース管理者が前述の 4 つの機能がデータベースで有効になっているかどうかを簡単に確認できるようになりました。

たとえば、データ圧縮が有効になったテーブルを含むデータベースで DMV を実行すると、以下のような結果が返されます。

SELECT * FROM sys.dm_db_persisted_sku_features;
GO
feature_name    feature_id
--------------  -----------
Compression     100

しかし、SQL Server 2008 では、復元がほぼ完了する段階になるまで、復元できないことがデータベース管理者に報告されないという問題に対処していません。実のところ、これは、復元操作の性質を考慮するとなかなか対処できない問題であると言えます。詳細については、「2008 年 10 月号の SQL に関する Q&A」を参照してください。

Standard Edition (または、さらに下位のエディション) でデータベースを復元しなければならない場合、データベース管理者は、このような機能を無効にするか、DMV を定期的に使用して非常時にデータベースを復元する際に予想外のエラーが発生しないようにする必要があります。

Q 同期データベースミラーリングを実装しました。この機能を実装すると、ミラーデータベースは一貫してプリンシパルデータベースと同期されるようになると思っていましたが、時折ミラーデータベースの状態が "同期済み" ではなく "同期中" となっていることがあります。これはミラーデータベースを設定した当初と同じ状態です。なぜこのような現象が起きるのでしょうか。

A ご質問にお答えする前に、データベースミラーリングにおける "同期中" と "同期済み" という状態について説明しましょう。端的に言えば、データベースミラーリングは、プリンシパルデータベースをホストする SQL Server インスタンスとミラーデータベースをホストする SQL Server インスタンスの間で物理的なトランザクションログレコードが随時送受信されることによって機能しています。プリンシパルデータベースにミラーデータベースへの送信待ちのトランザクションログレコードがない場合、ミラーデータベースの状態は "同期済み" となります (つまり 2 つのデータベースは同期された状態です)。プリンシパルデータベースにミラーデータベースへ送信されていないトランザクションログレコードがある場合、ミラーの状態は "同期中" となります (つまり、ミラーデータベースは、プリンシパルデータベースより古い状態です)。

ミラーデータベースを初期化する際には、データベースの完全バックアップと 1 つ以上のトランザクションログのバックアップが必要です。また、(WITH NO_RECOVERY を使用して) それらをミラーデータベースで復元する必要があります。このようにしてミラーリングが有効になると、最初のミラー化の状態は "同期中" となります。というのも、ミラーデータベースに復元されたログのバックアップが行われてから、プリンシパルデータベースでなんらかのトランザクション処理が行われた可能性があるからです。一定のワークロードがあるデータベースでは、その可能性が非常に高くなります。

ミラー化の状態が "同期中" となる時間を最小限に抑える秘訣は、ログバックアップを実行して、できる限りミラーデータベースを最新の状態にすることです。一定のワークロードがあるデータベースでは、これは非常に難しいことなので、初期化時には、ミラー化の状態が、しばらく "同期中" になりますが、ミラーデータベースの状態が最新になると、ミラー化の状態は "同期済み" になります。

その後、再度ミラーデータベースの状態がプリンシパルデータベースより古くなると、ミラー化の状態は "同期中" になります。この原因の 1 つとして、ミラーデータベースとプリンシパルデータベースがしばらくの間通信できない状態にある場合が挙げられます。また、ミラーデータベースにトランザクションログが送信されるよりも、速くプリンシパルデータベースでトランザクションログが生成されている場合も、この現象が発生することがあります。データベースミラーリングの構成にもよりますが、どちらの場合も、プリンシパルデータベースでは、トランザクション処理が継続的に行われるので、トランザクションログレコードのキュー (送信キュー) にデータが蓄積されていきます。ミラーデータベースで遅延が発生しないようにするには、このキューのデータを送信する必要がありますが、ミラーデータベースが最新の状態になるまで、ミラー化の状態は "同期中" になります。

プリンシパルデータベースとミラーデータベースの間のネットワーク接続が不安定な場合、ミラー化の状態が "同期中" と "同期済み" の間で頻繁に変更されることがあります。ミラー化の状態に関する包括的な解説は、ホワイトペーパー「SQL Server 2005 データベースミラーリング」を参照してください。

Q メインアプリケーションのデータベースに同期データベースミラーリングを設定しているのですが、監視結果によると、ミラーデータベースの再実行キューの長さは、たいてい 0 に近い値になっています。冗長なハードウェアをもっと活用する必要があるので、ミラーデータベースをレポート作成や整合性チェックに使用したいと考えています。可能であることは知っていますが、このような処理を実行する上で留意すべき問題点はありますか。

A これはよく行われている手法ですが、注意しなければならない問題が多少あります。

まず、ライセンスの問題があります。ミラーデータベースをホストしている SQL Server インスタンスのライセンスは、インスタンスがこの目的のみに使用されている場合は無償ですが、ミラーデータベースでデータベーススナップショットを作成するなど、SQL Server インスタンスでホスティング以外のことを行うのであれば、ライセンスを購入する必要があります。

また、整合性チェックに関しては、(データベーススナップショットを使用して) ミラーデータベースで整合性チェックを実行しても、プリンシパルデータベースとの整合性は保証されません。2 つのデータベース間ではトランザクションログレコードのみがミラー化されるので、プリンシパルデータベースの I/O サブシステムによって、あるページで破損が生じた場合、この破損はミラーデータベースにはミラー化されません。つまり、ミラーデータベースで整合性チェックを実行しても、このようなプリンシパルデータベースの破損ページは検出されません。

レポート作成はデータベースミラーリングの有効な利用法です。レポートを作成する際には、まず、レポートの作成対象となるデータベーススナップショットをどのように更新するのかという問題に直面します。データベーススナップショットは更新できないので、新しいスナップショットを作成して、レポート作成アプリケーションが、この新しいスナップショットに接続されるようにする必要があります。これを実現するためには、レポート作成アプリケーションに特別なロジックを追加する必要があります。また、ミラーリングフェールオーバーが発生した際に、レポート作成アプリケーションがどのように動作するのかを決定しなければならないという問題にも直面します。新しいプリンシパルデータベースに接続された状態を維持するのか、新しいミラーデータベースに接続するようにするのかを決定する必要があります。ただし、この点についての詳細は、今回のコラムで扱う範囲ではありませんので、説明を省略します。

ミラーデータベースの用途の増加に付随する主な懸念事項は、ミラーデータベースでパフォーマンスに関する問題が発生する可能性があるということです。データベーススナップショットを作成すると、ミラーデータベース内のページが初めて変更されたということになるので、追加の I/O 負荷が発生し、この変更は、データベーススナップショットにも反映される必要があります。また、その際データベーススナップショットで発生するワークロードがミラーデータベースの I/O 負荷を増大させます。というのも、データベーススナップショットから読み取られるページの多くは、スナップショットが作成されてから変化しないので、実際には、ミラーデータベースから読み取られているからです。

このミラーデータベースで発生する追加の I/O 負荷は、トランザクションログレコードの再実行の速度が低下し、バックログが発生する原因になることがあります。この場合のバックログは再実行キューと呼ばれ、フェールオーバー後にミラーデータベースをオンラインに復帰するために再生する必要があるトランザクションログの量になります。データベーススナップショットからミラーデータベースへの I/O 負荷が大きいほど、再実行キューのサイズが増加し、フェールオーバー発生時にデータベースが利用できない時間が長くなります。

ご質問のケースでは、再実行キューの長さがたいてい 0 に近い値だということなので、前述の問題を懸念する必要はないでしょう。しかし、レポートの実行能力を高めるためにデータベースの可用性を犠牲にするというジレンマに陥らないために留意しておきたい事項です。

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

その他のリソース