チューニングに大変革をもたらす動的管理ビュー：Dr. K's SQL Serverチューニング研修 Part II（2）（2/2 ページ）

» 2007年09月10日 10時00分公開

動的管理ビューを活用してWait事象を解明する

　SQL Server 2000のパフォーマンス・チューニングでは、sysperfinfoコマンドを使ってオブジェクト単位のパフォーマンス・カウンタを一定間隔で取得して、それをExcelの表に落とし込み、値がどう変化したか差分を調べていました。

　SQL Server 2005でも、SQL Server 2000との互換を保つためにsysperfinfoという仮想テーブルは残っており、「sys.dm_os_performance_counters」という動的管理ビューを見れば同じ結果が得られます。この機能を使って、システムのある2つの時点のカウンタを取り、変化した値をExcelで計算します。この結果、どのオブジェクトがどの程度変更されたかで、パフォーマンスの変化を知ることができます。これが動的管理ビューの基本的な使い方です。

　表1で示したとおり、SQL Server 2005では実に多くの内部情報が見られるようになっています。この結果、SQL Server 2000ではブラックボックスだったデータベースエンジン内部の動きは、ほぼ全部知ることができるようになりました。これはパフォーマンス・チューニングを手掛けるエンジニアにとって、大きな変化であり進化といえます。

　なぜそこまでいい切るかというと、今回公開された内部情報というのは、SQL Server 2000までは公式ドキュメントにさえ載っていない関数を使わない限り、知り得ないものだったからです。つまり一般のエンジニアは、本格的なパフォーマンス・チューニングの領域には手出しができなかったのです。例えばロックやラッチが原因でWait事象が発生しているのは分かっても、さらに深い部分で「どういう種類のロックで待ちが生じているのか」「共有ロックなのか排他ロックなのか」といったレベルになると、マイクロソフト社内の人間以外には全然分からなかったのです。しかし、SQL Server 2005になってからは、それらも明確なカウンタとして参照できるようになりました。

　ロックの詳しい内容を見るためには、「sys.dm_os_wait_stats」という動的管理ビューを用います。この階層まで降りていくと、ロックの内部がどうなっているかまでが見えるようになります（表2）。ここまでいくと、「何が根本的な原因なのか」というサーバ全体にかかわる問題が見えてきます。これらもすべて動的管理ビューのおかげです。


列名	データ型	説明
wait_type	nvarchar(60)	待機の種類の名前。
waiting_tasks_count	bigint	この待機の種類における待機の数。このカウンタは、待機が開始するたび増加します。
wait_time_ms	bigint	この待機の種類における総待機時間（ミリ秒単位）。この時間には、signal_wait_timeの開始時刻と終了時刻が含まれます。
max_wait_time_ms	bigint	この待機の種類における最大待機時間。
signal_wait_time	bigint	待機スレッドがシグナルを受け取ってから実行を開始するまでの時間。
表2　sys.dm_os_wait_statsのスキーマ MSDN2の「sys.dm_os_wait_stats」から転載。SQL Server 2005からこのような詳細なデータが公開され、日本語のドキュメントも用意された。

　このsys.dm_os_wait_statsで参照できる値は、SQL Server 2000までは非公開だったため、私としては守秘義務もあり皆さんにお教えしたくてもできませんでした。ところが実は、これこそボトルネックがどこにあるのかをサーバ全体から見るのに最も有効な情報源なのです。

　これを使って、冒頭でお話ししたような“start to end”の値を取って比較することができます（図1）。デモの開始と終了の値を取ってExcelで計算して比較するのです。ここで最も注意して見なくてはならないのは、処理待ちでどれくらいの間タスクが止まっているのかを示す「wait_time_ms」です。

図1　“start to end”を比較したWait事象

　さらに注目すべきは、待機の種類（wait_type）の「WRITELOG」です。公式ドキュメントには、

「ログフラッシュの完了を待機しているときに発生します。ログフラッシュの原因となる主な操作としては、チェックポイントとトランザクションのコミットがあります。」

と書かれています。ログというのは先行書き込みを行いますから、データベースに対してアプリケーションから更新命令を発行した段階で「before image」と「after image」、Oracleでいうと「undo」と「redo」ですが、この2種類の情報をリアルタイムでログファイルに書き込むのです。ここが遅いとログの書き出しがボトルネックになってしまうわけです。

　実際のチューニングでは「sys.dm_os_wait_stats動的管理ビューでwait_time_msの値が大きい待機の種類を調べたところ、WRITELOGであることが判明した」という流れになるでしょう。次は、どこを直せばこの値を減らせるかです。このカウンタの裏には何が隠れているのか、どういうリソースのボトルネックがあって、このカウンタの値が上がっているのか、これを知るにはひとえに経験が求められますが、少なくとも動的管理ビューによって原因を速やかに特定できるようになったわけですから、エンジニアには朗報といえるでしょう。

　注目すべき2つ目の待機の種類は「LCK_M_X」です。公式ドキュメントには、

「タスクが排他ロックの取得を待機しているときに発生します。ロックの互換性のマトリックスについては、『sys.dm_tran_locks』を参照してください。」

と書かれています。具体的にどういうシチュエーションかというと、自分より先に誰かが共有ロックを掛けてテーブルを検索している場合です。この場合、当該行は排他ロックが掛かっていて更新できません。更新がかけられるのは、誰もその行の排他権を持っていないときに自分がそれを取得できて、なおかつ自分がコミット命令を出すまでは更新中の中身は誰も読めない状態、つまりほかの人が排他制御を受けている場合だけです。

　SQL Server 2000までのパフォーマンスモニタではロックの全体しか見えなかったものが、SQL Server 2005ではロックの粒度が見えるのです。wait_typeの「LCK_」という接頭辞に続く「M_X」の部分が粒度を示しています。ここが「M_IX」になるとインテント排他ロックになります。

　また、前シリーズの「排他制御メカニズムから“待ち”原因を究明する」で紹介したラッチもパフォーマンスを劣化させるWait事象の1つですが、ある一定の時間内にラッチの待ちが何回発生したかというのも、すべて動的管理ビューで確認できるようになりました。ラッチはwait_typeに「LATCH_」の接頭辞が付き、「UP」は更新ラッチ、「SH」は共有ラッチなどとなります。

　詳細については公式ドキュメント「動的管理ビューと動的管理関数－sys.dm_os_wait_stats」（図2）を参照していただくとして、パフォーマンス・チューニングに取り組むエンジニアはこういうロックの粒度やラッチを知らないといけません。「そんなに細かく知る必要があるの？」といささかうんざり顔の方もいるかもしれませんが、パフォーマンス・チューニングを究めようと思うなら、ここまでとことん深く掘り下げる必要があることを肝に銘じておいてください。

図2　MSDN2の「sys.dm_os_wait_stats」から「待機の種類の一覧」をキャプチャ

動的管理ビューはチューニング情報の宝庫

　これまで紹介したように、動的管理ビューはこれまでブラックボックスだったパラメータやカウンタといったものをオープンにしました。SQL Server 2000では、チューニングに必要な重要情報がどこにもドキュメントとして存在していないということは珍しくありませんでした。しかしSQL Server 2005ではほぼ全部が公開され、Books Onlineなどで読むことができます。これらのデータによって、例えば「I/Oサブシステムのこの部分の負荷が高いから、データベースを分割した方がよい」といった判断を、客観的かつ確実な数値で判断することが可能になるのです。

　動的管理ビューは、メモリの増設を行う際に役に立つでしょう。また、ディスクI/Oの状態を調べれば、データベースの物理ファイルをどう分ければ最も効率のよい負荷分散になるか判断できます。さらにこれからは、使われているインデックスと使われていないインデックスがひと目で見分けられるため、これまでのようにムダなインデックスを付けてしまい、結果としてパフォーマンスが落ちるといったことも解消できるようになるのです。

　さて、ここまでSQL Server 2005の動的管理ビューに関する全体像を概観してきました。次回からはいよいよ各項目を個別に深く掘り下げていきたいと思います。

　その手始めとして、第3回は「メモリ」に焦点を当てて説明していく予定です。メモリプールのカウンタを、動的管理ビューを用いてチューニング前と後を比較・分析するといった方法や、表面的な事象をバックエンドで動いている動的管理ビューのどのカウンタにひもづけて見るのか……といったことをお話ししたいと思っています。

　概論だった今回までと異なり、内容も一段と深くなってきますが、ぜひ一緒に頑張って勉強していきましょう。どうぞ次回もお楽しみに！（次回へ続く）

監修者プロフィール

熊澤幸生（くまざわ・ゆきお）
株式会社CSK Winテクノロジ執行役員兼技術推進担当。メインフレーム環境で20年近くデータベース関連のITプロジェクトを数多く経験。また1979年から1983年まで米国に駐在し、データ主導型システム設計を実プロジェクトで学ぶ。1994年、アスキーNT（現、CSK Winテクノロジ）設立に参加し、SQL Server Ver 4.2からSQL Server 2000までシステム構築、教育にかかわってきた。現在同社執行役員 Chief Technology Officer。また、SQL Serverユーザー会「PASSJ」の理事として活動中。