SQL に関する Q&A: ログとインデックスの管理
SQL Server を効率的に実行し続けるには、ログ バックアップの保持とインデックスの適切な管理が重要になります。
Paul S. Randal
チェーンが途切れないようにする
Q. データベースのバックアップ戦略を策定しています。この戦略にはトランザクション ログのバックアップも含まれているため、データをほとんど損失することなく、障害回復を実行できます。直面する可能性があるいくつかの問題について調査したところ、ログ バックアップ チェーンが途切れないように注意する必要があるという記述を何度か目にしました。ログ バックアップ チェーンがどういうもので、どのようにしたら途切れる可能性があるのかを、教えていただけませんか。
A. これは良い質問ですね。多くの方が見落とす問題です。ログ バックアップ チェーンとは、最新のデータ バックアップ (完全バックアップまたは差分バックアップ) から、復元する時点までを対象とする連続した一連のトランザクション ログ バックアップです (ログ バックアップ チェーンは、単にログ チェーンと呼ばれることもあります)。復元シーケンスの例は次のとおりです。
- データベースの最新の完全バックアップを復元する
- 次に、データベースの最新の差分バックアップを復元する
- 最後に、すべてのトランザクション ログ バックアップを復元する
多くの場合、いずれかのバックアップが破損して、最新ではないデータ バックアップの復元が必要になる場合に備えて、なるべく多くのトランザクション ログ バックアップを保持しています。バックアップと復元に関する詳細については、私が昨年執筆した 2 つの TechNet Magazine のコラム「SQL Server のバックアップについて」と「バックアップを使用して障害から復旧する」を参照してください。
復元シーケンスに必要なログ バックアップが破損していたり、使用できない場合、ログ バックアップ チェーンは途切れていることになり、その時点より前の状態に復元することはできません。ログ バックアップが 1 つだけ破損している場合は、WITH CONTINUE_AFTER_ERROR オプションを使用して、強制的に復元できることがあります。このオプションでは、破損したトランザクション ログのレコードを強制的に復元するので、データベースの破損を招くおそれがあります。そのため、このような復元を強制的に行うのは、あまり気が進みません。
必要なログ バックアップが使用できない原因となる操作の 1 つには、ログ バックアップが保持されることを確認しないで実行される、"帯域外" のログ バックアップがあります。このバックアップは、開発者にデータベースのコピーを提供する際に実行することがあります。このログ バックアップはログ バックアップ チェーンの構成要素ですが、1 つ前のログ バックアップ以降に生成されたログ レコードしか含まれません。
ただし、WITH COPY_ONLY オプションを使用する場合は話が別です。このオプションを使用すると、ログ バックアップを実行して、次のログ バックアップでも、同じ一連のログ レコードをバックアップできるようになります。バックアップ チェーンが途切れるのを回避する方法の詳細については、私のブログ記事で「BACKUP WITH COPY_ONLY (WITH COPY_ONLY を使用したバックアップ、英語)」を参照してください。
ログ バックアップ チェーンが途切れる原因となる操作として、より一般的な例は、通常操作でトランザクション ログ バックアップを実行できないようにするものです。これには、次のような操作があります。
- SIMPLE 復旧モデルに切り替えてから、FULL または BULK_LOGGED に戻す
- BACKUP LOG ... WITH NO_LOG または TRUNCATE_ONLY オプションを使用して、SQL Server 2005 およびそれ以前のバージョンでログをダンプする
- データベース スナップショットからデータベースを復元する
このような操作を実行した後には、データのバックアップ (完全バックアップまたは差分バックアップ) を実行して、引き続きログ バックアップが作成されるようにする必要があります。これは、ログ バックアップ チェーンの再開と呼ばれます。
最後に一言。一般的な認識とは裏腹に、完全バックアップや差分バックアップを実行しても、ログ バックアップ チェーンが途切れることはありませんし、実際のところ、いかなるログ バックアップにも影響を与えることはありません。
インデックスをクラスター化する
Q. 社内で使用している SQL Server 2008 データベースの多くのテーブルには、クラスター化インデックスを作成していません。追加の I/O 処理が発生する転送済みレコードが原因で、パフォーマンス上の問題が発生する可能性があると聞いたことがあります。これはどのようにして確認できるもので、どう対処すべきなのか、教えていただけませんか。
A. ヒープは、クラスター化インデックスを含まないテーブルです。ヒープには元来秩序がありません。ヒープの転送済みレコードとそれらがどのように使用されるのかについてご存じない場合は、私のブログ記事「Forwarding and forwarded records, and the back-pointer size (転送中および転送済みレコードと戻りポインターのサイズ、英語)」を参照してください。ヒープの転送済みレコードにより、クエリの処理中に、追加の I/O 処理がランダムに発生し、その結果、パフォーマンスが低下します。
転送済みレコードを処理しているクエリが存在するかどうかを確認するには、Access Methods パフォーマンス オブジェクトの Forwarded Records/sec パフォーマンス カウンターを確認するのが最も簡単です。それから、データベースの一部のテーブルに対して、DETAILED モードで sys.dm_db_index_physical_stats 動的管理関数を実行すると、出力の forwarded_record_count 列の値として、各テーブルの転送済みレコードの数が返されます。詳細については、Books Online のこのトピックを参照してください。
転送済みレコードを削除する際、クラスター化されたインデックスを作成して、再び削除するのは最も良くない方法です。この操作を行うと、テーブル上のすべての非クラスター化インデックスは自動的に 2 回再構築され、リソースが大量に浪費されることになります。詳細については、私のブログ記事「What happens to non-clustered indexes when the table structure is changed? (テーブルの構造が変更されると非クラスター化インデックスはどうなるか、英語)」を参照してください。
ヒープに含まれる転送済みレコードを永続的に削除して回避するには、クラスター化インデックスを作成するのが最も簡単です。このコラムでは、ほとんどの場合、ヒープではなくクラスター化インデックスを使用する必要がある理由を説明するために、「クラスター化インデックスとヒープ」について議論をするつもりはありません。詳細については、私の妻、Kimberly Tripp のブログ記事「Clustering Key (クラスター化キー、英語)」シリーズを参照してください。クラスター化インデックスの使用を評価することをお勧めします。
テーブル レコードのサイズが大きくなり、十分な領域がなくなると、転送済みレコードが発生することがあります。そのため、転送済みレコードを回避するには、レコードのサイズが変化しないようにする方法もあります。たとえば、可変長列には既定の値を使用します。
SQL Server 2008 では、ヒープを再構築できる ALTER TABLE ... REBUILD という新しいステートメントが導入されました。このステートメントは、ALTER INDEX ... REBUILD ステートメントを使用して、インデックスを再構築できるのと同じように機能します。このステートメントは、データ圧縮機能をサポートするために追加されたものですが、この目的のためにも使用できます。詳細については、Books Online のこのトピックを参照してください。
インデックス メンテナンス
Q. インデックス メンテナンス ルーチンを変更して、オンラインのインデックス再構築を使用するようにしましたが、メンテナンス ルーチンを実行すると、ブロッキングの問題が発生することがあります。なぜこのような問題が発生するのでしょうか。オンラインのインデックス操作ではロックは使用されず、ブロッキングが発生するはずはないと思っていました。これは想定される動作なのでしょうか。それとも、何か操作を間違っているのでしょうか。
A. これは想定される動作です。操作の開始時には、初期化している間 (これは非常に短時間で完了するプロセスです)、共有されるテーブルをロックする必要があります。このロックはすぐに解除されます。このロックは、他のロックと同じようにキューに入れる必要があり、再びロックを許可して解放するまで、新しいクエリでテーブルに変更を加えられないようにします。
現在実行中の変更クエリの処理がすべて完了するまで、このロックを取得することはできません。ワークロードによって異なりますが、かなり時間がかかる可能性があります。つまり、オンラインのインデックス操作の最初の段階で、ブロッキングが発生することがあります。
操作を完了するには、操作の最後で、スキーマ修正ロック (排他的ロック) を取得する必要があります。これも非常に短時間で完了します。このロックは、すぐに解除されます。このロックでは、ロックが許可および解放されるまで、テーブルに対するあらゆる種類の新しいクエリ (読み取りまたは書き込み) が回避されます。
繰り返しになりますが、SQL Server で現在実行中のすべての読み取りまたは書き込みのクエリが完了するまで、このロックを取得することはできません。つまり、ブロッキングが発生する可能性があるということになります。
まとめると、機能の名前はオンラインのインデックス操作ですが、ブロッキングの問題を引き起こす、短時間のロックが 2 回必要になります。従来のオフラインのインデックス操作のメリットは、大多数のインデックス操作ではロックが保持されないので、全体的な同時実行性が向上します。これらの操作の動作に関する詳細については、ホワイト ペーパー「SQL Server 2005 のオンラインのインデックス操作 (英語)」を参照してください。
インデックス メンテナンス時間を短縮する
Q. 通常のインデックス メンテナンス ジョブで、大量の I/O 処理を伴い、ジョブの実行に時間がかかるシステムをいくつか引き継ぎましたが、インデックスは断片化されていないので、インデックスの再構築は実行していません。パフォーマンス上のメリットがないので、実行する処理量の削減を検討しています。役に立つ戦略を教えていただけませんか。
A. これは非常にありふれた問題です。この問題は、インデックス メンテナンス ジョブで、再構築または再構成するインデックスを判断する方法が原因で発生します。
ほとんどのユーザーは、データベースのすべてのインデックスに対して、先ほど言及した sys.dm_db_index_physical_stats 動的管理関数を実行して、インデックスを再構築するか、再構成するか、または何も実行しないかを選択します。また、出力に対して WHERE 句を使用して、avg_fragmentation_in_percent、page_count、および avg_page_space_used_in_percent の値に基づいて判断しています。
問題は、インデックスの断片化が、他の統計データのようにメモリに格納されないことです。この関数では、断片化の程度を判断するために、各インデックスを読み取って処理する必要があります。データベースに作成されたインデックスの大半が、静的であったり、(断片化の面では) 非常にゆっくり変化したりする場合、インデックスの再構築または再構成は実行されません。インデックス メンテナンス ジョブを実行するたびに断片化の状態を確認するのは、実際のところ、時間の無駄です。
ほとんどの動的管理ビューでは "述語のプッシュダウン" がサポートされており、WHERE 句の述語に一致するデータのみが処理されます。ただし、sys.dm_db_index_physical_stats は関数で、ビューではないので、この処理を実行することはできません。つまり、手動でフィルターを実行して、断片化され、再構築または再構成しなければならない可能性があるとわかっているインデックスのみを処理するように関数に要求する必要があります。
断片化は、数週間という期間をかけて監視することをお勧めします。そうすれば、インデックスをすべて確認しなくても、断片化を確認する必要があるインデックスを把握できます。このインデックスの一覧を取得したら、テーブル名、インデックス名、および操作を実行するかどうかを決める断片化のしきい値を含むテーブルを作成します。パフォーマンスに影響を与える断片化の程度は、インデックスによって異なることがあります。これは "基本テーブル" となり、このテーブルを使用してインデックス メンテナンス ジョブを実行します。テーブルに含まれるすべてのインデックスをループ処理して、テーブルに含まれるインデックスに対してのみ sys.dm_db_index_physical_stats 関数を実行します。
私はこのテーブルをいくつかのクライアントに実装しました。その結果、数時間かかっていたインデックス メンテナンス ジョブの実行時間が、15 分以下に短縮されたものもありました。これは単純に、この関数を静的なインデックスで実行しないからです。また、さらに 1 歩踏み込んで、インデックスが再構築され、インデックスの FILLFACTOR の設定が自動的に変更される可能性がある頻度を追跡することもできます。うまくいけば、インデックス メンテナンス ジョブで実行する処理がさらに削減されます。
インデックス メンテナンスを実行するさまざまな方法の詳細については、私のブログ記事「Importance of index maintenance (インデックス メンテナンスの重要性、英語)」 を参照してください。また、関数の裏側で実行されている処理の詳細な説明については、私のブログ記事「Inside sys.dm_db_index_physical_stats (sys.dm_db_index_physical_stats 関数について、英語)」を参照してください。
今月のコラムの技術校閲者を務めてくれた SQLskills.com の Kimberly L. Tripp に感謝します。
.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 の開発時にはコア ストレージ エンジンを担当していました。Paul は障害回復、高可用性、およびデータベース メンテナンスの専門家であり、世界中のカンファレンスで定期的に講演を行っています。彼のブログは、SQLskills.com/blogs/paul (英語) で公開しており、Twitter は Twitter.com/PaulRandal (英語) でフォローできます。