Page Internals – Investigation Proc

As many of you know, I like to crawl around in page internals in my free time. It can be very enlightening, or just a good check to make sure that what you think is happening, is actually happening. To help with this process, I’ve created myself a little stored procedure that I can simply pass a few parameters to and have it return the page data for me. So for those who don’t have anything better to do who are as interested in page internals as I am, here’s my proc:

Create Procedure dbo.dba_viewPageData_sp
        /* Declare Parameters */
          @databaseName varchar(128)
        , @tableName    varchar(128)    = Null -- database.schema.tableName
        , @indexName    varchar(128)    = Null
        , @fileNumber   int             = Null
        , @pageNumber   int             = Null
        , @printOption  int             = 3    -- 0, 1, 2, or 3
        , @pageType     char(4)         = 'Leaf' -- Leaf, Root, or IAM
    Name:       dba_viewPageData_sp
    Author:     Michelle Ufford
    Purpose:    Retrieves page data for the specified table/page.
    Notes:      Can pass either the table name or the pageID, but must pass one, or
                you'll end up with no results. 
                If the table name is passed, it will return the first page.
        @tableName must be '<databaseName>.<schemaName>.<tableName>' in order to
            function correctly for cross-database joins.  
        @printOption can be one of following values:
            0 - print just the page header
            1 - page header plus per-row hex dumps and a dump of the page slot array
            2 - page header plus whole page hex dump
            3 - page header plus detailed per-row interpretation
        Page Options borrowed from:
        @pageType must be one of the following values:
            Leaf - returns the first page of the leaf level of your index or heap
            Root - returns the root page of your index
            IAM - returns the index allocation map chain for your index or heap
        Conversions borrowed from:
    Called by:  DBA
    Date        User    Description
    2009-05-06  MFU     Initial release for public consumption
    Exec dbo.dba_viewPageData_sp
          @databaseName = 'AdventureWorks'
        , @tableName    = 'AdventureWorks.Sales.SalesOrderDetail'
        , @indexName    = 'IX_SalesOrderDetail_ProductID'
        --, @fileNumber   = 1
        --, @pageNumber   = 38208
        , @printOption  = 3
        , @pageType     = 'Root';
Set NoCount On;
Set XACT_Abort On;
Set Ansi_Padding On;
Set Ansi_Warnings On;
Set ArithAbort On;
Set Concat_Null_Yields_Null On;
Set Numeric_RoundAbort Off;
    Declare @fileID         int
        , @pageID           int
        , @sqlStatement     nvarchar(1200)
        , @sqlParameters    nvarchar(255)
        , @errorMessage     varchar(100);
    Begin Try
        If @fileNumber Is Null And @pageNumber Is Null And @tableName Is Null
            Set @errorMessage = 'You must provide either a file/page number, or a table name!';
            RaisError(@errorMessage, 16, 1);
        If @pageType Not In ('Leaf', 'Root', 'IAM')
            Set @errorMessage = 'You have entered an invalid page type; valid options are "Leaf", "Root", or "IAM"';
            RaisError(@errorMessage, 16, 1);
        If @fileNumber Is Null Or @pageNumber Is Null
            Set @sqlStatement = 
            Case When @pageType = 'Leaf' Then
                'Select Top 1 @p_fileID = Convert (varchar(6), Convert (int, 
                    SubString (au.first_page, 6, 1) +
                    SubString (au.first_page, 5, 1)))
                , @p_pageID = Convert (varchar(20), Convert (int, 
                     SubString (au.first_page, 4, 1) +
                     SubString (au.first_page, 3, 1) +
                     SubString (au.first_page, 2, 1) +
                     SubString (au.first_page, 1, 1)))'
            When @pageType = 'Root' Then
                'Select Top 1 @p_fileID = Convert (varchar(6), Convert (int, 
                    SubString (au.root_page, 6, 1) +
                    SubString (au.root_page, 5, 1)))
                , @p_pageID = Convert (varchar(20), Convert (int, 
                     SubString (au.root_page, 4, 1) +
                     SubString (au.root_page, 3, 1) +
                     SubString (au.root_page, 2, 1) +
                     SubString (au.root_page, 1, 1)))'
            When @pageType = 'IAM' Then
                'Select Top 1 @p_fileID = Convert (varchar(6), Convert (int, 
                    SubString (au.first_iam_page, 6, 1) +
                    SubString (au.first_iam_page, 5, 1)))
                , @p_pageID = Convert (varchar(20), Convert (int, 
                     SubString (au.first_iam_page, 4, 1) +
                     SubString (au.first_iam_page, 3, 1) +
                     SubString (au.first_iam_page, 2, 1) +
                     SubString (au.first_iam_page, 1, 1)))'
            End + 
            'From ' + QuoteName(ParseName(@databaseName, 1)) + '.sys.indexes AS i
            Join ' + QuoteName(ParseName(@databaseName, 1)) + '.sys.partitions AS p
                On i.[object_id] = p.[object_id]
                And i.index_id = p.index_id
            Join ' + QuoteName(ParseName(@databaseName, 1)) + '.sys.system_internals_allocation_units AS au
                On p.hobt_id = au.container_id
            Where p.[object_id] = Object_ID(@p_tableName)
                And au.first_page > 0x000000000000 ' 
                + Case When @indexName Is Null 
                    Then ';' 
                    Else 'And = @p_indexName;' End;
            Set @sqlParameters = '@p_tableName varchar(128)
                                , @p_indexName varchar(128)
                                , @p_fileID int OUTPUT
                                , @p_pageID int OUTPUT';
            Execute sp_executeSQL @sqlStatement
                        , @sqlParameters
                        , @p_tableName = @tableName
                        , @p_indexName = @indexName
                        , @p_fileID = @fileID OUTPUT
                        , @p_pageID = @pageID OUTPUT;
                Select @fileID = @fileNumber
                    , @pageID = @pageNumber;
        DBCC TraceOn (3604);
        DBCC Page (@databaseName, @fileID, @pageID, @printOption);
        DBCC TraceOff (3604);
    End Try
    Begin Catch
        Print @errorMessage;
    End Catch;
    Set NoCount Off;
    Return 0;

This proc does have cross-database support, i.e. you can install it in your DBA database and use it to investigate data in other databases. Here’s an example…

    Exec dbo.dba_viewPageData_sp
          @databaseName = 'AdventureWorks'
        , @tableName    = 'AdventureWorks.Sales.SalesOrderDetail'
        , @indexName    = 'IX_SalesOrderDetail_ProductID';

… will return a nice data page:

DBCC execution completed. If DBCC printed error messages, contact your system administrator.
PAGE: (1:11000)
BUF @0x0391F140
bpage = 0x0C0C0000                   bhash = 0x00000000                   bpageno = (1:11000)
bdbid = 7                            breferences = 0                      bUse1 = 35177
bstat = 0x1c00009                    blog = 0x21212159                    bnext = 0x00000000

[waits for the “oohs” and “aahs” to subside…]

I also give you the option to specify a specific page, in case you want to follow the page trail (i.e. m_nextPage). I’m not really providing support for partitions, although I do have a little dirty piece of code to return a hobt with data if possible (i.e. “first_page > 0x000000000000”).

Update: Special thanks to Jeremiah Peschka and Adam Machanic for showing me
QUOTENAME(PARSENAME(@databaseName, 1))! 🙂

Also, thanks to Paul Randal for his excellent blog posts on this very topic! Check out his blog post on DBCC Page. The conversion code was borrowed from his sp_AllocationMetadata proc.

Update 2: I’ve made some quick changes based on some feedback from Twitter. Thanks for the suggestions and hopefully you enjoy the updates.

Happy crawling!

If you enjoyed this post, please consider leaving a comment or subscribing to the RSS feed to have future articles delivered to your feed reader.
Tagged , , . Bookmark the permalink.

6 Responses to Page Internals – Investigation Proc

  1. Why not just make it non-injectable? Just change references of @databaseName to:

    QUOTENAME(PARSENAME(@databaseName, 1))

  2. If you SET @databaseName = QUOTENAME(@databaseName, ‘[‘); you should be safe from *most* injection style attacks. I’m sure someone more cunning than I could trick their way around such a thing.

  3. Thanks, Jeremiah and Adam! I didn’t know you could do that. 🙂

    I’ve updated my script!

  4. Jeremiah:

    I prefer to wrap the QUOTENAME around PARSENAME so that the interface can handle either quoted or non-quoted identifiers as inputs. I don’t believe there is a way to inject a quoted name… That’s the whole point 🙂

  5. It seems like we’re all learning today. Thanks to Adam, I now know a very very safe way to do clean up input from the outside world and use it safely in SQL. And, thanks to you, Michelle, I can now view horrifying details about SQL Server 🙂

  6. Pingback: Overhead in Non-Unique Clustered Indexes : SQL Fool

Leave a Reply

Your email address will not be published. Required fields are marked *