ブロッキングの情報を取得するための方法として「blocked process threshold (s)」を使用する方法がありますが、それ以外の方法の紹介を。
SQL Server エージェントでは「警告」からパフォーマンスモニターのカウンターの情報を使用して、SQL Server エージェントのジョブを実行することができ、これを使用することでブロッキングの情報をロギングすることができます。
ロック競合が発生した場合「Wait Statistics」の「Lock waits」の「Waits in progress」として、ロック競合が発生しているクエリの個数を取得することができます。
ロックにより待機した時間については、ロックが解消されたタイミングでカウントアップされたかと思いますので「ロック競合が発生中」のためのトリガーとしては使うことができないのですが、「Waits in progress」に関しては、「ロック競合が発生している要求」のカウントとなりますので、こちらであればロック競合が発生しているかどうかを取得することができます。
この項目を使用して以下のような警告の条件を作成します。
この条件に合致した場合に SQL Server エージェントのジョブを実行します。
実行するジョブですが、PowerShell のスクリプトを実行するようなジョブを指定しています。
このジョブで実行される PowerShell のスクリプトですが、以下のようなスクリプトを実行しています。
$sql = @" -- ブロッキングチェーンの取得 WITH sp AS( SELECT spid, blocked, cmd, lastwaittype,waitresource,status, text FROM sys.sysprocesses CROSS APPLY sys.dm_exec_sql_text(sql_handle) WHERE spid > 50 ), BlockList AS( -- Blocker SELECT spid, CAST(blocked AS varchar(100)) AS blocked, 1 AS level, CAST(RIGHT(REPLICATE('0', 8) + CAST(spid AS varchar(10)), 8) AS varchar(100)) AS blocked_chain, CAST('' AS varchar(100)) AS blocked_path, RTRIM(cmd) AS cmd, RTRIM(lastwaittype) AS lastwaittype, RTRIM(waitresource) AS waitresource, RTRIM(status) AS status ,text FROM sp WHERE blocked = 0 AND spid in (SELECT blocked FROM sp WHERE blocked <> 0) UNION ALL -- Blocked SELECT r.spid, CAST(r.blocked AS varchar(100)), BlockList.level + 1 AS level, CAST(BlockList.blocked_chain + CAST(r.spid AS varchar(10)) AS varchar(100)) AS blocked_chain, CAST(IIF(BlockList.blocked_path='', '', BlockList.blocked_path + '->') + CAST(r.blocked AS varchar(10)) AS varchar(100)) , RTRIM(r.cmd) AS cmd, RTRIM(r.lastwaittype) AS lastwaittype, RTRIM(r.waitresource) AS waitresource, RTRIM(r.status) AS status, r.text FROM sp r INNER JOIN BlockList ON r.blocked = BlockList.spid ) SELECT level, spid, IIF(blocked_path = '', '', blocked_path + '->' + CAST(spid AS varchar(10))) AS blocked_path, cmd, lastwaittype, waitresource, status, text FROM BlockList ORDER BY blocked_chain, level OPTION (MAXRECURSION 100, RECOMPILE) "@ $filepath = "C:\Scripts" $filename = "Bloking-{0}.json" -f (Get-Date).ToString("yyyyMMddHHmmss") Invoke-Sqlcmd -ServerInstance . -Query $sql | SELECT level,spid,blocked_path,cmd,lastwaittype, waitresource,status,text | ConvertTo-Json | Out-File -FilePath (Join-Path $filepath $filename)
これで準備は完了です、
SQL Server エージェントジョブの警告ですが、20 秒間隔程度のタイミングでチェックを実行しているようで、チェックされたタイミングでブロッキング (ロック競合) が発生していた場合に、ブロッキング情報が含まれた JSON が出力されます。
# ConvertTo-Json で出力している箇所を適宜変更していただければ CSV や TSV も対応可能です。
出力されたファイルですが、OPENROWSET を使うことでクエリで開くことも可能です。
DECLARE @BlockingList nvarchar(max) SELECT @BlockingList = BulkColumn FROM OPENROWSET(BULK 'C:\Scripts\Bloking-20170326211141.json', SINGLE_NCLOB) AS a SELECT * FROM OPENJSON(@BlockingList) WITH( level int '$.level', spid smallint '$.spid', blocked_path varchar(100) '$.blocked_path', cmd nchar(16) '$.cmd', lastwaittype nchar(32) '$.lastwaittype', waitresource nchar(256) '$.waitresource', status nchar(30) '$.status' )