小物SEのメモ帳

IT関係の試したことや調べたことの備忘録

【SQL Server】データの格納ページ位置の確認方法

テーブルに格納されているレコードがどのページにあるのか確認する方法です。
デッドロックの調査や以前説明したラッチ状況を確認する場合などに使えるかと思います。

sys.fn_PhysLocFormatter

今回確認に使うundocumentedなファンクションです。
どのような内容なのかチェックします。

sp_helptext [sys.fn_PhysLocCracker]

f:id:utiowa:20170226171639p:plain

上記から、sys.fn_PhysLocCrackerの引数にphysical_locatorを渡して実行すればよいことがわかります。
試しに実行してみます。

SELECT * FROM dbo.EMPLOYEES
CROSS APPLY sys.fn_PhysLocCracker(%%physloc%%) AS fPLC
ORDER BY 
	fPLC.file_id,
	fPLC.page_id,
	fPLC.slot_id

f:id:utiowa:20170226171736p:plain

sys.dm_db_database_page_allocations

また、データだけでなくクラスターインデックスなどに存在しているルートや中間ノードがどのページにあるかを知りたい場合は、以下のようなクエリでわかるかと思います。

SELECT	[dpa].[page_level] AS [page_level],
	[dpa].[allocated_page_page_id] AS [page_id],
	[i].[name] AS [index_name],
	[dpa].[page_type_desc],
	[dpa].[previous_page_page_id],
	[dpa].[next_page_page_id]
  FROM  sys.dm_db_database_page_allocations(	DB_ID('sales'),	
						OBJECT_ID('dbo.EMPLOYEES'),
						1,
						NULL,
						'DETAILED'
        				) AS [dpa]
  INNER JOIN sys.indexes AS [i]
	ON	[dpa].[object_id] = [i].[object_id]
	AND   [dpa].[index_id] = [i].[index_id]
  WHERE [dpa].[page_level] IS NOT NULL
  ORDER BY	[dpa].[page_level] DESC,
		[dpa].[allocated_page_page_id]

f:id:utiowa:20170226172058p:plain

尚、sys.dm_db_database_page_allocationsの実行の仕方は次のとおりです。

Syntax :

sys.dm_db_database_page_allocations
(@DatabaseId , @TableId , @IndexId , @PartionID , @Mode)

Parameters :

@DatabaseId database id [not null]
@TableId object id
@IndexId index id
@PartionI partition id
@Mode LIMITED or DETAILED


例えば、以前説明したもの【SQL Server】ロックとラッチのトレース - 小物SEのメモ帳
と同じものを確かめてみると、ラッチを確保していたルート、中間ノード、リーフ(データ)のページと一致することがわかります。
f:id:utiowa:20170226172408p:plain
f:id:utiowa:20170226173432p:plain
※page_levelが高いほど上位のノードです。
ルートページ:75715
中間1ページ:5615、75716、109086、143394
中間2ページ:5009、5624、5958、6209…

あまり普段使用しませんが、レコードがどのページにどれだけ格納されているか、インデックスのノードがどのページに格納されているのか調べる方法となりますので把握しておいて損はないかと。