SQL Serverのsp_whoisactiveについて - 不動の鳥の勉強記録

■はじめに

SQL Serverで実行しているクエリを調査するときにsp_whoやsp_who2など利用することがあると思いますが、海外ではsp_WhoIsActiveが有名だと聞いたので勉強してみました。

■sp_WhoIsActiveとは

sp_whoやsp_who2、DMVなどSQL Serverが提供されている調査情報を一括で取得する機能を持った調査用のストアドプロシージャのようです。SQL Serverが提供されている機能だと、sys.dm_exec_requestをみて、sys.db_dm_os_wait_statsをみてといったように複数の動的管理ビューの情報を個別に調査して分析を行う必要があります。sp_whoisactiveを利用すれば一括で必要な情報を取得することができます。

ソースコードは下記にあります。

github.com

また、ドキュメントは下記になります。

whoisactive.com

ドキュメントの中にはインストール方法や設計概念などいろいろ記載があるので、見ていただけたらどんなものかわかるのではないかと思います。その中からいくつかピックアップして学んだことを記載していきます。

■インストール方法

githubからwho_is_active.sqlをダウンロードして実行します。このクエリを実行すると、dbo.sp_WhoIsActiveという名前のストアドプロシージャが作成されます。
どのデータベースに作成するか？ですが、ドキュメントにもありますがデータベース特有の機能ではなく、サーバ全体の状況を把握するもののため、masterに作成するのが良いのではないかと思います。
また、実行するにはview server state権限が必要になります。sp_whoisactiveを実行したアカウントで権限がなかったら追加してください。

■実行パラメータ

sp_whoisactiveは24のパラメータで取得したい情報を細かく制御することができます。すべての状況を俯瞰してみるよりは見たい情報を抽出してみるべきという概念で作成されているとのことです。
例えばいくつかのパラメータを見てます。パラメータの制御内容はwho_is_active.sqlに記載されています。

   --Controls how sleeping SPIDs are handled, based on the idea of levels of interest
    --0 does not pull any sleeping SPIDs
    --1 pulls only those sleeping SPIDs that also have an open transaction
    --2 pulls all sleeping SPIDs
    @show_sleeping_spids TINYINT = 1,

この@show_sleeping_spidsではデフォルトが1となっており、1だとトランザクションを開いていればsleepingの状態のトランザクションも表示する設定になります。すべてのsleeping中のspidを表示したければ2に設定すれば表示することができるようになります。

    --Get associated query plans for running tasks, if available
    --If @get_plans = 1, gets the plan based on the request's statement offset
    --If @get_plans = 2, gets the entire plan based on the request's plan_handle
    @get_plans TINYINT = 0,

また、@get_plansでは、1を設定するとクエリの実行プランを取得することができます。
他にも多くのパラメータがあるので、検証を行いながら必要な情報を取得するお気に入りのパラメータが見つかるのではないかと思います。全部の情報を取得しようとすると分析も大変になってしまうので、発生している事象と調査項目は分けておくとよいと思います。

■おわりに

普段問題なくサーバが動作しているとこのような調査は不要です。ただし、時々トラブルが発生する場合があり調査の知見を必要とすることがあります。そういったときに調査方法などを事前に整理/訓練しておくことはとても重要なので、今回のsp_whoisactiveを有事の際にすぐに利用できるように検証機で検証作業を進めていきたいと思います。