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.
[...] Looking at Page Contents – A little code template here from Michael J Swart. [...]
Pingback by Something for the Weekend: SQL Server Links for the week 05/02/10 | John Sansom - SQL Server DBA in the UK — February 7, 2010 @ 12:00 pm