SQL Serverで長時間のブロッキングを確認する方法

SQL Serverにて長時間のブロッキングが行われていたSQLなどを検出する方法がないかを知りたくて、ネットで色々と調べてみたのでその内容を備忘としてここに記載したいと思います。

基本的には、以下のブログを参照させていただきました。こちらで紹介されていた、後追いで長時間のブロッキングが行われていたSQLを調査する方法を実際に自分の環境で試してみたいと思います。
ブロッキングとデッドロックを後追い確認する方法 at SE の雑記

まずは事前準備として、blocked process threshold サーバー構成オプションを設定します。これを設定しておくことで、ある閾値（秒）を超えてブロッキングが発生した場合にイベントを生成することができます。

以下のとおり、blocked process threshold サーバー構成オプションを設定変更します。今回は、10秒間ブロッキングが発生した場合にイベントを生成する設定としています。

sp_configure 'show advanced options', 1 
GO
RECONFIGURE
GO
sp_configure 'blocked process threshold', 10
GO
RECONFIGURE

上記の変更が正しく行うことができていたか以下の通り確認します。上記で指定したとおり、値が10秒となっていますね。

上記までで、blocked process threshold サーバー構成オプションを設定しました。上記でイベントが発生するようになりましたが、それをキャプチャするためには別途設定が必要です。

Blocked Process Report イベントクラスと呼ばれるものを使用してブロッキングの情報を取得します。今回は拡張イベントを使って、Blocked Process Report イベントクラスを使用し、ブロッキングのイベントを取得してみます。

SSMSのオブジェクトエクスプローラーから、「管理」-「拡張イベント」-「セッション」を右クリックし、「新規セッション」を選択します。
以下のような画面が開きますので、セッション名を任意で入力し、「セッションの作成後すぐにイベントセッションを開始する」をチェックしておきます。

次は、ページの選択で「イベント」を選択し、イベントライブラリから「blocked_process_report」を選択します。今回はこれで、「OK」ボタンを押下して作成完了です。

上記手順まででほぼ準備は完了です。これからブロッキングを発生させて確認していきますが、その前に拡張イベントのセッション欄に追加で表示されたセッションを右クリックして、「ライブデータの監視」を選択します。

以下のように情報を取得する画面が表示されます。これで準備はすべて完了しました。ブロッキングを発生させましょう。

新しいクエリでSQLシートを作成し、以下の通り更新SQLを実行します。

もう一度新しいクエリでSQLシートを作成し、以下の通り更新SQLを実行します。同じ行を更新しているので、あとから実行しているこちらのUPDATE文はブロッキングされます。このまま、10秒程度待ちます。

すると、先ほど「ライブデータの監視」で表示した画面に何やら行が追加で表示されました。これが今回色々と設定したおかげで取得できたブロッキングの情報になります。

中身を簡単に見てみましょう。「bloked_process」フィールドをダブルクリックするとXMLでブロッキングの細かい情報を見ることができます。

上記の情報を確認することで、ブロックしているSQLやブロッキングされているSQLどちらも確認することができます。

いかがでしたでしょうか。私自身作業してみて、とても簡単にブロッキング情報を取得することができたと思いました。今回はリアルタイムに確認しましたが、拡張イベントはファイル出力することができるので、後追いで問題のあるSQLなどを確認することができると思います。

ここらへん上手く活用して、実際の運用作業に役立てることができそうです。
最後までお読みいただきありがとうございました。今回はこの辺で失礼いたします。

ITエンジニアの成長ブログ