THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Michael Zilberstein

Reading temporary table from another session

It happens to me at least once a week – I want to check progress of some heavy script that runs in chunks over big dataset and find out that it writes intermediate data to temporary table only. Last time it happened 3 days ago when I wanted to analyze 50GB trace table on my notebook. I wrote a script that was taking 200 thousand rows at a time, parameterizing them and aggregating by different keys – host name, application etc. Usual trace analysis stuff. After an hour I wanted to check the progress but found out that intermediate results are written to temp table and of course I forgot to add debug prints. Took me some thought and ~5 minutes of coding to find the solution. How?

My trace analysis script ran over 200K rows chunks and among other things aggregated them grouping by parameterized query text. One of the measurement columns was “TotalQueries” – counter of rows in a group that could be later used to calculate average values for Reads, Writes, Duration and CPU. Every 200k rows chunk inserted rows into #BA temp table. Last step in a script, when all chunks have already been processed, was aggregation of #BA table’s data into permanent table. So in order to monitor the progress I could SUM all values of “TotalQueries” column in #BA table and compare it to number of rows in my 50GB trace table (which can be easily verified using sp_spaceused).

Of course, I new the temp table name – I wrote the initial script. So first thing was to find its object_id.

SELECT [object_id] FROM tempdb.sys.tables WHERE name LIKE '#BA%'

It brought me negative number: –1546597904 in my current demo. I don’t have access to temp table created by another session. But as admin on my own notebook I sure have access to every page in every database. So next step is to find all pages that belong to #BA table. For this task we have undocumented but widely known (and used) DBCC IND command. Since digging in every page manually and wasting hour on it wasn’t exactly my purpose, I kept DBCC IND output in the table variable and used it later in the script.

DECLARE @BATablePages TABLE
(
  
PageFID INT, PagePID INT, IAMFID INT, IAMPID INT, ObjectID BIGINT, IndexID INT, PartitionNumber INT
,
  
PartitionID BIGINT, iam_chain_type VARCHAR(64), PageType TINYINT, IndexLevel TINYINT, NextPageFID INT
,
  
NextPagePID INT, PrevPageFID INT, PrevPagePID
INT
)

INSERT INTO
@BATablePages
EXEC('DBCC IND(2, -1546597904, 1)'
)

Actually we need only PagePID column and only for leaf level data pages e.g. PageType = 1. Next step is to loop over those pages and insert their content into another table. For viewing page’s data I used another widely known (and undocumented as well) DBCC PAGE command. It is less known that DBCC PAGE can be used along with WITH TABLERESULT suffix. Note: column definitions in the following script are accidental – I just wanted them to be wide enough so that script can complete successfully.

DECLARE @rc INT, @v_PageID INT, @sql NVARCHAR(MAX)

CREATE TABLE #ind( RowNum INT NOT NULL IDENTITY(1,1), PageNum INT
NOT NULL )
CREATE TABLE #page(ParentObject VARCHAR(128), [Object] VARCHAR(512), Field VARCHAR(128), [Value] VARCHAR(8000
) )

INSERT INTO #ind( PageNum
)
SELECT
PagePID
FROM
@BATablePages
WHERE PageType =
1

SET @rc =
@@ROWCOUNT
WHILE @rc >
0
BEGIN
   SELECT
@v_PageID = PageNum FROM #ind WHERE RowNum =
@rc
      
  
SET @sql = 'DBCC PAGE(2, 1, ' + CONVERT(VARCHAR(32), @v_PageID) +
', 3) WITH TABLERESULTS'

  
INSERT INTO #page(ParentObject, [Object], Field, [Value]
)
  
EXEC(@sql
)

  
SET @rc = @rc -
1
END

So now we have all data pages inside single table but not in a format we’re used to:

image

But actually, we now have everything we need. The rest is simple:

DELETE FROM #page WHERE Field <> 'TotalQueries'
                                
SELECT SUM(CONVERT(INT, [Value])) AS
TotalQueries
FROM #page
Published Thursday, March 01, 2012 8:30 PM by Michael Zilberstein
Filed under: ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Chris said:

That's great info, but....

A lot of time long running processes you want to check on are inserting data into that temp table in batch, meaning the temp table is locked. How can I get the contents of the page/temp table before the batch is committed? The DBCC IND command is blocked while another process is writing to the temp table. How can I do the equivalent of "read uncommitted" when executing DBCC IND?

March 19, 2014 1:25 PM
 

Michael Zilberstein said:

Chris, I'm not sure that's possible using SQL Server tools. DMVs usually don't pay much attention to NOLOCK hint. If they're locked - they're locked. Logically, those DMVs sit on top of system pages like PFS, GAM and SGAM. When you add rows (and probably allocate new pages to your table), it is logical that while in transaction, your bits in PFS are locked - that is what ensures "transactability" of TRUNCATE command. So you can't query them. Probably transaction log of tempdb can help? But that's really wide shot - you'll have to work hard to decipher data there.

March 19, 2014 1:43 PM
 

Michael Zilberstein said:

Chris, I'm not sure that's possible using SQL Server tools. DMVs usually don't pay much attention to NOLOCK hint. If they're locked - they're locked. Logically, those DMVs sit on top of system pages like PFS, GAM and SGAM. When you add rows (and probably allocate new pages to your table), it is logical that while in transaction, your bits in PFS are locked - that is what ensures "transactability" of TRUNCATE command. So you can't query them. Probably transaction log of tempdb can help? But that's really wide shot - you'll have to work hard to decipher data there.

March 19, 2014 1:43 PM
 

Stephen Morris said:

Why not just use a global temp table (##temp) which is designed to be accessible from more than one session ?

July 14, 2014 5:33 AM
 

Michael Zilberstein said:

There're plenty of solutions to monitor progress: use normal and not temporary table, use global temp table as you've proposed, print some outputs (I prefer to use RAISERROR with severity 10) etc. The problem was - I've thought about monitoring progress too late, so had to find another solution. No doubt, if only I could think about it in advance, I would have used another solution, not the one I've presented here. But from my experience - the situation I've described is very common.

July 14, 2014 5:48 AM

Leave a Comment

(required) 
(required) 
Submit
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement