NOBTAの気ままにITブログ

Azure全般 / SQL Serverに関する情報を発信していきます。

【第21回】基本から始める SQL Server【ブロッキング調査用スクリプト】


スポンサーリンク

第20回では、SQL Server トランザクション ログファイルの圧縮方法についてまとめてみました。

 

SQL Server や Azure SQL Database などには、標準機能として実行されているクエリ内容などをトレースする機能を保持していますが、簡易的にブロッキング調査を実施したい、標準のトレース機能が利用できないサービス (Google Cloud SQL for SQL Server など) でもブロッキング調査を実施したいことがあるのではないでしょうか。

 

今回は、T-SQL  (動的管理ビュー、システム カタログ ビュー) を利用し、SQL Server、Azure SQL Database だけではなく、標準のトレース機能が利用できないサービス (Google Cloud SQL for SQL Server など) でもブロッキング調査を実施することを可能とする方法についてまとめてみようと思います。

 

 

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

ブロッキング調査用のスクリプトを実行することで、ブロッキング関連のセッション (SPID) で、どのようなトランザクションが開始され、各トランザクション内でどのようなクエリが実行され、どのようにロックを獲得しているかなどの処理の流れをある程度、特定することが可能となります。

※ 本スクリプトで取得される情報の詳細については「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

 

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

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

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

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

  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 を条件に指定

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

 

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

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

 

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

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

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

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

 

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

【前提条件/注意事項】

  • 本手順では 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. メモ帳などで出力されたブロッキング情報を確認します。
    　
    image.png

 

まとめ

今回、T-SQL  (動的管理ビュー、システム カタログ ビュー) を利用し、SQL Server、Azure SQL Database だけではなく、標準のトレース機能が利用できないサービス (Google Cloud SQL for SQL Server など) でもブロッキング調査を実施することを可能とする方法についてまとめてみました。

本スクリプトは、動的管理ビュー、システム カタログ ビューなどの標準機能を利用しているため、基本的にどのような環境でもブロッキング情報を採取することができると思います。

なお、本スクリプトを実行する際には、事前に大量に情報が出力されることがないか検証を実施のうえ、利用していただければと思います。

 

※ 2024年9月時点