SE の雑記

SQL Server の情報をメインに Microsoft 製品の勉強内容を日々投稿

SQL Server の待ち事象を見るいくつかの方法

leave a comment

以前、今ほどSQL Database におけるシステムの動作傾向の把握が必要とされている時代はない という投稿を書きました。

こちらの投稿は Windows Azure の SQL Database 向けですが、SQL Server でも待ち事象を確認することができます。
ボックスの SQL Server の場合は、SQL Database と比較して、待ち事象の取得方法が複数用意されています。

今回の投稿ではどのような方法があるかを確認してみたいと思います。
なお、洋書になりますが待ち事象については、
SQL Wait Stats Joes 2 Pros: SQL Performance Tuning Techniques Using Wait Statistics, Types & Queues で待ち事象について解説されています。
Kindle 版だとかなり安いので、興味のある方は一度見てみるとよいかと思います。
# 私は英語が本気でダメダメなのですが、こんな感じかな~と思いながら最後まで眺めることはできました。

SQL Server で待ち事象を確認するための方法としては、

  • 動的管理ビュー
  • パフォーマンスモニター
  • 拡張イベント

の 3 種類が一般的かと思います。

それずれについてみていきたいと思います。

 

■動的管理ビュー


待ち事象を確認するための動的管理ビューとしては、

を使うことが多いかと思います。

基本的な情報としては sys.dm_os_wait_stats を確認し、sys.dm_os_wait_stats の LATCH_xx 系の詳細な情報を sys.dm_os_latch_stats から確認をするという流れになります。

SQL Database の場合には発生した待ち事象が表示されていましたが、SQL Server の場合はすべての待ち事象についての情報が出力されます。
# SQL Server 2012 SP1 の場合には 650 程度の待ち事象が取得できます。

SQL Server の場合には何もしていないでも wait_time_ms が増加するものがあります。
そのため、情報の解析をする場合にはこれらの待ち事象は除く必要があります。

どのような待ち事象が常に増加するかの確認については、アプリケーションからアクセスをしていない SQL Server を用意して、サービスを起動して数分放置し、待ちが増加したものを取り除くとよいかと思います。

また、動的管理ビューの待ち事象については、サービスを最後に起動してからの累積値となります。

累積値をクリアするためには以下のような DBCC コマンドを実行します。

DBCC SQLPERF ('sys.dm_os_wait_stats', CLEAR)
DBCC SQLPERF ('sys.dm_os_latch_stats', CLEAR)

 

■パフォーマンスモニター


SQL Server 関連のパフォーマンスモニターのカウンターとして、SQL Server: Wait Statistics オブジェクト があります。

先ほどの動的管理ビューでは累積値の取得となるため、特定の時間でトータルとして、どれだけ発生したかはわかるのですが、時系列に動きをみるということはできません。

どのような待ちがどのタイミングで発生したかを可視化する際にはパフォーマンスモニターを利用するとよいかと思います。

ただし、パフォーマンスモニターで取得できる項目は以下のように限定的なものとなっています。

image

 

詳細を見るためには DMV を使用する必要がありますが、 Page IO latch / Page latch / Non-Page latch waits などは CPU / メモリ / ディスク関連のパフォーマンスモニターの項目と照らし合わせながら解析を行うと有効に利用することができますので、見方の一つとして覚えておくと便利かと思います。

■拡張イベント


SQL Server 2008 以降であれば、拡張イベントが追加されています。

SSMS からの使い勝手の問題で、多用するのは SQL Server 2012 以降になると思いますが、拡張イベントでも待ち事象を確認することができます。

拡張イベントとして wait_info というイベントが含まれており、このイベントを使用することで、待ち事象を取得することができます。

image

拡張イベントを使用した待ち事象の取得の大きな特徴の一つとしては、セッション単位で待ち事象を取得できるところがあるかと思います。

フィルターとしてセッション ID を指定することで特定のセッションの待ち事象について取得することができます。

image

これにより、クエリによりどのような待ちが発生するかを確認することができます。

大量に CPU を使用するクエリをデータがキャッシュされていない状態で実行してみます。

MAXDOP の制限をしていないため、CXPACKET やデータがキャッシュされていないために PAGEIOLATCH 等の待ちが発生しています。

image

今回はライブデータの監視でみているのですが、拡張イベントで取得した結果をグループ化 / フィルタ / 集計をすることができるため取得したデータを以下のように表示することができます。

Duration は opcode が [End] の時に値が出力されるので、End をフィルタ条件として、duration の SUM を取得したものになります。

image

こちらが同様のクエリをデータがキャッシュされている状態で実行したものとなります。

image

メモリにキャッシュされているため、ディスクからのデータ読み込みの待ちは発生しなくなっていますが、並列クエリの同期待ちは継続として発生していることが、ここからわかるかと思います。

このような動作を見たい時には拡張イベントを使用すると便利かと思います。

細かなデータの見方に関しては解説は行いませんが、この辺の機能を有効に活用することで SQL Server の状態を把握する際の情報を取得することができるようになります。

Written by masayuki.ozawa

10月 15th, 2013 at 11:57 pm

Posted in SQL Server

Tagged with

Leave a Reply

*