はじめに
「Microsoft SQL Server 2005 Reporting Services」（以下、SSRS）を使ってデータの見える化を行っています。実際の実績データはOracle11gで登録しているのですが、自作のレプリケーションツールを使って定期間隔でSQL Server側に実績データのレプリケーションさせています。 このレプリケーション時に「タイムアウトに達しました。…」のエラーが発生するわけです。
「ロックが原因で発生していると思うのですが、まずは再発とその時の状況が分かるようにデッドロックとブロッキングを監視したい」との依頼を受けました。
今回は、ブロッキングの監視方法についてのみ説明します。
監視方法
早速、「SQLServer デッドロック ブロッキング 監視」で検索してみると、下記サイトが見つかります。
「ブロッキングとデッドロックを後追い確認する方法 - SE の雑記」
SQL Server 2005 以降では Blocked Process Report イベント クラス を使用することができます。
ブロッキングが特定の秒数発生した場合にイベントを発生する上に、レポートには競合が発生しているクエリが出力されるので、どのクエリ間で競合が発生したのかを確認することもできて便利です。
設定方法
今回、SQL Server Profiler（プロファイラ）で一時的に監視するのではなく、永続的に監視をするためスクリプトを作成して使用します。
SQL Server 2012 であれば、拡張イベントの設定が GUI で簡単にできるようですが、SQL Server 2008なので下記サイトを参考に作成します。
「SQL トレーススクリプトの作成、実行 (SQL Server 2005 ～ 2014)」
イベントの内容を1つ1つ確認するのは大変なので「SQL Server Profiler」で作成後にsqlファイルにエクスポートするのが簡単です。
トレースファイルは「D:\sqltrace\blockprocess」に保存しています。
スクリプトを修正し、ブロッキングの秒数(10秒)の設定を追記しています。
ブロッキングの秒数(10秒)の設定は別途行います。
スクリプトの中に入れてバッチ内で実行しようとしたところ、権限エラーとなってしまいました。
一般ユーザーでも実行するには下記サイトを参考にするといいでしょう。
「サーバーレベルの権限を必要とする操作を一般ユーザーで実施する」
/****************************************************/
/* 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エージェントの開始時に自動的に開始でジョブ登録するのが簡単でしょう。
私は下記バッチを作成してジョブ登録しました。
@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を実行して確認する際にエラーになってしまいました。
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を実行します。
BEGIN TRAN
SELECT * FROM TEST WITH(TABLOCKX)
WAITFOR DELAY '00:00:30' --任意の時間スリープ。
COMMIT TRAN
Bにて下記のSQLを実行(Aを実行して30秒以内に実行する)
SELECT * FROM TEST
30秒後に結果が返ってくるので、BのSQLを中断します。
※ブロッキングは、10秒としている。
トレースファイル「D:\sqltrace\blockprocess.trc」にブロッキングのログが出力されていれば、ブロッキングの監視が出来ていることになります。
その他
TraceID が分からなくなってしまった場合は、fn_trace_getinfo 関数を使用することで、実行中のトレースの TraceID を確認することができます。
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サーバー構成オプションの表示と設定
イベントの確認方法でBlocked Process Report イベントの種類「137」が登録されていることを確認する。
SELECT * FROM fn_trace_geteventinfo({TraceID})