SE の雑記

SQL Server の情報をメインに Microsoft 製品の勉強内容を日々投稿

ブロッキングの情報を「blocked process threshold (s)」以外の方法でロギングしてみる

leave a comment

ブロッキングの情報を取得するための方法として「blocked process threshold (s)」を使用する方法がありますが、それ以外の方法の紹介を。


SQL Server エージェントでは「警告」からパフォーマンスモニターのカウンターの情報を使用して、SQL Server エージェントのジョブを実行することができ、これを使用することでブロッキングの情報をロギングすることができます。
ロック競合が発生した場合「Wait Statistics」の「Lock waits」の「Waits in progress」として、ロック競合が発生しているクエリの個数を取得することができます。
ロックにより待機した時間については、ロックが解消されたタイミングでカウントアップされたかと思いますので「ロック競合が発生中」のためのトリガーとしては使うことができないのですが、「Waits in progress」に関しては、「ロック競合が発生している要求」のカウントとなりますので、こちらであればロック競合が発生しているかどうかを取得することができます。
この項目を使用して以下のような警告の条件を作成します。
image
この条件に合致した場合に SQL Server エージェントのジョブを実行します。
実行するジョブですが、PowerShell のスクリプトを実行するようなジョブを指定しています。
image
このジョブで実行される 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'
)

image
ロック競合は同時実行性を低下させる一因になりますので、複数の情報取得方法を知っておくと便利かなと思います。

Share

Written by Masayuki.Ozawa

3月 26th, 2017 at 9:52 pm

Posted in SQL Server

Tagged with

Leave a Reply