Does large table variables fill up logs in tempdb?

Question

I'm running into an issue with a DBA who claims that table variables reside in ldf of the tempdb and when large amount of data is loaded into the table variable, the tempdb's logs fill up.

The DBA's solution is to use temporary table instead of table variable. While I see the justification to use the temp table in case of large data sets, I don't understand how temp table is created and stored in the mdf of tempdb where as the table variable is stored in ldf. Can someone please throw some light?

Answer 1

Your DBA is correct that actions on table variables are logged to the tempdb transaction log but so are operations on #temp tables so the proposed resolution doesn't help. Indeed if there is any surrounding user transaction the #temp table version may be worse as the tempdb log cannot be truncated until that completes.

Both have their pages allocated in the data file (mdf).

The understanding that the table variable is "stored in the ldf" is completely incorrect. All that gets written to the log is the information necessary to roll back the statement (required in case the statement encounters an error and needs to be rolled back)

You can see examples of the logging involved using sys.fn_dblog. The script below inserts a couple of rows in a table variable and #temp table and updates them.

Example Results

(The log records relating to the table variable show up with the system generated name #162F4418 in the output below)

+-----------------+----------+---------------------------------+-------------------+
|    Operation    | Context  |          AllocUnitName          | Log Record Length |
+-----------------+----------+---------------------------------+-------------------+
| LOP_MODIFY_ROW  | LCX_PFS  | dbo.#162F4418                   |                80 |
| LOP_FORMAT_PAGE | LCX_IAM  | dbo.#162F4418                   |                84 |
| LOP_MODIFY_ROW  | LCX_IAM  | dbo.#162F4418                   |                88 |
| LOP_FORMAT_PAGE | LCX_HEAP | dbo.#162F4418                   |                84 |
| LOP_INSERT_ROWS | LCX_HEAP | dbo.#162F4418                   |                72 |
| LOP_INSERT_ROWS | LCX_HEAP | dbo.#162F4418                   |                72 |
| LOP_MODIFY_ROW  | LCX_HEAP | dbo.#162F4418                   |               172 |
| LOP_MODIFY_ROW  | LCX_HEAP | dbo.#162F4418                   |               172 |
| LOP_MODIFY_ROW  | LCX_PFS  | dbo.#T___ ... _____000000000056 |                80 |
| LOP_FORMAT_PAGE | LCX_IAM  | dbo.#T___ ... _____000000000056 |                84 |
| LOP_MODIFY_ROW  | LCX_IAM  | dbo.#T___ ... _____000000000056 |                88 |
| LOP_FORMAT_PAGE | LCX_HEAP | dbo.#T___ ... _____000000000056 |                84 |
| LOP_INSERT_ROWS | LCX_HEAP | dbo.#T___ ... _____000000000056 |                72 |
| LOP_INSERT_ROWS | LCX_HEAP | dbo.#T___ ... _____000000000056 |                72 |
| LOP_MODIFY_ROW  | LCX_HEAP | dbo.#T___ ... _____000000000056 |               172 |
| LOP_MODIFY_ROW  | LCX_HEAP | dbo.#T___ ... _____000000000056 |               172 |
+-----------------+----------+---------------------------------+-------------------+

Script

USE tempdb 
GO   

CHECKPOINT

GO

/*Table Variable*/
DECLARE @T TABLE ([C71ACF0B-47E9-4CAD-9A1E-0C687A8F9CF3] INT, Description CHAR(100))

INSERT INTO @T VALUES (1, REPLICATE('A', 100)), (2, REPLICATE('A', 100))

UPDATE @T
SET    Description = REPLICATE ('B', 100)

/*Temporary Table*/
CREATE TABLE #T ([9E2E9F95-2B75-456B-BF2F-BAE7BCA4109F] INT, Description CHAR(100))

INSERT INTO #T VALUES (1, REPLICATE('A', 100)), (2, REPLICATE('A', 100))

UPDATE #T
SET    Description = REPLICATE ('B', 100)

/*Check the transaction log*/
SELECT  Operation,
        Context,
        AllocUnitName,
        [Log Record Length]
FROM   fn_dblog(NULL, NULL)
WHERE AllocUnitId IN (SELECT a.allocation_unit_id
                                FROM   tempdb.sys.partitions AS p
                              INNER JOIN tempdb.sys.system_internals_allocation_units AS a
                                         ON p.hobt_id = a.container_id
                                       INNER JOIN tempdb.sys.tables AS t
                                         ON t.object_id = p.object_id
                                       INNER JOIN tempdb.sys.columns AS c
                                         ON c.object_id = p.object_id
                                WHERE  c.name IN ('C71ACF0B-47E9-4CAD-9A1E-0C687A8F9CF3',
                                                  '9E2E9F95-2B75-456B-BF2F-BAE7BCA4109F'))

DROP TABLE #T