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.
