【Google Cloud】ブロッキング調査用スクリプト【Cloud SQL for SQL Server】

0. はじめに

Cloud SQL for SQL Server では、クエリ ストア 機能を利用することで パフォーマンス問題発生時のクエリ特定、クエリ実行プラン の確認をすることが可能ですが、拡張イベントによるクエリのトレース機能については、現時点 (2024年8月時点) においてサポートされていないようです。
しかしながら、ブロッキングによるパフォーマンス問題発生時、ブロッキング関連のセッション (SPID) で、どのようなトランザクションが開始され、各トランザクション内でどのようなクエリが実行され、どのようにロックを獲得しているかなどの処理の流れを知りたいこともあるのではないでしょうか。
今回、T-SQL (動的管理ビュー、システム カタログ ビュー) を利用した 「ブロッキング調査用スクリプト」 を作成してみようと思います。

目次

0. はじめに
1. ブロッキング調査用スクリプト
2. ブロッキング調査用スクリプトの利用例
3. ブロッキング調査用スクリプト実行手順

1. ブロッキング調査用スクリプト

ブロッキング調査用のスクリプトは以下の通りです。
※ 本スクリプトで取得される情報の詳細については「2. ブロッキング調査用スクリプトの利用例」を参照。
※ 本スクリプトで実行されているクエリの詳細な説明は省略しておりますが、興味のある方は解析してみると楽しいかもしれません。

ブロッキング調査用スクリプト (TSQL)

USE *****; --明示的にデータベース名を指定
GO

SELECT GETDATE() as Date;
GO
SELECT @@version as Version;
SELECT
SERVERPROPERTY('ProductVersion') AS ProductVersion,
SERVERPROPERTY('ProductLevel') AS ProductLevel,
SERVERPROPERTY('Edition') AS Edition,
SERVERPROPERTY('EngineEdition') AS EngineEdition,
SERVERPROPERTY('ServerName') AS ServerName;
GO
PRINT '【 サーバー構成オプション 】';
PRINT '';
SELECT * FROM sys.configurations ORDER BY name;
GO
PRINT '【 データベース情報  】';
PRINT '';
SELECT * FROM sys.databases;
SELECT * FROM sys.master_files;
GO

DECLARE @CurDate datetime;
 
WHILE(1=1)
BEGIN
	SET @CurDate = GETDATE();

	SELECT N'StartTime:', @CurDate as Date;
	PRINT '';

	PRINT '【ブロッキング情報】'
	PRINT '';
	WITH ctetraninfo
	AS ( SELECT 
			dess.session_id,
			derq.request_id,
			derq.blocking_session_id,
			LEFT (derq.status, 15) AS 'status',
			LEFT (ISNULL (derq.wait_type, ''), 40) AS 'wait_type',
			derq.wait_time AS 'wait_time',
			LEFT (ISNULL (derq.wait_resource, ''), 40) AS 'wait_resource',
			LEFT (derq.last_wait_type, 50) AS 'last_wait_type',
			derq.start_time,
			derq.command,
			derq.cpu_time,
			derq.logical_reads,
			derq.reads,
			derq.writes,
			derq.percent_complete,
			derq.plan_handle,
			derq.sql_handle,
			derq.statement_start_offset,
			derq.statement_end_offset,
			derq.query_hash,
			derq.query_plan_hash,
			dect.most_recent_sql_handle,
			dess.cpu_time AS 'session_cpu_time',
			dess.logical_reads AS 'session_logical_reads',
			dess.reads AS 'session_reads',
			dess.writes AS 'session_writes'
		 FROM sys.dm_exec_sessions AS dess
		 LEFT OUTER JOIN sys.dm_exec_requests AS derq ON dess.session_id = derq.session_id
		 LEFT OUTER JOIN sys.dm_exec_connections AS dect on dess.session_id = dect.session_id
	   ),
	cteblockinginfo
	AS ( SELECT
			session_id AS head_blocker_session_id,
			blockinginfo.session_id AS session_id, 
			blockinginfo.blocking_session_id,
			blockinginfo.status,
			blockinginfo.wait_type,
			blockinginfo.wait_resource,
			blockinginfo.start_time,
			blockinginfo.command,
			ISNULL (cpu_time, session_cpu_time) AS 'cpu_time',
			ISNULL (logical_reads, session_logical_reads) AS 'logical_reads',
			ISNULL (reads, session_reads) AS 'reads',
			ISNULL (writes, session_writes) AS 'writes'
		 FROM ctetraninfo as blockinginfo
		 WHERE (blockinginfo.blocking_session_id IS NULL OR blockinginfo.blocking_session_id = 0)
		 AND blockinginfo.session_id IN (SELECT DISTINCT blocking_session_id FROM ctetraninfo WHERE blocking_session_id != 0)
		 UNION ALL
		 SELECT
			blockedinfo.blocking_session_id AS head_blocker_session_id,
			blockedinfo.session_id AS session_id, 
			blockedinfo.blocking_session_id,
			blockedinfo.status,
			blockedinfo.wait_type,
			blockedinfo.wait_resource,
			blockedinfo.start_time,
			blockedinfo.command,
			blockedinfo.cpu_time AS 'cpu_time',
			blockedinfo.logical_reads AS 'logical_reads',
			blockedinfo.reads AS 'reads',
			blockedinfo.writes AS 'writes'
		 FROM ctetraninfo as blockedinfo
		 INNER JOIN cteblockinginfo ON cteblockinginfo.session_id = blockedinfo.blocking_session_id 
		 AND cteblockinginfo.session_id != blockedinfo.session_id 
	   )
	SELECT @CurDate, * FROM cteblockinginfo


	PRINT '【トランザクション情報】'
	PRINT '';
	SELECT @CurDate,
	dtst.session_id, LEFT(DB_NAME(stdt.database_id),15) as 'db_name', 
	dtst.transaction_id, stdt.database_transaction_begin_time, 
	stdt.database_transaction_state, 
	stdt.database_transaction_log_bytes_used, 
	dtst.enlist_count, dtst.open_transaction_count, 
	derq.sql_handle,
	dect.most_recent_sql_handle,
	dest.text as 'sql_query_text'
	FROM sys.dm_tran_database_transactions stdt
	INNER JOIN sys.dm_tran_session_transactions dtst ON stdt.transaction_id = dtst.transaction_id
	INNER JOIN sys.dm_exec_connections dect ON dtst.session_id = dect.session_id
	LEFT OUTER JOIN sys.dm_exec_requests AS derq ON dect.session_id = derq.session_id
	CROSS APPLY sys.dm_exec_sql_text (ISNULL (derq.sql_handle, dect.most_recent_sql_handle)) AS dest;

	WAITFOR DELAY '00:00:15' --情報採取間隔を指定 (秒単位)
 
END

2. ブロッキング調査用スクリプトの利用例

ブロッキング調査用スクリプトでは以下の情報を取得できるように設定しています。

• SQL Server バージョン情報 (1回のみ)
  
  　
• SQL Server バージョン, エディション情報 (1回のみ)
  
  　
• SQL Server 構成情報 (1回のみ)
  
  　
• SQL Server データベース情報 (1回のみ)
  
  　
• ブロッキング情報 (ブロッキング発生時のみ : 情報取得間隔 15秒)
  

　
上記のブロッキング情報の内容について説明します。

1. head_blocker_session_id :
   ブロッキング チェーンの先頭になっているセッション (SPID) 情報
2. blocking_session_id :
   該当セッションから実行されているトランザクションをブロッキングしているセッション情報
3. wait_type :
   該当セッションから実行されているトランザクションで獲得待ち状態となっているロック情報
   ※ 本例の場合、LCK_M_X : 排他ロック の獲得待ち状態となっている。
4. command :
   トランザクションの種類
   ※ 本例の場合、「UPDATE」(更新) クエリがブロッキングにより待ち状態となっている。
5. wait_resource :
   該当セッションから実行されているトランザクションで獲得待ち状態となっているロックリソース情報
   ※ 本例の場合、データベースID : 5 (GCSDB), HOBT ID : 72057594045792256, ロックリソース ハッシュ : (a0c936a3c965)

HOBT ID デコードクエリ (例)

SELECT
    sau.allocation_unit_id,
    OBJECT_NAME(spt.object_id) AS object_name,
    sdx.name
FROM
    sys.allocation_units AS sau WITH(NOLOCK)
    INNER JOIN sys.partitions AS spt WITH(NOLOCK)
        ON spt.hobt_id = sau.container_id
    INNER JOIN sys.indexes AS sdx WITH(NOLOCK)
        ON sdx.object_id = spt.object_id AND sdx.index_id = spt.index_id
WHERE
    spt.hobt_id = 72057594045792256 -- HOBT ID を条件に指定

※ 本例の場合、テーブル "tab1" の インデックス "PK_tab1" のキーに対して、排他ロックを獲得しようとしていることが確認できます。
　

ロックリソース ハッシュ デコードクエリ (例)

SELECT
    *,
    %%lockres%% AS lock_resource
FROM
    tab1 WITH(NOLOCK) -- HOBT ID デコードクエリで確認したテーブルを指定
WHERE %%lockres%% = '(a0c936a3c965)' -- ロックリソース ハッシュを指定

※ 本例の場合、テーブル "tab1" の c1 列が「4」の行データに対して、排他ロックを獲得しようとしていることが確認できます。
　

• トランザクション情報 (実行中のトランザクション情報のみ : 情報取得間隔 15秒)
  
  

　
上記のトランザクション情報の内容について説明します。

1. transaction_id :
   トランザクション ID 情報
2. sql_handle :
   現在実行中のクエリのハンドル情報
3. most_recent_sql_handle :
   該当セッションの中で最後に実行されたクエリのハンドル情報
4. sql_query_text :
   現在実行中のクエリもしくは最後に実行されたクエリ情報

3. ブロッキング調査用スクリプト実行手順

【前提条件/注意事項】

• 本手順では SQL Server Management Studio (SSMS) を利用
  ※ ブロッキング調査用スクリプトは SQLCMD などの他のツールからも実行可能
• ブロッキング調査用スクリプトは Cloud SQL for SQL Server から物理的な距離が近い Google Cloud Compute Engine から実行
• ブロッキング調査用スクリプトは検証環境などでしっかりと検証し、出力情報量を減らす必要がある場合はスクリプト内を変更

【実行手順】

1. SQL Server Management Studio を起動し、Cloud SQL for SQL Server : sqlserver ログインで Cloud SQL for SQL Server インスタンスに接続します。

2. [新しいクエリ] を選択します。

3. [クエリ] - [結果の出力] - [結果をファイルに出力] を選択します。

4. ブロッキング調査用スクリプトを張り付けて実行します。

   • USE 句で情報を採取するデータベース名を明示的に指定
   • WAITFOR DELAY '00:00:15' を修正し、情報採取間隔を指定
     　

5. 保存用ダイアログが開いた後、情報の出力先 及び ファイル名を指定します。

6. [Alt] + [Break] もしくは、赤四角ボタン (クエリ実行の取り消し) をクリックし、情報採取を停止します。

7. メモ帳などで出力されたブロッキング情報を確認します。
   　
   

　
※ 本ブログに記載した内容は個人の見解であり、所属する会社、組織とは全く関係ありません。
※ 2024年8月 現在