THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Paul White: Page Free Space

A technical SQL Server blog from New Zealand.

Viewing Another Session’s Temporary Table

Is it possible to view the contents of a local temporary table, created on a different connection?

Say we run this code on “connection 1”:

CREATE  TABLE #Private (data NVARCHAR(30) NOT NULL);
GO
INSERT #Private
(data)
VALUES (N'Only I can see this');

Is there a way to see the contents of the #Private table from “connection 2”?  It isn’t particularly easy, but a user with sysadmin permissions, a bit of internal knowledge (and some patience) can do it.

Bound Sessions

One suggestion I have heard is to use the (deprecated) Bound Sessions feature.  The idea is to get a bind token from sp_getbindtoken and use that to allow a second session to connect using sp_bindsession.  The theory is that the now-related sessions will be able to see each other’s local temporary tables.  It sounds plausible, but bound sessions only allow multiple sessions to share the same transaction and locks.  Bound sessions do not allow one connection to view another’s temporary tables.  (Even if it were possible, it might not always be practical to call sp_getbindtoken from the table-owing session.)

Reading Data Pages Directly

The winning strategy is to find and read the table’s data pages directly from memory (brought in from disk if necessary).  We start by finding the temporary table in tempdb:

SELECT  *
FROM tempdb.sys.tables T
WHERE T.name LIKE N'#Private[_]%';

Armed with the object_id provided by that query, we now need a way to find the physical data pages associated with the table, and then read the row data directly from those pages.  Easy!

Finding the Data Pages

The available ways to find the first data page for a table depends on which version of SQL Server you are running.  In SQL Server 2000, we can use the sysindexes system view, which contains a column called first.  That view is retained for compatibility in SQL Server 2005 onward (but the column has been helpfully renamed to first_page).  In SQL Server 2005 and later versions, we have another, less documented, choice: The sys.system_internals_allocation_units view, which provides the same first_page column as sysindexes.  We can join back to sys.tables via the sys.partitions view in the normal way.  This is the full query:

SELECT  T.name,
T.[object_id],
AU.type_desc,
AU.first_page,
AU.data_pages,
P.[rows]
FROM tempdb.sys.tables T
JOIN tempdb.sys.partitions P
ON P.[object_id] = T.[object_id]
JOIN tempdb.sys.system_internals_allocation_units AU
ON (AU.type_desc = N'IN_ROW_DATA' AND AU.container_id = P.partition_id)
OR (AU.type_desc = N'ROW_OVERFLOW_DATA' AND AU.container_id = P.partition_id)
OR (AU.type_desc = N'LOB_DATA' AND AU.container_id = P.hobt_id)
WHERE T.name LIKE N'#Private%';

The join from partition to allocation units depends on the type of page.  This is documented in Books Online under the sys.allocation_units entry – see the container_id column description.  Running the above query (on a second connection) produced these results on my system:

Results1

Decoding the First Page Pointer

The first_page column contains a pointer to the first data page in binary form.  Although the view itself is documented, the format of the pointer is not.  The pointer is six bytes long, with each byte is represented by two hexadecimal characters.  In the sample output above, the twelve hexadecimal characters are: AD 00 00 00 01 00.  Reading from right to left in groups of two, the first two bytes are 00 and 01.  These represent the file_id on which the first page is stored: file id #1.  The remaining four bytes (continuing to read right to left in bytes) are: 00 00 00 AD. This is page #173 in decimal.

Reading the Data Pages

Many of you will be familiar with the undocumented DBCC PAGE command, which allows anyone with sysadmin permissions to view the contents of an 8KB database page.  Now that we have file and page numbers, we can show the contents directly:

DBCC TRACEON (3604);
DBCC PAGE (tempdb, 1, 173, 3) WITH TABLERESULTS;

Trace flag 3604 is required to redirect the DBCC PAGE command’s output to the client window.  Output style 3 decodes the raw data into human-readable rows.  The WITH TABLERESULTS clause is optional, just affecting whether the output appears as printed text or in the familiar tabular form.  At the bottom of the output, we see this:

Reveal

One other interesting thing from the output is in the page header, where the field m_nextPage points to the next data page for this table.  Thankfully, that pointer is decoded for us by DBCC PAGE and appears in (file_id : page_id) decimal format.  As it is, our test table just has one page, so m_nextPage is (0:0).

Paul White
email: SQLkiwi@gmail.com
twitter: @SQL_Kiwi

Further Reading

Paul Randal – Anatomy of a Page

Acknowledgement

My thanks to David M Maxwell (twitter | blog) for his #sqlhelp question that prompted this post.

Published Saturday, August 14, 2010 11:07 AM by Paul White

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Fabiano Amorim said:

Hi Paul, some time ago I wrote a post about it,

http://bit.ly/9ch6lz

Take a look I think you will like...  :-)

Tks, keep posting , I love your blog.

August 13, 2010 8:16 PM
 

Paul White said:

Fabiano,

That's awesome - it even translates from Portugese!

I did look, and I did like it :c)

Cool script for viewing all the pages for a table too.

Paul

August 13, 2010 9:33 PM
 

dmmaxwell said:

Wow... I certainly wasn't expecting this.  I will be putting this to immediate use.

Thank you both so much for your time! :-)

August 16, 2010 6:56 AM
 

Paul White said:

You're welcome, David!

August 16, 2010 9:19 AM
 

Oliver said:

THIS IS AWESOME!!!!  One of the best real-world examples of using SQL internals!  Using it already - thanks!!!

August 17, 2010 4:35 PM
 

Paul White said:

Thanks guys!

August 18, 2010 3:04 AM

Leave a Comment

(required) 
(required) 
Submit
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement