「待機時間」に向き合うパフォーマンスチューニング

待機時間を把握する

　第1回目となる今回は、これまでとは違うアプローチによるパフォーマンスチューニング手法について紹介します。

　SQL Server2012が登場するまでのパフォーマンスチューニングの入り口は、「クエリの実行プランを確認する」ことや「パフォーマンスログからボトルネックを発見する」といったところが定石である、と言えるのではないでしょうか。

　ご存知の方も多いと思いますがSQL Server 2012からは、拡張イベントが大きく機能拡張されています。この便利なツールを活用することで、新たな視点からパフォーマンスの改善ができるようになりました。

　新たな視点とは、クエリのライフサイクルの中でたびたび発生する「待機状態」です。

　クエリの実行が始まってから完了するまでの間には、ブロックの獲得待ち、タスクを処理するためのスレッド割り当て待ち、タスクに必要なメモリの割り当て待ちなど、さまざまなリソースを獲得するための待機状態が発生します。クエリのパフォーマンスを解析、あるいはチューニングする場合には、どのような種類のリソースをどれだけの期間待機したかを把握することはとても重要です。なぜなら、それらの待機状態を発生させない、または待機時間を短くすることは、次の図のようにクエリの実行時間を短縮することに直接つながるからです。

　SQL Server 2012 よりも前のバージョンでも、待機状態を把握するためのツールが用意されていました。

　ただ、残念なことに従来の情報ではクエリのライフサイクルを解析する際に、どうしても推測が入る余地が残っていて、完全に待機状態を把握することができなかったのです。実際の例を交えてSQL Server 2012より前のツールで判断することのできた情報を紹介します。

　ある種のクエリはメモリ内でデータの並べ替えを行う必要があります(ヒープテーブルにORDER BY句を指定する場合など)。その際に、SQL Serverが使用できるメモリリソースに余裕がないと、必要なサイズのメモリを獲得することができずに、クエリは待機状態になります。そのような場合にはSQL Serverの内部で、クエリは「RESOURCE_SEMAPHORE」というリソースを待機した状態になります。今回は「RESOURCE_SEMAPHORE」の待機状態を例として考えてみましょう。SQL Server 2012より前のリリースでは大きく分けると2つのツールで待機状態を確認することができます。

　最初のツールは、sys.dm_os_wait_stats動的管理ビューです。この動的管理ビューを参照することでSQL Serverインスタンス全体の様々な待機状態を確認することができます。

　つぎのクエリを実行すれば、SQL Serverのインスタンス全体で「RESOURCE_SEMAPHORE」の待機状態が何回発生したか(waiting_tasks_count)、あるいは(すべての処理の待機時間を)合計するとどのくらいの時間になるのか(wait_time_ms)などを出力結果から知ることができます。(注1)

-　　　クエリ
select * from sys.dm_os_wait_stats where wait_type = 'RESOURCE_SEMAPHORE'
go

-	実行結果
wait_type                 waiting_tasks_count  wait_time_ms max_wait_time_ms signal_wait_time_ms
----------------------------- ------------------------ ---------------- ---------------------- ------------------------- 
RESOURCE_SEMAPHORE                4328        111552                599                 44481
(1 row(s) affected)

　ただ、あくまでSQL Serverインスタンス全体の値が確認できるだけであって(注2)、個々のクエリと関連付けることはできません。そのため、特定のクエリのチューニングを目的として、待機時間を正確に把握するための用途には適していません。

　注1)：where句を省略すると、「RESOURCE_SEMAPHORE」以外のリソースの待機状態も確認することができます。出力されるリソース数はバージョンやサービスパックのレベルによって異なり、SQL Server 2012 RTM の場合は、700 種類弱のリソースに関する情報が出力されます。

　注2)：　SQL Server インスタンスが起動してからクエリが実行された時間までの累積値が出力されます。