SQL How To: Dump the contents of a SQL Page

This technique uses an excellent stored procedure developed by Paul S. Randal, which I’ve copied here for safe keeping, and described how to use it (in my own words). Be sure to check out his articles, they are insightful to say the least.

http://www.sqlskills.com/blogs/paul/inside-the-storage-engine-sp_allocationmetadata-putting-undocumented-system-catalog-views-to-work/

 

After installing the stored procedure below called sp_AllocationMetaData, and allowing it to be executed from any database by  marking it as a system object, you can simply call this for a given table to extract a raw view of the page. Not entirely useful every day, but fun.

CREATE TABLE Person(
FirstName char(40),
LastName char(40) )

INSERT INTO Person VALUES ('Giles','Middleton')

EXEC sp_AllocationMetadata 'Person'

Which results in this bad boy:

AllocationMetaData

The two underlined numbers are then fed into the DBCC PAGE tool

http://blogs.msdn.com/b/sqlserverstorageengine/archive/2006/06/10/625659.aspx

http://support.microsoft.com/kb/83065

Notice that we pass in the database name, not the table name. We also have to enable a trace flag in order to see this output.

DBCC TRACEON (3604);
GO

DBCC PAGE (‘test’, 1, 175, 3);

Et voilà – a memory dump!

SimplePageOutput

As you can see, there is our data, and some extra bytes before and after it.

The first 4 bytes are meta information, followed by the data, then ended with 0x020000.

0x02 is actually little-endian and represents 0x0002, or 2 which is the number of columns.

The remaining byte represents a bitmask of nullable columns. We have none, so it’s a byte of zeros.

 

For more information on the data spewed out, see Paul’s other post. http://www.sqlskills.com/blogs/paul/inside-the-storage-engine-anatomy-of-a-page/

And

http://www.sqlskills.com/blogs/paul/inside-the-storage-engine-anatomy-of-a-record/

 

 

Stored procedure – sp_AllocationMetadata

USE master;
GO

 

IF OBJECT_ID (‘sp_AllocationMetadata’) IS NOT NULL
   DROP PROCEDURE sp_AllocationMetadata;
GO

 

CREATE PROCEDURE sp_AllocationMetadata
(
@object VARCHAR (128) = NULL
)
AS
SELECT
OBJECT_NAME (sp.object_id) AS [Object Name],
sp.index_id AS [Index ID],
sa.allocation_unit_id AS [Alloc Unit ID],
sa.type_desc AS [Alloc Unit Type],
‘(‘ CONVERT (VARCHAR (6),
CONVERT (INT, SUBSTRING (sa.first_page, 6, 1) +
   SUBSTRING (sa.first_page, 5, 1))) +
‘:’ CONVERT (VARCHAR (20),
CONVERT (INT, SUBSTRING (sa.first_page, 4, 1) +
   SUBSTRING (sa.first_page, 3, 1) +
SUBSTRING (sa.first_page, 2, 1) +
SUBSTRING (sa.first_page, 1, 1))) +
‘)’ AS [First Page],
   ‘(‘ CONVERT (VARCHAR (6),
CONVERT (INT,
SUBSTRING (sa.root_page, 6, 1) +
SUBSTRING (sa.root_page, 5, 1))) +
‘:’ CONVERT (VARCHAR (20),
CONVERT (INT,
SUBSTRING (sa.root_page, 4, 1) +
SUBSTRING (sa.root_page, 3, 1) +
SUBSTRING (sa.root_page, 2, 1) +
SUBSTRING (sa.root_page, 1, 1))) +
‘)’ AS [Root Page],
‘(‘ CONVERT (VARCHAR (6),
CONVERT (INT,
SUBSTRING (sa.first_iam_page, 6, 1) +
SUBSTRING (sa.first_iam_page, 5, 1))) +
‘:’ CONVERT (VARCHAR (20),
CONVERT (INT,
SUBSTRING (sa.first_iam_page, 4, 1) +
SUBSTRING (sa.first_iam_page, 3, 1) +
SUBSTRING (sa.first_iam_page, 2, 1) +
SUBSTRING (sa.first_iam_page, 1, 1))) +
‘)’ AS [First IAM Page]
FROM
sys.system_internals_allocation_units AS sa,
sys.partitions AS sp
WHERE
sa.container_id = sp.partition_id
AND sp.object_id =
(CASE WHEN (@object IS NULL)
         THEN sp.object_id
ELSE OBJECT_ID (@object)
END);
GO

EXEC sys.sp_MS_marksystemobject sp_AllocationMetadata;
GO

 

 

 

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s