5

I am using SQL Server 2008 and my stored proceduce does inserts into #temptable. My procedure has been running for 32 hours (which isn't unheard of considering the amount of data that it has to aggregate) and I was wondering if outside of the stored procedure itself, I can query the temp table and view what is in the table?

The only solutions I can come up with at this time are stopping the procedure, which I do not want to do :(
1) Stop the procedure and use global temp tables ##temptableglobal
2) Stop the procedure and use actual tables temptable

EDIT

Following the tutorial linked in below, My first page returns the below

0XE601000000100

but if I try to run

DBCC TRACEON (3404)
DBCC PAGE (tempdb, 1, 173, 3) With table results

the data doesnt appear to be in plain text, for example the data in the column VALUE is showing 0 and nothing with a 0 value should be in the #temptable at all.

0

1 Answer 1

4

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): 0x426f42
      • NVARCHAR(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.

2
  • it is returning 1760 rows, whereas the count should be MUCH higher. Is their a cap at how many rows this syntax returns? Or will it return all actual data that exists in the #temptable? Nov 14, 2015 at 22:03
  • @MustangLover I highly doubt that the row count is any other than completely accurate. HOWEVER, as I mention in the answer, it is accurate for the rows that the query has returned so far. It could be that more rows are about to be returned in a few more seconds. The only way to know if this behavior is "normal" is to run this query several times, maybe once per hour, for the duration of a run. If the query completes successfully and took the expected amount of time, consider that a baseline to compare future runs against. Nov 15, 2015 at 19:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.