Let's start with the query in Paul White's blog post, Viewing Another Session’s Temporary Table (linked provided by @PJMahoney in a comment on the Question), as that gives us a few key pieces of information. Please note that I have added two fields to Paul's original query to handle the translation of the hex/binary value in the first_page field.
SELECT T.name,
T.[object_id],
AU.type_desc,
AU.first_page,
AU.data_pages,
P.[rows],
CONVERT(INT,
CONVERT(BINARY(2), REVERSE(SUBSTRING(AU.first_page, 5, 2)))) AS [FileIdINT],
CONVERT(INT,
CONVERT(BINARY(4), REVERSE(SUBSTRING(AU.first_page, 1, 4)))) AS [FirstPageINT]
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'#temptable%'; -- replace with your temp table name!!
You should get something similar to the following:
name object_id type_desc first_page data rows File 1st
pages Id Page
----------------------- ----------- ----------- -------------- ----- ---- ---- ---
#bob__...__000000000054 -1221199275 IN_ROW_DATA 0xF00100000100 1 4 1 496
#bob__...__000000000054 -1221199275 LOB_DATA 0xF20100000100 0 4 1 498
Before even bothering with looking at the datapages directly using DBCC PAGE, take a look at the "rows" field. Is it 0? Is it much lower than expected? Does your query, if not inserting the results into a temporary table, start returning rows well before it is finished? Or does it wait until somewhere towards the end of the query processing? Depending on what the query is doing, and the execution plan, it could be that sorting and/or parallelism is preventing any results from being released until later in the processing. If this is the case (and for a query that normally takes more than a day to run, it quite likely is the case), then there might not be anything to see here, in which case the "rows" will be 0 but that won't imply that anything is being blocked, etc.
But, if there are rows there, then you can view them by using:
DBCC PAGE(tempdb, {file_id}, {page_id}, 3) WITH TABLERESULTS;
Just replace {file_id} and {page_id} with the values returned in the FileIdINT and FirstPageINT columns respectively.
The rows in a data page are in "slots". So scroll down until you see something along the lines of Slot 0 Offset 0x60 Length 37 in the ParentObject field. There should be several rows for each "slot", depending on how many columns are in the table and if any are LOB types with enough data that won't fit in the row, etc. But in the Field field you should see the column names of the table, though the LOB types sometimes don't display the field name here and instead only show it on the far left of the Object field as "field name = [BLOB ...". In the VALUE field for each "field" row, you should see the value. Just keep in mind that:
The first several rows for each "slot" (i.e. the actual row of data), are the actual bytes of the entire row. The Object field is set to "Memory Dump @0x00..." and the Field field is empty. The VALUE field for each of these rows is displayed as "first_byte_offset: hex_values ASCII_characters". Each "Memory Dump" row shows 20 bytes, with the hex values arranged in 5 sets of 4 bytes each (1 byte = 00 - FF), and the 20 ASCII characters corresponding to each of those 20 bytes.
This representation is not translated so don't expect to see any actual data here outside of in-row string data. CHAR / VARCHAR data shows up as it would anywhere else: "test" shows up as "test". NCHAR / NVARCHAR is not displayed as Unicode. The bytes are parsed (not translated!) as if they are VARCHAR (i.e. 8=bit ASCII Extended) characters. So characters that fit in the ASCII Extended range (Code Points 0 - 255) display "normally", just with a trailing "." (the "00" in the hex). Any Code Points above 255 will be incorrectly represented since a Code Point of 0x03F4 will show up as two characters -- 0x03 and 0xF4 -- instead of ϴ.
- The next set of rows are the individual columns:
- NULLs show up as:
[NULL]
- String fields display differently depending on whether they are a LOB type (
VARCHAR(MAX), NVARCHAR(MAX), XML, etc) or not. For example, the string "BoB" shows up as:
- non-MAX
VARCHAR or NVARCHAR: BoB
VARCHAR(MAX): 0x426f42NVARCHAR(MAX): 0x42006f004200
- Unicode characters, including Supplementary Characters, do display correctly for non-LOB string types.
- LOB data that cannot fit on the page will just show a "Link X" where X starts at 0 (in the
Object field) and "RowId" in the Field field and a PageID specification (e.g. (1:501:0)) in the VALUE field.
UPDATE:
The information above only covers getting you the values in the first data page. But what if you want additional pages? There is a row towards the top that points to the next (FileID:PageID) (ParentObject = "PAGE HEADER:", Object = "Page @0x000...", Field = "m_nextPage"), but that is a bit cumbersome to get each time, and doesn't exactly allow for going directly to the last page.
Fortunately there are two ways of getting the PageID list:
The undocumented DBCC IND(database_name, object_id, index_id) function. The object_id value was returned from the query above (where we also got the FileID and PageID from). For example:
DBCC IND(tempdb, -1125198933, 0);
If using SQL Server 2012 or newer, you can use the undocumented sys.dm_db_database_page_allocations(database_id, object_id, index_id, partition_number, mode). The value for mode can be either LIMITED or DETAILED. For example:
SELECT *
FROM sys.dm_db_database_page_allocations(DB_ID(N'tempdb'), -1221199275,
NULL, NULL, 'DETAILED')
WHERE page_type = 1;
The columns for next_page_file_id and next_page_page_id are what you need.
