SQL Server の sys.dm_exec_requests や、sys.dm_tran_locks 等の DMV では、どのオブジェクトに起因して待機が発生しているかというリソースの情報が出力されることがあります。
本投稿では、待機要因となっているリソースの見方について見ていきます。
英語の情報となりますが、Decoding Key and Page WaitResource for Deadlocks and Blocking が詳しいかと思います。
Wait Resource の表示についてはいくつかのパターンがありますが、基本形としては次の 2 種類を把握しておくとよいかと。
- リソースタイプ : DB ID : オブジェクト ID
- リソースタイプ : DB ID : ファイル ID : ページ番号
- リソースタイプ : DB ID : アロケーションユニット
例としては次のような情報です。
「wait_resource」に待ち事象が発生しする原因となっているリソースの情報が出力されており、この情報を読み解くことで「どのリソースのアクセスに対して待機が発生しているか」を理解することができます。
Contents
1. リソースタイプ : DB ID : オブジェクト ID
このパターンはシンプルですね。
出力されている DB の該当のオブジェクト ID に対してアクセスを行おうとした際にロック競合が発生しているという状態となります。
「OBJECT: 11:368720366:0 」という情報になっている場合は、「DB ID :11」の「オブジェクト ID : 368720366」にアクセスを使用とした際に待機状態となります。
これがどのオブジェクトを取得するのかはとてもシンプルで OBJECT_NAME 関数を使用することで解決ができます。
この関数にオブジェクト ID と DB ID を渡すことで、どのオブジェクトかを確認することができます。
これでどのオブジェクトへのアクセスで問題があるかを把握することができます。
2. リソースタイプ : DB ID : ファイル ID : ページ番号
SQL Server のデータ格納領域の基本単位の種類として 8KB ページがあり、そのデータ領域にアクセスを行った際にブロッキングされている状態になります。
例としては次のような「11:1:39424」というような形式です。
この場合は、「DB ID : 11」の「ファイル ID : 1」の「ページ番号 : 39424」にアクセスを行った際に待機状態になったということを示します。
「ページ番号がどのオブジェクトか？」を確認するためには、SQL Server のバージョンに応じていくつかの方法があります。
- DBCC PAGE
- sys _db_page_info (SQL Database or SQL Server 2019 以降)
ページの情報を直接参照して、該当のページがどのオブジェクトのデータなのかを確認する方法です。
ページ ID 39424 の情報を確認する場合は次のようなコマンドとなります。
DBCC TRACEON(3604) DBCC PAGE(11, 1, 39424, 0) WITH TABLERESULTS
取得したページ情報の「Metadata: ObjectId」がページのオブジェクトの情報となります。
ここまで情報が取得できれば OBJECT_NAME 関数でどのオブジェクトなのかを取得することが可能です。
SQL Database や SQL Server 2019 以降を使用している場合、DBCC コマンドを使用しなくても DMV 経由でページ情報を取得することができますので、こちらでも大体は可能です。
他にはは sys.dm_os_buffer_descriptors から情報を参照する方法もあります。
この方法については、バッファキャッシュ内にデータが載っている場合に限定されますが、メモリ上にデータが存在しているのであれば取得することができます。
SELECT
DB_NAME(b.database_id) AS database_name,
b.file_id,
b.page_id,
p.object_id,
object_name(p.object_id) AS object_name
FROM
sys.dm_os_buffer_descriptors AS b WITH(NOLOCK)
INNER JOIN sys.allocation_units AS a WITH(NOLOCK)
ON a.allocation_unit_id = b.allocation_unit_id
INNER JOIN sys.partitions AS p WITH(NOLOCK)
ON p.hobt_id = a.container_id
WHERE
b.page_id = 39424
メモリにデータがのっていない場合は、この方法では取得できませんが、方法の一つとしては覚えておくと良いのではないでしょうか。
(DBCC PAGE で正しいページの情報が見れない場合などは、こちらで取得した方が良いこともありますので)
3.リソースタイプ : DB ID : アロケーションユニット
最後は、アロケーションユニットを元に算出を行うケースとなります。
実際にはこのケースが多いのではないでしょうか。
形式としては、次の画像のような形式です。
ロック競合では、この形式で表示されるケースが多いですね。
今回は「KEY: 11:562949955649536 (f4d50ff9792b)」となっています。
先頭はリソースタイプとなり、その次の情報が「DB ID」「HOBT ID」となります。
上の画像の場合は、「DB ID : 11」の「HOBT ID: 562949955649536」の「マジックハッシュ : (f4d50ff9792b)に対して、競合が発生していることになります。
マジックハッシュについては後述するとして、まずは HOBT IDを元に情報を取得してみましょう。
SELECT
a.allocation_unit_id,
OBJECT_NAME(p.object_id) AS object_name,
i.name
FROM
sys.allocation_units AS a WITH(NOLOCK)
INNER JOIN sys.partitions AS p WITH(NOLOCK)
ON p.hobt_id = a.container_id
INNER JOIN sys.indexes AS i WITH(NOLOCK)
ON i.object_id = p.object_id AND i.index_id = p.index_id
WHERE
p.hobt_id = 562949955649536
実行結果がこちらになります。
HOBT IDからどのオブジェクトの情報なのかを取得することができていますね。
マジックハッシュについてはアンドキュメントの関数になるのですが「%%lockres%%」を使用することで、データのマジックハッシュを表示させることができます。
(%%physloc%% でデータの物理ページを取得するというアプローチも併せて覚えておくとよいかもしれません)
SELECT *, %%lockres%% AS lock_res FROM sys.sysschobjs WITH(NOLOCK) ORDER BY %%lockres%%
この情報から「どの行に対してロックが発生していたのか？」まで、特定できるケースがあります。
まとめ
待ち事象が発生しており、「どのリソースなのか？」の情報を読み解くことで、オブジェクトを特定し、問題に対してのアプローチを検討する際に追加の情報を得ることができるようになります。
「どのような待ちで待機していたのか」だけでなく「どのオブジェクト / データで待機していたのか？」を見れるようになると、一歩進んだ問題解決ができるようになりますので「情報をどこまで掘り下げて確認することができるか？」と考えることは常に重要ではないでしょうか。