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 As /********************************************************************************* 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: https://blogs.msdn.com/sqlserverstorageengine/archive/2006/06/10/625659.aspx @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: http://sqlskills.com/blogs/paul/post/Inside-The-Storage-Engine- sp_AllocationMetadata-putting-undocumented-system-catalog-views-to-work.aspx 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; Begin 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 Begin Set @errorMessage = 'You must provide either a file/page number, or a table name!'; RaisError(@errorMessage, 16, 1); End; If @pageType Not In ('Leaf', 'Root', 'IAM') Begin Set @errorMessage = 'You have entered an invalid page type; valid options are "Leaf", "Root", or "IAM"'; RaisError(@errorMessage, 16, 1); End; If @fileNumber Is Null Or @pageNumber Is Null Begin 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 i.name = @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; End Else Begin Select @fileID = @fileNumber , @pageID = @pageNumber; End; 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; End Go |
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) BUFFER: 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!