SQL Serverを使ったシステムのオンコール担当者が知っておくと便利だと思うDMVクエリの使い方をまとめてみました。もちろんオンコール担当者じゃなくてもトラブルシューティング等に使っていただける手順かと思います。

現在実行中のクエリを実行するところから調査を出発するのがおすすめです。

チェックするポイント

1. 通常時と比べて、大量にレコードが取得されていないか

オンコール担当者がこのクエリを実行するときは、なんらかのエラーやアラートを受け取り、その原因がDBにあるのでは、と疑ったときです。
そのため、通常時と比べて大量にクエリが実行中の場合は、DBに原因があるという疑いを強めることができます。考えらえる原因は複数ありますので、よくみる原因について以下にまとめたいと思います。
逆に、通常時と比べてあまり変化が無い場合は、DB以外の原因（アプリ側等）についても考えを巡らせる必要があるかもしれません。

2. ブロッキングは起きていないか

ブロッキングが起きている場合は、head blockerを特定します。
以下のように、blk_spidに0以外の数字が表示される場合は、ブロッキングが発生しています。

Microsoft MVPの小澤さんが公開しているこちらのクエリを使うと簡単にhead blockerを特定することができます。
head blockerを特定できたら、以下の点に着目します。
・head blockerはどんなクエリか？
・head blockerのホスト名は？（誰かが手動で投げたクエリの可能性もある）
・head blockerの数は？(1つのhead blockerが大量プロセスをブロックしていることが多い）
・head blockerのstatusは？(sleepingならkillを検討）

ブロッキングが起きている場合は、以下のような情報が取得できます。

head blockerのプロセスID(spid)から、該当プロセスをさらに詳細に見たいときは以下のクエリを実行します。

sp_who2 プロセスID
dbcc inputbuffer(プロセスID)

3. 同じようなクエリが大量に取得されていないか

特定のストアドプロシージャやプリペアドステートメントのみが大量に実行中である場合は、実行プランがおかしくなっている可能性があります。該当クエリのリコンパイルにより解消される可能性があります。

■ ストアドプロシージャの場合

EXEC sp_recompile N'ストアド名';

■ プリペアドステートメントの場合

DBCC FREEPROCCACHE(sql_handle)

※plan_handleは「現在実行中のクエリ」を取得するクエリで確認できます。

4. wait_typeおよびlast_wait_typeに偏りがないか

wait_typeおよびlast_wait_typeには、クエリが現在および最後に待ち状態になったときの待ち項目が表示されます。何度実行しても同じ項目名の場合は、そのwait_typeからクエリのボトルネックを特定できる可能性が高いです。以下によくみるwait_typeと、解消のために試す価値のある行動についてまとめました。

PAGEIOLATCH_SH
　概要：物理ディスク読み込み完了待ち
　行動：実行プランが最適なものでない可能性があるため、該当クエリをリコンパイル
RESOURCE_SEMAPHORE / RESOURCE_SEMAPHORE_QUERY_COMPILE
　概要：メモリリソースの獲得待ち
　行動：実行プランが最適なものでない可能性があるため、該当クエリをリコンパイル。あわせて、このクエリを使ってメモリを大量に消費しているクエリが無いかチェックする。
SOS_SCHEDULER_YIELD
　概要：CPUリソースの獲得待ち
　行動：実行プランが最適なものでない可能性があるため、該当クエリをリコンパイル
LCK_M_* **
　概要：ロックの獲得待ち
　行動：ブロッキングチェーンの取得クエリを実行し、head blockerを突き止める
ASYNC_NETWORK_IO
　概要：プログラム側でレコードセットの処理完了待ち
　行動：この待ちは特に問題ないことがほとんどのため静観
THREADPOOL
　概要：ワーカースレッド獲得待ち
　行動：スロークエリが大量発生しているはず。ブロッキング発生状況など、なぜスロークエリが多発しているのか原因を調査する。

まとめ

現在実行中のクエリリストを取得し、現状を把握することが問題解決の第一歩だと思います。
また、注意点として例えばチューニング不足で日常的に「PAGEIOLATCH_SH」で長時間待つクエリが発生しているようなケースだと、リコンパイルではなくインデックス作成等の根本的なクエリチューニングが必要になります。今回紹介した考え方のほとんどは、あくまで「いつもは問題が起きていないサーバーで突然何らかの問題が発生した場合」に焦点を当てています。
以上、オンコール担当者の方の参考になれば幸いです。