「DMV（Dynamic Management View）」でパフォーマンス遅延の「原因」を調べる：SQL Serverトラブルシューティング（39）

本連載は、「Microsoft SQL Server」で発生するトラブルを「どんな方法で」「どのように」解決していくか、正しい対処のためのノウハウを紹介します。今回は、「処理遅延の原因を追求する“DMVの使い方”」を説明します。

[内ヶ島暢之，ユニアデックス株式会社]

連載バックナンバー

　本連載では、「Microsoft SQL Server（以下、SQL Server）」で発生するトラブルについて、「なぜ起こったか」の理由とともに具体的な対処方法を紹介していきます。

　前回は、SQL Serverでパフォーマンス遅延の問題が起きたときのアプローチと情報収集の方法を解説しました。「処理遅延の課題解決に必要な3つのポイント」を押さえ、「パフォーマンスログ」「トレースログ」を正しく採取することで、何から対策すべきかの「当たり」を付けられます。今回は、その先のステップである「原因と証拠をつかむ」ために必要な手順を紹介します。

SQL Serverの内部動作を調査する「DMV（Dynamic Management View）」とは

　SQL Serverには、パフォーマンス問題に限らず、その内部動作を調査するための「DMV（Dynamic Management View）」と呼ばれるツールが用意されています。

　DMVは、データベースに実装されている状況を可視化するツールです。DMVで表示される情報を「SELECTステートメント」で検索することで、データベース内の動作状況を視認できます（図1）。

　参考までに、同じような情報を取得する機能に「DMF（Dynamic Management Function）」もあります。DMFはDMVと違い、関数の形で情報を採取する仕組みとなりますが、本稿では説明をシンプルにするためにこのDMFもDMVの一部として扱います。

図1　DMVで表示したユーザーセッションの情報。SSMS（SQL Server Management Studio）からインスタンスに接続し、「SELECT文」を実行することで情報を採取できる

　DMVでは、SELECT文を実行した「瞬間の情報」を確認できます。時系列で値の変化を調べたいときには、繰り返しSELECT文を実行して情報を取得します。ただし、VIEWの項目によっては、インスタンス起動後からの「累積値」になっているものもあるので注意してください。

　DMVは、単体で個別に調査するよりも、複数のDMVを併用する、あるいはシステムテーブルと結合すると使い勝手がよくなります。例えば、「典型的な調査クエリ」を保存しておき、併用するようにすると便利です。

DMVで「sys.dm_os_wait_stats」を実行し、「遅延要因が何か」を探る

　今回はDMVの活用例として、タスクの待ち時間（wait time）の情報を確認する「sys.dm_os_wait_stats」の情報を詳細に見てみましょう。sys.dm_os_wait_statsを実行すると、次のような結果が表示されます（図2）。

図2　DMVでsys.dm_os_wait_statsを実行し、トータル待ち時間（wait_time_ms）の長い順に並べ替えてみたところ

　図2の実行結果から、SQL Serverが起動してから最も長く待ち時間が発生していたタスクは「SLEEP_TASK」だったことが分かりました。

　SQL Serverにおける遅延調査においては、まずsys.dm_os_wait_statsのカウンター値をリセット（実施方法は以下を参照）し、その後しばらく遅延状態を続けた後でsys.dm_os_wait_statsにクエリを実行するのがコツです。そうすることで、「遅延発生中に絞った」状態で、待ち時間が多く発生したタスクが何かを特定できます。例えばディスクI/Oに関わる待ち時間が長かったならば、そのI/Oに特化した調査をさらに進めていく、という流れです。

sys.dm_os_wait_statsのカウンター値をリセットする方法

　カウンター値のリセットには「DBCC SQLPERF(’sys.dm_os_wait_stats’,CLEAR)」コマンドを実行します（図3）。

図3　「DBCC SQLPERF(’sys.dm_os_wait_stats’,CLEAR)」コマンドを入力すると、sys.dm_os_wait_statsのカウンター値をリセットできる

　このようにsys.dm_os_wait_statsは、パフォーマンス問題に関する調査における初期段階の方向性を定めるのに有効です。この他にsys.dm_os_wait_statsで確認できる待ち情報として、以下のイベントにも注目するとよいでしょう（表1）。

（表1）sys.dm_os_wait_statsで得られる情報のうち、特に注目しておくべきイベント

イベント名	説明	チェック項目
CXPACKET EXCHANGE	SQL Server内部で発生する並列処理に関する待ち時間の情報	これが「大きい」場合、クエリプランが妥当ではない可能性がある
LATCH_XX	データベースの内部構造を保護するために用いられる構造体「ラッチ」の待ち時間の情報。「XX」には、EXやSHなど取得が“排他モード”なのか、“共有モード”なのかを示す文字列が入る（以下同）	どのような内部待ちだったのかについて、さらなる詳細な調査を行うための基礎情報として認識しておく
LCK_M_XX	ロックに関する待ち時間の情報	ロックによる待ち時間が長いときは、クエリプランの妥当性、または想定していない処理によるブロッキングを疑う
PAGELATCH_XX	データベースのページをメモリ上で操作しているときにページを保護するために用いられるラッチについての情報	ページラッチによる待ち時間が長いときには、特定ページへのアクセス頻度を調査する
PAGEIOLATCH_XX	データベースのページをディスクから読むまたは書き出す際に用いられるラッチについての情報	ページI/Oラッチによる待ち時間が長いときには、I/Oの高負荷やディスクの過負荷などを調査する

　今回はDMVの特徴と「sys.dm_os_wait_statsの使い方」を解説しました。DMVはこの他にもさまざまな調査で使えるツールです。この他の使い方については、今後の具体的なシナリオ例の中で紹介していく予定です。次回はパフォーマンス問題を考える上で必要な統計情報について紹介する予定です。

筆者紹介

内ヶ島 暢之（うちがしま のぶゆき）

ユニアデックス株式会社 NUL System Services Corporation所属。Microsoft MVP Data Platform（2011〜）。OracleやSQL Serverなど商用データベースの重大障害や大型案件の設計構築、プリセールス、社内外の教育、新技術評価を担当。2016年IoTビジネス開発の担当を経て、2016年現在は米国シリコンバレーにて駐在員として活動中。目標は生きて日本に帰ること。

椎名 武史（しいな たけし）

ユニアデックス株式会社所属。入社以来 SQL Serverの評価／設計／構築／教育などに携わりながらも、主にサポート業務に従事。SQL Serverのトラブル対応で社長賞の表彰を受けた経験も持つ。休日は学生時代の仲間と市民駅伝に参加し、銭湯で汗を流してから飲み会へと流れる。