【SQL Server】データの格納ページ位置の確認方法
テーブルに格納されているレコードがどのページにあるのか確認する方法です。
デッドロックの調査や以前説明したラッチ状況を確認する場合などに使えるかと思います。
sys.fn_PhysLocFormatter
今回確認に使うundocumentedなファンクションです。
どのような内容なのかチェックします。
sp_helptext [sys.fn_PhysLocCracker]
上記から、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
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]
尚、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のメモ帳
と同じものを確かめてみると、ラッチを確保していたルート、中間ノード、リーフ(データ)のページと一致することがわかります。
※page_levelが高いほど上位のノードです。
ルートページ:75715
中間1ページ:5615、75716、109086、143394
中間2ページ:5009、5624、5958、6209…
あまり普段使用しませんが、レコードがどのページにどれだけ格納されているか、インデックスのノードがどのページに格納されているのか調べる方法となりますので把握しておいて損はないかと。