SQL SERVER – Watching Table Variable Data in TempDB

I cannot get enough of working with TempDB and the learning never stops. Previously, when I wrote about SQL SERVER – Is tempDB behaving like a Normal DB?, SQL SERVER – Inside Temp Table Object Creation and Difference TempTable and Table Variable – TempTable in Memory a Myth many did ask me what is the difference when working with table variables is. In one of the email interactions, one of my readers asked – “Is there a way to know table variables are created? Are objects created stored as part of TempDB? Are they written to the T-Log?” 

I thought these questions were interesting. All of us work with Table Variables and assume it is just in-memory and don’t utilize anything from tempDB. Recently, during one of my post session meetings a DBA said their applications were completely built on table variables and their production servers were experiencing huge usage of TempDB. These questions made me write this blog post which I thought is worth a share. Let us look at each step one after another as I explained to my friend:

View Objects Creation and TLog

The first stage for me would be to demystify the object creation process. The thought that table variables are just memory bound objects and nothing gets written to transaction log needs to be clarified first.

Here is a simple steps to clear the doubts. 1) We will clear the T-Log of TempDB. 2) Create a table variable and then check the number of rows added in T-Log of TempDB. 3) We will add rows next. 4) Check the TLogs again and see if they are any different from just table creation. This is my development box and I can assure you there is no other process using tempdb in the meantime on my system.

USE tempdb
GO
CHECKPOINT
GO
-- Output of this is 3
SELECT COUNT(*) [RowNums] FROM sys.fn_dblog(NULL, NULL)
GO


Next is to create our dummy table variable.

DECLARE @TblVariable TABLE(
id INT NOT NULL IDENTITY (1,1)
,Name CHAR(30) NOT NULL
,DOJ DATE NOT NULL
,Company CHAR(400) NULL DEFAULT 'SQLAuth'
)
-- Output of this is 110+ in my machine
SELECT COUNT(*) [RowNums] FROM sys.fn_dblog(NULL, NULL)
GO


This small experiment confirms the fact that there is something written to the TLog for table variables creation. I went ahead and cleared the rows and then did a small experiment to see if there are additional rows getting added while I insert rows. So the second script was changed as below.

DECLARE @TblVariable TABLE(
id INT NOT NULL IDENTITY (1,1)
,Name CHAR(30) NOT NULL
,DOJ DATE NOT NULL
,Company CHAR(400) NULL DEFAULT 'SQLAuth'
)
INSERT @TblVariable  (Name, DOJ)
SELECT 'SQLAuth',GETDATE()
-- Output of this is 130+ in my machine
SELECT COUNT(*) [RowNums] FROM sys.fn_dblog(NULL, NULL)
GO


As you can see there additional rows getting added to TLog showing there is some writes even here. Feel free to look at the description fields of fn_dblog and learn more about the fine prints. My next experiment was to catch the table reference somehow using the sysobjects.

SELECT * FROM sysobjects WHERE TYPE = 'U'

This showed no rows in my runs whenever I execute as I create the table variable. If you wondered why, don’t worry the next steps will demystify.

Seeing Table Variable data

I wanted to try something different so that I can capture the table variable data somehow as it persists in the metadata tables. To mimic the same, I used the below script. Simple, yet powerful.

DECLARE @TblVariable TABLE(
id INT NOT NULL IDENTITY (1,1)
,Name CHAR(30) NOT NULL
,DOJ DATE NOT NULL
,Company CHAR(400) NULL DEFAULT 'SQLAuth'
)
INSERT @TblVariable  (Name, DOJ)
SELECT 'SQLAuth',GETDATE()
SELECT * FROM @TblVariable
WAITFOR DELAY '00:02:00'


Now this script will delay and wait for 2 mins. Since the script is waiting for 2 mins, the batch doesn’t finish and hence the table variable is not destroyed immediately as in the previous case. As the script runs, now we can query sysobjects:

SELECT * FROM sysobjects WHERE TYPE = 'U'

Now, here is our table variable and make sure to note the Object ID. We will next grab the Page which holds the data and examine the same.

SELECT allocated_page_file_id, allocated_page_page_id, *
FROM sys.dm_db_database_page_allocations(2, NULL , 1, NULL, 'DETAILED')
WHERE OBJECT_ID NOT IN (SELECT ID FROM sysobjects WHERE TYPE IN ('S','IT','SQ')) AND page_type=1 AND OBJECT_ID = -1098578155


The output for the above query shows we have Page 306 allocated for data and the next step is to examine the same. For this task, we will use DBCC PAGE and print the output.

DBCC TRACEON(3604)
GO
DBCC PAGE(2,1,306,3)
GO


This confirms our understand that table variables also are written to TempDB and can be very much viewed during its duration of existence just like any normal table. When I showed this to my friend, he was pleasantly surprised. Do let me know if you have ever got a chance to view and debug table variables like this?

Reference: Pinal Dave (https://blog.sqlauthority.com)