Início / dba / Perguntas / 36296
Accepted
darwindeeds
Asked: 2013-03-09 13:10:25 +0800 CST

Variáveis ​​de tabela grandes preenchem logs em tempdb?

  • 772

Estou tendo um problema com um DBA que afirma que as variáveis ​​da tabela residem no ldf do tempdb e quando uma grande quantidade de dados é carregada na variável da tabela, os logs do tempdb são preenchidos.

A solução do DBA é usar tabela temporária ao invés de variável de tabela. Embora eu veja a justificativa para usar a tabela temporária no caso de grandes conjuntos de dados, não entendo como a tabela temporária é criada e armazenada no mdf do tempdb, enquanto a variável da tabela é armazenada no ldf. Alguém por favor pode jogar alguma luz?

sql-server

1 respostas

  1. Best Answer

    Seu DBA está certo de que as ações nas variáveis ​​da tabela são registradas no tempdblog de transações, mas também as operações nas #temptabelas, portanto, a resolução proposta não ajuda. De fato, se houver alguma transação de usuário ao redor, a #tempversão da tabela pode ser pior, pois o tempdblog não pode ser truncado até que seja concluído.

    Ambos têm suas páginas alocadas no arquivo de dados (mdf).

    O entendimento de que a variável da tabela é "armazenada no ldf" é completamente incorreto. Tudo o que é gravado no log são as informações necessárias para reverter a instrução (necessário caso a instrução encontre um erro e precise ser revertida)

    Você pode ver exemplos do log envolvido usando sys.fn_dblog. O script abaixo insere algumas linhas em uma variável de tabela e #temptabela e as atualiza.

    Resultados de exemplo

    (Os registros de log relacionados à variável de tabela aparecem com o nome gerado pelo sistema #162F4418na saída abaixo)

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

    Roteiro

    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

relate perguntas