SQL Server にて長時間のブロッキング が行われていたSQL などを検出する方法がないかを知りたくて、ネットで色々と調べてみたのでその内容を備忘としてここに記載したいと思います。
基本的には、以下のブログを参照させていただきました。こちらで紹介されていた、後追いで長時間のブロッキング が行われていたSQL を調査する方法を実際に自分の環境で試してみたいと思います。ブロッキングとデッドロックを後追い確認する方法 at SE の雑記
blocked process threshold サーバー構成オプションを設定する
まずは事前準備として、blocked process threshold サーバー構成オプションを設定します。これを設定しておくことで、ある閾値 （秒）を超えてブロッキング が発生した場合にイベントを生成することができます。
blocked process threshold サーバー構成オプション - SQL Server | Microsoft Docs
以下のとおり、blocked process threshold サーバー構成オプションを設定変更します。今回は、10秒間ブロッキング が発生した場合にイベントを生成する設定としています。
sp_configure ' show advanced options ' , 1
GO
RECONFIGURE
GO
sp_configure ' blocked process threshold ' , 10
GO
RECONFIGURE
blocked process threasholdサーバー構成オプション変更
Blocked Process Report イベントクラスを使用して、ブロッキング の情報を取得する
上記までで、blocked process threshold サーバー構成オプションを設定しました。上記でイベントが発生するようになりましたが、それをキャプチャするためには別途設定が必要です。
Blocked Process Report イベントクラスと呼ばれるものを使用してブロッキング の情報を取得します。今回は拡張イベントを使って、Blocked Process Report イベントクラスを使用し、ブロッキング のイベントを取得してみます。
Blocked Process Report イベント クラス - SQL Server | Microsoft Docs
SSMSのオブジェクトエクスプローラ ーから、「管理」-「拡張イベント」-「セッション」を右クリックし、「新規セッション」を選択します。
拡張イベント設定① ベントラ イブラリから「blocked_process_report」を選択します。今回はこれで、「OK」ボタンを押下して作成完了です。
拡張イベント設定②
ブロッキング を発生させて、拡張イベントで確認してみる上記手順まででほぼ準備は完了です。これからブロッキング を発生させて確認していきますが、その前に拡張イベントのセッション欄に追加で表示されたセッションを右クリックして、「ライブデータの監視」を選択します。
拡張イベント設定③ 以下のように情報を取得する画面が表示されます。これで準備はすべて完了しました。ブロッキング を発生させましょう。
拡張イベント設定④ SQL シートを作成し、以下の通り更新SQL を実行します。
ブロッキング を発生させる①もう一度新しいクエリでSQL シートを作成し、以下の通り更新SQL を実行します。同じ行を更新しているので、あとから実行しているこちらのUPDATE文はブロッキング されます。このまま、10秒程度待ちます。
ブロッキング を発生させる②ブロッキング の情報になります。
拡張イベントでブロッキング 情報を確認する① 中身を簡単に見てみましょう。「bloked_process」フィールドをダブルクリックするとXML でブロッキング の細かい情報を見ることができます。
拡張イベントでブロッキング 情報を確認する② 上記の情報を確認することで、ブロックしているSQL やブロッキング されているSQL どちらも確認することができます。
おわりに
いかがでしたでしょうか。私自身作業してみて、とても簡単にブロッキング 情報を取得することができたと思いました。今回はリアルタイムに確認しましたが、拡張イベントはファイル出力することができるので、後追いで問題のあるSQL などを確認することができると思います。
ここらへん上手く活用して、実際の運用作業に役立てることができそうです。
