【SQLServer】ブロッキングを監視する方法

# はじめに
「Microsoft SQL Server 2005 Reporting Services」（以下、SSRS）を使ってデータの見える化を行っています。実際の実績データはOracle11gで登録しているのですが、自作のレプリケーションツールを使って定期間隔でSQL Server側に実績データのレプリケーションさせています。 このレプリケーション時に「タイムアウトに達しました。…」のエラーが発生するわけです。

「ロックが原因で発生していると思うのですが、まずは再発とその時の状況が分かるようにデッドロックとブロッキングを監視したい」との依頼を受けました。

今回は、ブロッキングの監視方法についてのみ説明します。

# 監視方法
早速、「SQLServer デッドロック ブロッキング 監視」で検索してみると、下記サイトが見つかります。

「[ブロッキングとデッドロックを後追い確認する方法 - SE の雑記](http://blog.engineer-memo.com/2012/08/30/%E3%83%96%E3%83%AD%E3%83%83%E3%82%AD%E3%83%B3%E3%82%B0%E3%81%A8%E3%83%87%E3%83%83%E3%83%89%E3%83%AD%E3%83%83%E3%82%AF%E3%82%92%E5%BE%8C%E8%BF%BD%E3%81%84%E7%A2%BA%E8%AA%8D%E3%81%99%E3%82%8B%E6%96%B9/)」
> SQL Server 2005 以降では Blocked Process Report イベント クラス を使用することができます。

ブロッキングが特定の秒数発生した場合にイベントを発生する上に、レポートには競合が発生しているクエリが出力されるので、どのクエリ間で競合が発生したのかを確認することもできて便利です。

# 設定方法
今回、SQL Server Profiler（プロファイラ）で一時的に監視するのではなく、永続的に監視をするためスクリプトを作成して使用します。

SQL Server 2012 であれば、拡張イベントの設定が GUI で簡単にできるようですが、SQL Server 2008なので下記サイトを参考に作成します。
「[SQL トレーススクリプトの作成、実行 (SQL Server 2005 ～ 2014)](https://blogs.msdn.microsoft.com/jpsql/2011/01/25/sql-sql-server-2005-2014/)」
イベントの内容を1つ1つ確認するのは大変なので「SQL Server Profiler」で作成後にsqlファイルにエクスポートするのが簡単です。

トレースファイルは「D:\sqltrace\blockprocess」に保存しています。
~~スクリプトを修正し、ブロッキングの秒数(10秒)の設定を追記しています。~~

ブロッキングの秒数(10秒)の設定は別途行います。
スクリプトの中に入れてバッチ内で実行しようとしたところ、権限エラーとなってしまいました。
一般ユーザーでも実行するには下記サイトを参考にするといいでしょう。
「[サーバーレベルの権限を必要とする操作を一般ユーザーで実施する](http://blog.engineer-memo.com/2016/04/28/%E3%82%B5%E3%83%BC%E3%83%90%E3%83%BC%E3%83%AC%E3%83%99%E3%83%AB%E3%81%AE%E6%A8%A9%E9%99%90%E3%82%92%E5%BF%85%E8%A6%81%E3%81%A8%E3%81%99%E3%82%8B%E6%93%8D%E4%BD%9C%E3%82%92%E4%B8%80%E8%88%AC%E3%83%A6/)」

```sql:sp_trace_block_process.sql
/****************************************************/
/* Created by: SQL Server 2008 R2 Profiler          */
/****************************************************/
-- Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
set @maxfilesize = 100 

-- Please replace the text InsertFileNameHere, with an appropriate
-- filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc extension
-- will be appended to the filename automatically. If you are writing from
-- remote server to local drive, please use UNC path and make sure server has
-- write access to your network share

exec @rc = sp_trace_create @TraceID output, 2, N'D:\sqltrace\blockprocess', @maxfilesize, NULL 
if (@rc != 0) goto error

-- Client side File and Table cannot be scripted

-- Set the events
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 137, 3, @on
exec sp_trace_setevent @TraceID, 137, 15, @on
exec sp_trace_setevent @TraceID, 137, 51, @on
exec sp_trace_setevent @TraceID, 137, 4, @on
exec sp_trace_setevent @TraceID, 137, 12, @on
exec sp_trace_setevent @TraceID, 137, 24, @on
exec sp_trace_setevent @TraceID, 137, 32, @on
exec sp_trace_setevent @TraceID, 137, 60, @on
exec sp_trace_setevent @TraceID, 137, 64, @on
exec sp_trace_setevent @TraceID, 137, 1, @on
exec sp_trace_setevent @TraceID, 137, 13, @on
exec sp_trace_setevent @TraceID, 137, 41, @on
exec sp_trace_setevent @TraceID, 137, 14, @on
exec sp_trace_setevent @TraceID, 137, 22, @on
exec sp_trace_setevent @TraceID, 137, 26, @on


-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint

-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1

-- display trace id for future references
select TraceID=@TraceID
goto finish

error: 
select ErrorCode=@rc

finish: 
go
```

ブロッキングの秒数(10秒)を設定

```
-- Set blocked process threshold
exec sp_configure 'show advanced options', 1  
go 
RECONFIGURE 
go 
exec sp_configure 'blocked process threshold', 10 
go 
RECONFIGURE
```

# 監視の運用
ブロッキングの監視はSQLServerのインスタンスが停止すると定義が消えてしまいます。そのため、SQLServerの起動時にトレースを開始するように設定する必要があります。
方法として、SQLServerのジョブスケジュールにてSQL Serverエージェントの開始時に自動的に開始でジョブ登録するのが簡単でしょう。

私は下記バッチを作成してジョブ登録しました。

```bat:CreateBlocking.bat
@echo off
cd /d %~dp0
SET target=D:\sqltrace

IF NOT EXIST %target% (
  MKDIR %target%
)
DEL /Q %target%\blockprocess.trc

sqlcmd -S localhost -U (ユーザー名) -P (パスワード) -i sp_trace_block_process.sql

exit 0
```


あと、トレースが有効になっているか新しいクエリで下記のSQLを実行して確認する際にエラーになってしまいました。

```sql
Select * from ::fn_trace_getinfo(NULL)
```
原因は、fn_trace_gettableの権限がなかったためです。その場合、下記のようにALTER TRACE権限を付与する必要があります。

```
USE [master]
GRANT ALTER TRACE TO  [ユーザー名]
```

# 確認方法
SQLServerManagementStudioを2つ立ち上げる。
片方のSQLServerManagementStudioをA、もう片方をBとする。
テスト用にTESTテーブルを用意したとします。

Aにて下記のSQLを実行します。

```sql
BEGIN TRAN
    SELECT * FROM TEST WITH(TABLOCKX)
    WAITFOR DELAY '00:00:30'  --任意の時間スリープ。
COMMIT TRAN
```

Bにて下記のSQLを実行(Aを実行して30秒以内に実行する)

```sql
SELECT * FROM TEST
```

30秒後に結果が返ってくるので、BのSQLを中断します。
※ブロッキングは、10秒としている。

トレースファイル「D:\sqltrace\blockprocess.trc」にブロッキングのログが出力されていれば、ブロッキングの監視が出来ていることになります。

# その他

TraceID が分からなくなってしまった場合は、fn_trace_getinfo 関数を使用することで、実行中のトレースの TraceID を確認することができます。

```sql
Select * from ::fn_trace_getinfo(NULL)
```

トレース停止・開始・削除には、sp_trace_setstatus 関数を使用します。

```
sp_trace_setstatus {TraceID}, 0 -- トレースの停止 
sp_trace_setstatus {TraceID}, 1 -- トレースの開始 
sp_trace_setstatus {TraceID}, 2 -- トレース定義の削除
```

ブロッキング秒数(今回は10秒)の確認方法でrun_valueの値が「10」になっていることを確認する。

```
sp_configure 'show advanced options'
go 
sp_configure 'blocked process threshold'
go
```

sp_configure 'blocked process threshold'は、sp_configure 'show advanced options', 1 で拡張オプションを表示するようにしておかないと、「構成オプション 'blocked process threshold' が存在しないか、詳細構成オプションの可能性があります。」が表示される。
[[T-SQL] SQLサーバー構成オプションの表示と設定](http://www.pine4.net/Memo/Article/Archives/144)

イベントの確認方法でBlocked Process Report イベントの種類「137」が登録されていることを確認する。

```
SELECT * FROM fn_trace_geteventinfo({TraceID}) 
```
# 参照

* [ブロッキングとデッドロックを後追い確認する方法](http://blog.engineer-memo.com/2012/08/30/%E3%83%96%E3%83%AD%E3%83%83%E3%82%AD%E3%83%B3%E3%82%B0%E3%81%A8%E3%83%87%E3%83%83%E3%83%89%E3%83%AD%E3%83%83%E3%82%AF%E3%82%92%E5%BE%8C%E8%BF%BD%E3%81%84%E7%A2%BA%E8%AA%8D%E3%81%99%E3%82%8B%E6%96%B9/)

* [SQL トレーススクリプトの作成、実行 (SQL Server 2005 ～ 2014)](https://blogs.msdn.microsoft.com/jpsql/2011/01/25/sql-sql-server-2005-2014/)

* [[SQL Troubleshooting] 第6回:ブロッキング情報を採取する (SQL Server 2000 ～ 2008 R2)](http://blogs.msdn.com/b/jpsql/archive/2012/07/24/sql-troubleshooting-6-sql-server-2000-2008-r2.aspx)

* [[SQLServer]ロック関連](http://yuruse.tou3.com/sqlserver/-sqlserver-%E3%83%86%E3%83%BC%E3%83%96%E3%83%AB%E3%83%AD%E3%83%83%E3%82%AF%E6%96%B9%E6%B3%95)

* [SQL Server でロック待ちのプロセスをリストアップ その4](http://d.hatena.ne.jp/matu_tak/20091111/1257868883)

* [SharePoint と SQL ブロッキングの話 Part3](https://blogs.technet.microsoft.com/sharepoint_support/2012/07/11/sharepoint-sql-part3/)

* [ロック待ちプロセスをプロファイラで監視する Blocked process report の利用](http://d.hatena.ne.jp/miho_matsumoto/20091103/1257223821)

* [blocked process threshold の増加または無効化](https://msdn.microsoft.com/ja-jp/library/bb402879.aspx)

* [ロックエスカレーションを禁止する設定](http://www.projectgroup.info/tips/SQLServer/MSSQL_00000016.html)

* [[T-SQL] SQLサーバー構成オプションの表示と設定](http://www.pine4.net/Memo/Article/Archives/144)