SQL に関する Q&A: 最適なパフォーマンスを実現するための微調整
重複するインデックス、キャンセルしたロールバック操作、および I/O スパイクは、パフォーマンスの問題の原因になりますが、この問題は回避することが可能です。
Paul S. Randal
重複するインデックス
Q. SQL Server では、同じテーブルに重複するインデックスの作成が許可されているようです。この仕様は、ワークロードのパフォーマンスにどのように役立つのでしょうか。クエリによって、使用されるインデックスが異なっているのでしょうか。
A. SQL Server では、重複するインデックスを作成できますが、残念ながら、得られるメリットは一切ありません。それどころか、重複するインデックスを使用すると、いろいろな意味で悪影響があります。
重複するインデックスは、インデックス キーが他のインデックスとまったく同じである場合 (同じ ASC 仕様または DESC 仕様を使用して、同じ順序で指定されている場合) に作成されます。列が含まれる場合は、含まれる列についても同様です (ただし、順序は自由に指定できます)。
SQL Server でクエリに使用するのは重複するインデックスのうちの 1 つだけですが、挿入操作、更新操作、および削除操作の実行中は、テーブルに作成されている全インデックスを保持する必要があります。したがって、テーブルに対して挿入または削除の操作を行うと、その変更はすべてのインデックスに毎回必ず反映されます。更新された列がインデックスに含まれている場合、更新についても同じことが言えます。
インデックス メンテナンスでは、(事実上意味のないインデックスに対して) 余分なリソースを使用して余分なトランザクション ログ レコードを生成し、重複するインデックスにより、余分なディスクとバックアップの領域が必要になります (インデックス メンテナンスに必要なページによってもメモリ領域が必要以上に消費されます)。
重複するインデックスは断片化しやすいため、定期的にインデックスの断片化を解消するための処理でも余分なリソースが必要になります。インデックス メンテナンスと断片化の解消によって生成される余分なトランザクション ログ レコードは、データベース ミラーリングやトランザクション レプリケーションなど、高可用性 (HA) 機能のパフォーマンスを低下させる原因にもなります。
SQL Server では、重複するインデックスを作成したことに関する警告は通知されません。したがって、重複するインデックスの作成を避ける責任はユーザーにあります。重複するインデックスが存在するかどうかを確認する作業を、軽視しないでください。この作業には、すべてのインデックス定義をスクリプトで出力して、出力内容を手動で比較するか、プログラムによるシステム カタログの広範な解析が必要になります。昨年、Kimberly Tripp が、この問題に対する完全な解決策に関する記事を投稿しています。
ロールバックに注意する
Q. 先日、実行時間の長い更新操作をキャンセルしなければならないことがありました。操作をロールバックした後、最初に作成された日単位のトランザクション ログ バックアップは膨大なサイズになりました。データベースには変更を加えなかったため、バックアップのサイズは、とても小さくなることを想定していたのですが、この異常な状態について説明していただけますか。
A. これは非常によくある誤解です。大規模な操作をロールバックした場合、次に作成される差分バックアップが小さくなるというのは間違いです。
SQL Server でデータベースに変更を加えると、必ず 2 つの処理が実行されます。まず、変更内容を説明するトランザクション ログ レコードが生成されます。次に、変更によって更新された全データ ファイル ページに対して、対応するビットが差分ビットマップに設定されます。この設定は、次の差分バックアップで、これらのページをバックアップする必要があることを表しています。
操作をロールバックすると、SQL Server では、その操作による変更箇所を元に戻す必要があります。つまり、ロールバック前の操作によって生成されたすべてのトランザクション ログ レコードが調べられます。SQL Server では、変更を逆の順序で元に戻す必要があります。各トランザクション ログ レコードには、データベースに対する変更内容が、操作の一環として 1 つずつ示されています。この変更をロールバックするには、元の変更の影響を無効にする新たな変更をデータベースに加える必要があります。たとえば、レコードの挿入操作をロールバックするには、挿入したレコードを削除します。実質的な効果は、挿入したレコードが存在しなくなることです。
ここからが、非常にわかりにくい部分です。ロールバック中に行われた変更はすべて、(特殊な変更ではありますが) データベースへの新たな変更になります。トランザクション ログ レコードは、データベースに対するすべての変更について必ず作成されるため、ロールバック中に加えられた変更も、当然のこととしてログ記録されます。つまり、大規模な操作をロールバックすると、ロールバック前の操作だけでなく、ロールバックに対してもトランザクション ログ レコードが生成されます。トランザクション ログ バックアップでは、これらのトランザクション ログ レコードがすべてバックアップされます。これが、膨大なサイズのトランザクション ログ バックアップが作成された原因です。
ロールバック前の操作で、データベースの一部を変更したことにより差分ビットマップにビットが設定された場合、データベースが変更されているため、差分ビットマップのビットを再度クリアすることはできません。その変更が最終的にロールバックされてても、データ ファイル ページが変更されたことに変わりはありません (実際には、2 回変更されています)。そのため、データ ファイル ページは、差分バックアップによってバックアップが作成される必要があります。
この問題で最も重要な部分は、操作がロールバックされても、データベースが変更されたことに変わりはなく、すべてのバックアップには、この変更を反映させる必要があるということです。
スパイクを探す
Q. ある SQL Server で継続的に I/O スパイクが発生するという問題をトラブルシューティングしています。PerfMon を使用してチェックポイントを絞りましたが、どのデータベースが主な原因になっているのかわかりません。どのようにトラブルシューティングを進めればいいでしょうか。
A. チェックポイントが存在する理由は 2 つあります。1 つ目の理由は、トランザクション ログに書き込まれた内容でデータ ファイル ページを更新できることです。SQL Server では、先書きログというメカニズムを使用します。このメカニズムでは、データベースに対する変更が、データ ファイルに反映される前にトランザクション ログに記録されます。このメカニズムにより、クラッシュした場合に変更が維持されることが保証されます。2 つ目の理由は、変更されたデータ ファイル ページを、(各データ ファイル ページが変更されるたびに出力するのではなく) 一定の間隔で出力することで、常時発生する I/O の負荷を下げられることです。
チェックポイントは、各データベースで個々に発生し、復旧間隔 (SQL Server が、前回のチェックポイントから、約 1 分間 (既定) でクラッシュから回復するのに十分なトランザクション ログが生成されていることを認識する時間) を含むさまざまな要因に基づいてトリガーされます。
この数は、生成された何万件ものトランザクション ログ レコードの数と同じです。トランザクション ログ レコードによって変更されたデータ ファイル ページの数が増えるにつれて、データベースのチェックポイントで発生する I/O の量も増加します。
チェックポイントは、SQL Server の Buffer Manager パフォーマンス オブジェクトに用意されている "Checkpoint pages/sec" カウンターを使用して追跡できます。このカウンターでわかるのは、SQL Server インスタンスの全データベースにおけるチェックポイントの合計数だけです。その時点で "チェックポイントが発生している" データベースを特定するには、トレース フラグを使用する必要があります。
トレース フラグ 3502 (チェックポイント発生時に出力をトレースする)、3504 (チェックポイントに関する出力の詳細をトレースする)、および 3605 (トレースした情報をエラー ログに出力する) を有効にすると、チェックポイントが I/O スパイクの原因となっているデータベースを特定できます。
これらのトレース フラグを有効にするには、次のコマンドを使用します。
DBCC TRACEON (3502, 3504, 3605, -1)
これらのトレース フラグを無効に戻すには、次のコマンドを使用します。
DBCC TRACEOFF (3502, 3504, 3605, -1)
後続のチェックポイントが発生すると、エラー ログに次のような出力が記録されます。
2011-12-30 05:07:14.390 spid17s Ckpt dbid 21 started (8) 2011-12-30 05:07:14.390 spid17s About to log Checkpoint begin. 2011-12-30 05:07:14.390 spid17s Ckpt dbid 21 phase 1 ended (8) 2011-12-30 05:07:14.830 spid17s FlushCache: cleaned up 4307 bufs with 201 writes in 441 ms (avoided 23 new dirty bufs) 2011-12-30 05:07:14.830 spid17s average throughput: 76.30 MB/sec, I/O saturation: 198, context switches 392 2011-12-30 05:07:14.830 spid17s last target outstanding: 15, avgWriteLatency 2 2011-12-30 05:07:14.830 spid17s About to log Checkpoint end. 2011-12-30 05:07:14.830 spid17s Ckpt dbid 21 complete
これで、チェックポイントが発生しているデータベースを確認し、そのデータベースと PerfMon の情報を照合できます。その後で、チェックポイント間で多くのデータが変更されている原因を突き止めたり、I/O スパイクを減らすためにチェックポイントを発生させる頻度を上げたり、I/O サブシステムの容量を増やしたりすることが可能です。
統合に関する懸念事項
Q. 私の会社では、ハードウェア コストを削減するために、可能な限り統合を行うという新しい方針が決まりました。また、ライセンス コストを削減するために、SQL Server のインスタンス数を減らすことも求められています。1 台の SQL Server で使用できる妥当なデータベースの数に関するガイドラインはありますか。
A. この質問に対する答えは、本当に "場合によりけり" です。その理由となる要因には、データベースのサイズ、実行しているワークロードの種類、データの揮発性、必要な定期メンテナンスの種類、および障害回復と高可用性の要件があります。
各 SQL Server インスタンスのメモリには、処理中のデータ ファイル ページを格納する有限の領域 (通称、バッファー プール) があります。SQL Server のインスタンスに、処理が必要なワークロードを持つデータベースが多いほど、バッファー プールの領域を確保するためのワークロード間の競合も多くなります。
この状況は、バッファー プール メモリのスラッシングを引き起こすおそれがあり、ディスクから読み取る新しいデータ ファイル ページの領域を確保するため、絶えずチャーンが発生します。また、許容範囲を超えた待ち時間を伴う読み取り I/O も多く発生します。これらの要素は、すべてワークロードのパフォーマンスが低下する原因になります。
さまざまなワークロードでデータベースの変更を伴う場合は、定期的なチェックポイントによる書き込み I/O が発生します。多数のデータベースが 1 つのインスタンスに統合されている場合は、複数のチェックポイントが同時に発生することがあり、書き込み I/O の待ち時間が生じる可能性があります (チェックポイントの処理速度が遅くなり、ワークロードのパフォーマンスがさらに低下します)。
データベースの数が多いと、定期的なデータベースのメンテナンスでも問題が生じます。各データベースについて、インデックスと統計のメンテナンス、整合性チェックの実行、およびバックアップが必要な場合、各データベースが競合してサーバーにかかる I/O 負荷が増えないように、すべてのデータベースの処理をスケジュールするのは困難です。
インスタンス上に存在するデータベースの数が多いほど、SQL Server のネイティブな高可用性テクノロジを使用してすべてのデータベースを保護することは難しくなります。管理容易性の観点だけから見ても、I/O サブシステムのレベルでなんらかのレプリケーション テクノロジが必要になる可能性が高いでしょう。つまり、サーバー統合によるコスト削減を相殺する追加の設備投資が必要であるということです。
統合は大きなトピックです。統合について語り尽くすには、1 つのコラムでは足りません。このコラムは、統合を慎重に進めるための十分な判断材料となりますが、その一方で、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 (英語) でフォローできます。