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!

Estimating Completion Times of a SQL Process

This is the sort of query I've written from scratch a hundred times, or done manually using my trusty old friend calc.exe. You have a running process, and you know it has to chew through so many rows, and you have a count (possibly with a nolock hint) query that can see how many it has processed, or many are remaining to process. Well, I finally wrote out an adaptable version of this query that I can use as a template when I'm trying to quickly gauge how long a process is going to take. Basically you adjust the two "count" queries for your specific scenario, set either a target count query or a target fixed value (can be zero if you're counting backwards), and set a delay time between the two count values.

DECLARE
        @t INT,                 --Target value
        @c1 INT,                --First count value
        @c2 INT,                --Second count value
        @tm1 DATETIME,          --First time value
        @tm2 DATETIME;          --Second time value

--Alter delay value depending how patient you are.
DECLARE @SampleDelay CHAR(8) = '00:01:00';

--Determine Target Count
--Alter the following based on the specifics of the situation, or use a hard coded value:
SELECT @t=
--COUNT(*) FROM someTable WHERE WITH (NOLOCK) ThisValue='this';

--Determine First Count
SELECT @tm1=GETDATE(), @c1=
--Alter the following based on the specifics of the situation:
--COUNT(*) FROM someOtherTable WITH (NOLOCK) WHERE ThisValue='that';

--Variable Delay
WAITFOR DELAY @SampleDelay;

--Determine Second Count
SELECT @tm2=GETDATE(), @c2=
--Alter the following based on the specifics of the situation...should be same as first count query barring variable names
--COUNT(*) FROM someOtherTable WITH (NOLOCK) WHERE ThisValue='that';

--Compute Estimates
PRINT 'Target Count:         ' + CAST(@t AS VARCHAR(100));
PRINT 'Initial Count:        ' + CAST(@c1 AS VARCHAR(100)) + ' at ' + CAST(@tm1 AS VARCHAR(100));
PRINT 'Second Count:         ' + CAST(@c2 AS VARCHAR(100)) + ' at ' + CAST(@tm2 AS VARCHAR(100));
PRINT 'Elapsed seconds:      ' + CAST(DATEDIFF(second,@tm1,@tm2) AS VARCHAR(100));
PRINT 'Records processed:    ' + CAST(ABS(@c2-@c1) AS VARCHAR(100));
PRINT 'Rate per second:      ' + CAST(((@c2-@c1)*1.0)/DATEDIFF(second,@tm1,@tm2) AS VARCHAR(100));
PRINT 'Records remaining:    ' + CAST(ABS(@t - @c2) AS VARCHAR(100));
PRINT 'Seconds remaining:    ' + CAST(CAST((@t - @c2)/(((@c2-@c1)*1.0)/DATEDIFF(second,@tm1,@tm2)) AS INT) AS VARCHAR(100));
PRINT 'Estimated completion: ' + CAST(DATEADD(second,(@t - @c2)/(((@c2-@c1)*1.0)/DATEDIFF(second,@tm1,@tm2)), GETDATE()) AS VARCHAR(100));

Gives you rate information, time and records remaining, and calculates an estimated completion time. Here's a sample of the output:

Target Count: 20000
Initial Count: 1346 at Aug 15 2016 2:05PM
Second Count: 1498 at Aug 15 2016 2:06PM
Elapsed seconds: 60
Records processed: 152
Rate per second: 2.533333333333
Records remaining: 18502
Seconds remaining: 7303
Estimated completion: Aug 15 2016 4:08PM