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%'
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;
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!
|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|
|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!