16 August 2016

How You Can Crack Another Session's Temp Table, And Why You Might Want To

One of the understood behaviors of a SQL Server temporary table is that (aside from global temp tables) you can't peek into them from another session. Your temp table is your own and nobody else can access it (although sysadmins can at least find row counts in tempdb, which can be handy). But every now and then you run into an odd situation where it would be incredibly handy to find out what's inside of a temporary table that you didn't create.

For instance, say you have a batch process that has been running for hours, and seems to be doing nothing of interest. You look into the code that is running, and it has a while loop based on a count of the rows in a temp table. The exact methods can and do vary considerably, this is a completely contrived example.

SET NOCOUNT ON;CREATE TABLE #TempTable ( Col1 INT, Col2 CHAR(2000));INSERT INTO #TempTable VALUES (-42691,'Odd Record'),(0,'A'),(1,'A'),(2,'A'),(3,'A'),(4,'A'),(5,'A'),(6,'A'),(7,'A'),(8,'A'),(9,'A'),(10,'A');
DECLARE @i INT = 0;WHILE EXISTS (SELECT * FROM #TempTable)BEGIN
   WAITFOR
DELAY '00:00:01';

  
--Do something with the record

  
DELETE FROM #TempTable WHERE Col1 = @i;
  
SET @i = @i+1;END


Now you may look at that and see the obvious logical flaw here, but if the table population wasn't visible to you, it might not be so clear. So you see that it is spinning forever in this while loop, and you can tell from various DMVs that the table exists with one row. But you can't see inside the table to find out why. OR CAN YOU? First let's grab the object ID...note the percent wildcard, important because SQL Server appends a bunch of silly stuff on the name:

SELECT name, [object_id], type_desc, create_date FROM tempdb.sys.tables WHERE name LIKE '#TempTable%'

name object_id type_desc create_date
#TempTable____________________000000011F66 -1590458413 USER_TABLE 2016-08-16 11:55:02


Make sure the create_date matches up to what you would expect (in case you grab a wrong but similarly named table) and take the object_id value and plug it into either DBCC IND, or my current favorite, sys.dm_db_database_page_allocations:

SELECT index_id, allocation_unit_type_desc, allocated_page_page_id, page_type, page_type_desc, page_level, extent_file_id FROM sys.dm_db_database_page_allocations (DB_ID(), -1590458413, NULL, NULL, 'DETAILED') WHERE is_allocated = 1 ORDER BY index_id, page_type;

index_id allocation_unit_type_desc allocated_page_page_id page_type page_type_desc page_level extent_file_id
0 IN_ROW_DATA 681 1 DATA_PAGE 0 1
0 IN_ROW_DATA 939 10 IAM_PAGE 0 1


In this example, we can ignore the IAM page and go straight to the one (currently) allocated data page. Note both the file ID and page ID...often very important for tempdb where multiple files is more the norm.

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

If you're new to DBCC PAGE, just Google Paul Randal and DBCC PAGE and you'll be off in no time. Just don't confuse it with DBCC WRITEPAGE or it will be a bad, bad day for you. But once you scan on down through, you can see your record. Col1 with a negative value, and our oddly written increment logic (starting from zero) will never hit that. So that's where our endless loop came from!

ParentObject Object Field VALUE
PAGE HEADER: Slot 0 Offset 0x60 Length 2011 Record Type PRIMARY_RECORD
PAGE HEADER: Slot 0 Offset 0x60 Length 2011 Record Attributes  NULL_BITMAP
PAGE HEADER: Slot 0 Offset 0x60 Length 2011 Record Size 2011
Slot 0 Offset 0x60 Length 2011 Memory Dump @0x000000003078A060 0000000000000000:   1000d807 3d59ffff 4f646420 5265636f 72642020  ..Ø.=YÿÿOdd Record  
Slot 0 Offset 0x60 Length 2011 Memory Dump @0x000000003078A060 0000000000000014:   20202020 20202020 20202020 20202020 20202020                      
extra rows…
Slot 0 Offset 0x60 Length 2011 Memory Dump @0x000000003078A060 00000000000007D0:   20202020 20202020 020000                             
Slot 0 Offset 0x60 Length 2011 Slot 0 Column 1 Offset 0x4 Length 4 Length (physical) 4 Col1 -42691
Slot 0 Offset 0x60 Length 2011 Slot 0 Column 2 Offset 0x8 Length 2000 Length (physical) 2000 Col2 Odd Record         

As seemingly obscure as this may appear, I have had to use it to find root cause of a production issue. Kudos to the Pauls (both White and Randal)...the former's article helped me last year when I needed to try this out the first time, and the latter's various blog posts and SQLskills training has helped me a lot in sorting out how and when to use these useful undocumented commands like DBCC PAGE.

Happy data hacking!

No comments: