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

Solarwinds

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'

SQL SERVER - Watching Table Variable Data in TempDB table-variable-create-01

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

SQL SERVER - Watching Table Variable Data in TempDB table-variable-create-02

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

SQL SERVER - Watching Table Variable Data in TempDB table-variable-create-03

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)

Solarwinds
Previous Post
SQL SERVER – Configuration and Performance of SQL Server is Now Easy to Master
Next Post
Interview Question of the Week #003 – How to Write Script for Database Cursor?

Related Posts

8 Comments. Leave new

  • Avinash Ghawali
    January 17, 2015 7:32 pm

    Awesome article to clear understand difference between Temp and Variable Table. Surely I will be doing same experiment in my development sandbox.

    Reply
  • Truly Genius. Thank you for sharing this post!!

    Reply
  • Hi Pinal

    Actually I have doubt

    I have SP which have 8 other stored procs . My sys CPU is Octa Core processor .I would like to execute like SP1 —-> CPU1 SP2 —-> CPU2 SP3 —-> CPU3 SP4 —-> CPU4 SP5 —-> CPU5 SP6 —-> CPU6 SP7 —-> CPU7 SP8 —-> CPU8 Is this possible ?

    Reply
  • Leonardo Milagres
    January 29, 2015 12:43 am

    I have not ever got a chance to view and debug table variables like this. Thanks!

    Reply
  • wowsome. What is the difference then?

    Reply
  • Thanks! Very nice to learn this. I was searching what these ‘temp tables’ were in the tempdb. Turned out they are table variables..However, at may DB they are empty (there are no pages referencing that table). It suspect there is some process doing that, but cannot find out what it is. Any idea?

    Reply

Leave a Reply

Menu