SQL Server では、「待ち事象」の情報を確認することでボトルネックの解析の一助とすることができます。
クエリを実行し、処理を実行する際には、次の状態を推移しながら処理が進められます。
「待機状態」に入っている処理については、何らかの処理の実行を阻害する要因があり、実行効率が低下している状態となっていると考えられます。
(阻害する要因はハードウェアリソースの性能限界や、ロックの競合のような論理的な要因等、様々なものがあります)
SQL Server では、DMV やパフォーマンスモニターで待ち事象 (待機状態に入っていた状態) の情報を取得することができますが、それぞれの情報が「どのようなタイミングでカウントアップされるか」ということを意識しておくことは重要なポイントの一つとなります。
それでは、各情報の取得と、待ち事象がカウントアップされるタイミングについて見ていきたいと思います。
今回はロック競合を発生させることで「待機状態」を作り出しています。
ロック競合による待機状態への遷移は実際のワークロードでも発生する頻度が高いのではないでしょうか。
待ち事象が解消したタイミングでカウントアップされる情報
SQL Server で待ち事象を確認する方法としては、代表的なものとしては、sys.dm_os_wait_stats から情報を取得する方法があります。
この DMV では様々な待ち事象の発生状況を確認することができます。
sys.dm_os_wait_stats の各待ち事象の情報がカウントアップされるタイミングですが「待ち事象が解消されたタイミング」で、カウントアップされます。
現在、ロックによる待ちを発生させている状態なのですが、ロックの待ち事象については情報がカウントアップされていない状態です。
SELECT * FROM sys.dm_os_wait_stats WHERE wait_type LIKE 'LCK_M%' ORDER BY wait_time_ms DESC
sys.dm_os_wait_stats の DMV ですが、「待ち事象が解消されたタイミング」で情報の集計が行われます。
次の画像はロック競合が解消されたタイミングで取得されたものなのですが、先ほどまでは確認ができなかった「LCK_M_X」が発生して「いた」ことが確認できますね。
このように「待ち事象が解消したタイミング」で取得される情報は、DMV ではなく、パフォーマンスモニターでも同様なものがあります。
待ち事象の情報はパフォーマンスモニターの「SQLServer:Wait Statistics」からも取得することができます。
この中の、「Average wat tme (ms)」については、「待ち事象が解消したタイミング」でカウントアップされるものとなりますので、DMV から取得可能な情報と等価となってきます。
そのため、パフォーマンスモニターでかカウンターが上昇したタイミングは「待ち事象が発生していたタイミング」ではなく、「待ち事象が解消したタイミング」となります。
待ち事象が解消したタイミングでカウントアップされる譲歩については「待ち事象によってどの程度待機状態に推移した状態を維持していたか」というような「影響を受けていた時間」を算出するために利用できるのではないでしょうか。
待ち事象が発生したタイミングでカウントアップされる情報
「待ち事象が解消したタイミング」でカウントアップされる情報については、「現在発生中の待ち事象を確認する」というような、リアルタイム性は低い情報となります。
それでは、「待ち事象が発生している状態かどうか?」ということを取得したい場合の方法も考えてみましょう。
これにはいくつかの方法がありますので、本投稿でも複数種類で紹介してみたいと思います。
動的管理ビューで見る場合は、sys.dm_exec_requests や sys.dm_os_waiting_tasks から取得してみるという方法があります。
(想定するケースによっては、これらの DMV 以外から取得するアプローチもあります)
SELECT session_id, start_time, command, blocking_session_id, wait_type, wait_time, last_wait_type, wait_resource FROM sys.dm_exec_requests WHERE status = 'suspended' SELECT * FROM sys.dm_os_waiting_tasks WHERE resource_description IS NOT NULL
これらの動的管理ビューでは「実行中のタスク (クエリ)」の情報について取得することができます。
待ち事象が発生している場合は、「どのような待ち事象が発生」しており「待ち事象によって何秒待機された状態なのか」が取得できます。
これにより「待ち事象が発生しているクエリの情報」について確認することができます。
パフォーマンスモニターからも情報を確認することはできます。
先ほど使用した「SQLServer:Wait Statistics」を使用した場合は、「Waits in progress」が、該当の待ち事象により待機が発生しているタスクの数となりますし、「Waits started per second」が「待ち事象が発生した」タスクの数となります。
そのほかにも、「SQLServer:General Statistics」の「Process blocked」を確認することで、何個のプロセスがブロッキングされているかということを確認することもできますので、ここから待ち事象が発生しているプロセスの数を取得するということもできます。
これらの方法を取得することで「現在、どの程度のタスクで処理が待機状態となっているか」を確認することができます。
まとめ
SQL Server では処理が待機状態となっている「待ち事象」の情報を取得するための方法が何種類も提供されています。
取得される情報は、
- 待ち事象が解消されたタイミングで集計される情報
- 待ち事象が発生していることを示す情報
の 2 種類に大別されます。
自分が確認している情報が「どの状態で集計される情報なのか?」を意識することは、待ち事象を正しく読み解くうえで重要となります。
「情報の示す意味」ということを考えて、取得した情報を扱うことで、正しい SQL Server の状態の把握につながります。