Michael J. Swart

February 2, 2010

Looking at Page Contents

Filed under: SQL Scripts,SQLServerPedia Syndication,Technical Articles — Tags: , — Michael J. Swart @ 10:06 am

If you want to dig deep into SQL Server internals, you’re eventually going to want to look at the physical page structure of a data page. As such I’ve bookmarked Anatomy of a Page by Paul Randal.

The syntax for all this stuff is hard for me to remember, so I’ve made a template for myself. And I’m sharing that here:

-- looking at page contents.
-- T3604 to output to console
dbcc traceon (3604)
-- select rows from the table of interest
-- (as well as their physical location)
select top (10) t.*,
	pl.file_id, pl.page_id, pl.slot_id
from <tablename,sysname,Production.Product> as t
cross apply sys.fn_PhysLocCracker(t.%%physloc%%) as pl;
declare @dbname sysname;
set @dbname = DB_NAME();
dbcc page(@dbname, --db_name
	1, --file_id
	136, --page_id
	1) --results style

Ctrl+Shift+M will let you provide the table name. I’ve also made this script into a code-snippet called page.

Note that the script makes use of sys.fn_PhysLocCracker which is SQL 2008 and later.

By the way, I highly recommend playing with this stuff. Not having to guess at the width of records is really helpful. I plan to write a post soon which shows how it helped an investigation I did.

Powered by WordPress