Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

migrated from stackoverflow.com Mar 9 '13 at 0:28

This question came from our site for professional and enthusiast programmers.

1 Answer 1

up vote 10 down vote accepted

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
share|improve this answer
1  
@AaronBertrand - Thanks! I've added some of the pertinent information from their to this answer just looking at logging. –  Martin Smith Mar 8 '13 at 21:41
    
@MartinSmith Thanks for a great response. With tempdb in simple recovery should the log space be released after the transaction has been complete? DBA said it was not release in case of Table Variable. –  darwindeeds Mar 8 '13 at 22:11
    
@Darwindeeds - If you aren't using explicit multi statement transactions then the behavior of both should be the same AFAIK and if you are using user transactions then the table variable should release earlier as those transactions don't have to wait for the user transaction to be committed (they only have to support statement rollback not rollback of the entire user transaction) –  Martin Smith Mar 8 '13 at 22:21

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.