次の方法で共有

  • [アーティクル]

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 で公開されています。