主页 / dba / 问题 / 36296
Accepted
darwindeeds
Asked: 2013-03-09 13:10:25 +0800 CST

大表变量是否会填满 tempdb 中的日志？

  • 772

我遇到了一个 DBA 的问题，他声称表变量驻留在 tempdb 的 ldf 中，当大量数据加载到表变量中时，tempdb 的日志就会填满。

DBA 的解决方案是使用临时表而不是表变量。虽然我看到在大型数据集的情况下使用临时表的理由，但我不明白临时表是如何创建和存储在 tempdb 的 mdf 中的，而表变量存储在 ldf 中。有人可以请一些光吗？

sql-server

1 个回答

  1. Best Answer

    您的 DBA 是正确的，对表变量的操作记录到tempdb事务日志中，但对#temp表的操作也是如此，因此建议的解决方案没有帮助。实际上，如果周围有任何用户事务，则#temp表版本可能会更糟，因为在tempdb完成之前无法截断日志。

    两者都在数据文件（mdf）中分配了它们的页面。

    表变量“存储在ldf中”的理解是完全错误的。写入日志的所有内容都是回滚语句所需的信息（如果语句遇到错误并需要回滚，则需要）

    您可以使用 . 查看所涉及的日志记录示例sys.fn_dblog。下面的脚本在表变量和表中插入几行#temp并更新它们。

    示例结果

    （与表变量相关的日志记录与系统生成的名称一起显示#162F4418在下面的输出中）

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

    脚本

    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
    • 10

相关问题