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:
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