[SQL Troubleshooting] 第6回:ブロッキング情報を採取する (SQL Server 2000 ～ 2008 R2)

  • Article

[このエントリについて]

SQL Server のブロッキングを調査する為の情報、いわゆるブロッキング情報の採取手順を紹介します。

SQL Server サポートでは、ブロッキング発生が疑われる際に、一般的に以下の手順で情報採取をお願いしています。

[手順概要]

事前に情報採取の準備を行い、ブロッキングが発生する前に情報採取を開始し、現象発生を確認後に情報採取を停止し、出力情報を採取します。

おおまかな情報採取の流れは以下となります。

1. sp_blocker_pss80 ストアド プロシージャを作成する

2. ストアドプロシージャを定期的に呼び出すスクリプトを作成する

3. ブロッキング情報の採取を開始

4. 調査対象の処理を実行

5. 現象(クエリ遅延など)発生を確認

6. ブロッキング情報の採取を停止

7. 出力情報を採取

 

[手順詳細]

それでは、実際の手順について順を追って説明します。

1. sp_blocker_pss80 ストアド プロシージャを作成する

以下のサイトにアクセスし、sp_blocker_pss80 ストアド プロシージャ作成用スクリプトを入手します。

 

<タイトル : SQL Server 2005 および SQL Server 2000 のブロッキングを監視する方法>

URL : https://support.microsoft.com/kb/271509/ja

 

※URL の公開情報は SQL Server 2005 、SQL Server 2000 を対象としていますが、SQL Server 2008 、 SQL Server 2008 R2 においても、同一手順で情報を取得できます。

※スクリプトはサイト中の 「以下は、sp_blocker_pss80 ストアド プロシージャを作成するためのスクリプトです。 」の下に記載しています。 blocking_01

SQL Server Management Studio などから調査対象のインスタンスに接続し、入手したスクリプトを実行します。 02

注：スクリプトを実行すると下記のエラーが発生しますが、 これはスクリプトの構成上発生する可能性のあるエラーとなります。対処は必要ありませんので、無視してください。

-------------

メッセージ 2714、レベル 16、状態 3、プロシージャ sp_blocker_pss80、行 271

データベースに 'sp_blocker_pss80' という名前のオブジェクトが既に存在します。

------------- 03

スクリプト実行後、master データベースに、sp_blocker_pss80 ストアドプロシージャが作成された事を確認します。

 04

2. ストアドプロシージャを定期的に呼び出すスクリプトを作成する

メモ帳などで任意の場所に checkblk.sql ファイルを作成し、次のスクリプトを入力し、保存します。

---ここから---

WHILE 1=1

BEGIN

EXEC master.dbo.sp_blocker_pss80

WAITFOR DELAY '00:00:15'

END

GO

---ここまで---
WAITFOR DELAY に設定した間隔で sp_blocker_pss80 ストアドプロシージャが繰り返し実行されます。

現象発生の期間の間、ブロッキング情報を少なくとも 3 回程度取得したいので、例えば処理の実行時間が 60 秒の現象を調査する場合、15 秒程度に設定します。

上記クエリでは 15 秒間隔で実行するように指定しています。
-------------
WAITFOR DELAY '00:00:15'
-------------

 

3. ブロッキング情報の採取を開始

いよいよ情報採取を開始します。

先の手順 2. で作成した checkblk.sql を、SQLCMD ユーティリティもしくは OSQL ユーティリティから実行します。

対象マシン上でコマンドプロンプトを開き、以下のコマンドを実行します。

※Vista、Windows7 では「管理者として実行」でコマンドプロンプトを起動します。

 

<SQLCMD ユーティリティの場合>

sqlcmd -E -S <インスタンス名> -i <パス名>checkblk.sql -o <出力先パス名>checkblk.out -w 2000

 

<OSQL ユーティリティの場合>

osql -E -S <インスタンス名> -i <パス名>checkblk.sql -o <出力先パス名>checkblk.out -w 2000

 

実行例)

インスタンス名：Ryo01\SQLSERVER2008R2

checkblk.sql のパス：C:\Blocking\checkblk.sql

checkblk.out のパス：C:\Blocking\checkblk.out

上記条件の場合のコマンドは以下となります。

sqlcmd-E –S VRyo01\SQLSERVER2008R2 –i C:\Blocking\checkblk.sql –o C:\Blocking\checkblk.out -w 2000 07 

コマンドプロンプトは開いたままにしておきます。

 

4. 調査対象の処理を実行

ブロッキング情報の採取が開始された事を確認した上で、調査対象の処理を実行します。処理の開始時刻をメモしておきます。

5. 現象(クエリ遅延など)発生を確認

クエリ遅延など調査対象の現象が発生した事を確認します。

6. ブロッキング情報の採取を停止

コマンドプロンプトで CTRL+C キー を押し、ブロッキング情報の採取を停止します。 14

7. 出力情報を採取

ブロッキング情報の採取開始時に -o 引数で指定した出力先パスから、アウトプットファイル checkblk.out を採取します。

 13

[参考]

メモ帳などのテキストエディタで checkblk.out を開いてみましょう。

どうやら SPID55 のトランザクションが SPID 58 のトランザクションをブロックしているようです。 out_01 out_02 out_03

 

ブロッキング情報の採取手順は以上です。

 

★ SQL Server トラブルシューティング 6 回シリーズのご案内 ★

※本記事は、当初第6回でのご案内を予定しておりましたが、第5回に先行してご案内いたします。

他の記事は以下をご参照ください。
https://blogs.msdn.com/b/jpsql/archive/2012/03/30/sql-server-6.aspx 第1回 SQL Server のログ、イベントログの確認方法 (03/30 UP)
第2回 パフォーマンスログの採取方法 (04/20 UP)
第3回 パフォーマンスログの確認方法 (05/07 UP)
第4回 サーバートレースの解析方法 1 (05/18 UP)
第5回 サーバートレースの解析方法 2 (02/18 UP)
第6回 ブロッキング情報の確認方法 (★ 07/24 UP 本記事)