Início / dba / Perguntas / 233689
Accepted
Hannah Vernon
Asked: 2019-04-02 13:06:57 +0800 CST

leituras lógicas na tabela temporária global, mas não na tabela temporária no nível da sessão

  • 772

Considere o seguinte MCVE simples:

SET STATISTICS IO, TIME OFF;
USE tempdb;

IF OBJECT_ID(N'tempdb..#t1', N'U') IS NOT NULL DROP TABLE #t1;
CREATE TABLE #t1
(
    r int NOT NULL
);

IF OBJECT_ID(N'tempdb..##t1', N'U') IS NOT NULL DROP TABLE ##t1;
CREATE TABLE ##t1
(
    r int NOT NULL
);

IF OBJECT_ID(N'dbo.s1', N'U') IS NOT NULL DROP TABLE dbo.s1;
CREATE TABLE dbo.s1 
(
    r int NOT NULL
        PRIMARY KEY CLUSTERED
);

INSERT INTO dbo.s1 (r)
SELECT TOP(10000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM sys.syscolumns sc1
    CROSS JOIN sys.syscolumns sc2;
GO

Quando executo as seguintes inserções, inserir em #t1não mostra nenhuma E/S de estatísticas para a tabela temporária. No entanto, inserir em ##t1 mostra E/S de estatísticas para a tabela temporária.

SET STATISTICS IO, TIME ON;
GO

INSERT INTO #t1 (r)
SELECT r
FROM dbo.s1;

A saída das estatísticas:

SQL Server analisa e compila o tempo:
   Tempo de CPU = 0 ms, tempo decorrido = 1 ms.
Tabela 's1'. Contagem de varredura 1, leituras lógicas 19, leituras físicas 0, leituras antecipadas 0, leituras lógicas lob 0, leituras físicas lob 0, leituras antecipadas lob 0.

 Tempos de execução do SQL Server:
   Tempo de CPU = 16 ms, tempo decorrido = 9 ms.

(10.000 linhas afetadas)
INSERT INTO ##t1 (r)
SELECT r
FROM dbo.s1;

SQL Server analisa e compila o tempo:
   Tempo de CPU = 0 ms, tempo decorrido = 1 ms.
Tabela '##t1'. Contagem de varredura 0, leituras lógicas 10016, leituras físicas 0, leituras antecipadas 0, leituras lógicas lob 0, leituras físicas lob 0, leituras antecipadas lob 0.
Tabela 's1'. Contagem de varredura 1, leituras lógicas 19, leituras físicas 0, leituras antecipadas 0, leituras lógicas lob 0, leituras físicas lob 0, leituras antecipadas lob 0.

 Tempos de execução do SQL Server:
   Tempo de CPU = 47 ms, tempo decorrido = 45 ms.

(10.000 linhas afetadas)

Por que há tantas leituras na tabela ##temp quando estou apenas inserindo nela?

sql-server sql-server-2016

1 respostas

  1. Best Answer

    O registro mínimo não está sendo usado ao usar INSERT INTOe tabelas temporárias globais

    Inserindo um milhão de linhas em uma tabela temporária global usandoINSERT INTO

    INSERT INTO ##t1 (r)
SELECT top(1000000) s1.r
FROM dbo.s1
CROSS APPLY  dbo.s1 S2;

    Ao executar SELECT * FROM fn_dblog(NULL, NULL)enquanto a consulta acima está em execução, aproximadamente 1 milhão de linhas são retornadas.

    insira a descrição da imagem aqui

    Uma LOP_INSERT_ROWoperação para cada linha + outros dados de log.

    A mesma inserção em uma tabela temporária local

    INSERT INTO #t1 (r)
SELECT top(1000000) s1.r
FROM dbo.s1
CROSS APPLY  dbo.s1 S2;

    Apenas indo até 700 linhas retornadas por SELECT * FROM fn_dblog(NULL, NULL)

    insira a descrição da imagem aqui

    Registro mínimo

    Inserindo um milhão de linhas em uma tabela temporária global usandoSELECT INTO

    SELECT top(1000000) s1.r
INTO ##t2
FROM dbo.s1
CROSS APPLY  dbo.s1 S2;

    insira a descrição da imagem aqui

    SELECT INTOuma tabela temporária global com 10k registros

    SELECT s1.r
INTO ##t2
FROM dbo.s1;

    Estatísticas de tempo e IO

    SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.
Table 's1'. Scan count 1, logical reads 19, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 16 ms,  elapsed time = 10 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

    Com base nesta postagem do blog , podemos adicionar TABLOCKpara iniciar o registro mínimo em uma tabela de heap

    INSERT INTO ##t1 WITH(TABLOCK) (r)
SELECT   s1.r
FROM dbo.s1

    Baixas leituras lógicas

    Table 's1'. Scan count 1, logical reads 19, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(10000 rows affected)

    Parte de uma resposta de @PaulWhite sobre como obter o mínimo de log em tabelas temporárias

    Não. As tabelas temporárias locais (#temp) são privadas para a sessão de criação, portanto, uma dica de bloqueio de tabela não é necessária. Uma dica de bloqueio de tabela seria necessária para uma tabela temporária global (##temp) ou uma tabela regular (dbo.temp) criada em tempdb, porque elas podem ser acessadas de várias sessões.

    Criando uma tabela regular para testar isso:

    CREATE TABLE dbo.bla
(
    r int NOT NULL 
);

    Preenchendo com 1 milhão de registros

    INSERT INTO bla 
SELECT   top(1000000)s1.r
FROM dbo.s1
CROSS APPLY  dbo.s1 S2;

    >1M de leituras lógicas nesta tabela

    Table 's1'. Scan count 17, logical reads 155, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'bla'. Scan count 0, logical reads 1001607, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    A resposta de Paul White explicando as leituras lógicas relatadas na tabela temporária global

    Geralmente, as leituras lógicas são relatadas para a tabela de destino quando a inserção não é minimamente registrada.

    Essas leituras lógicas estão associadas à localização de um local na estrutura existente para adicionar as novas linhas. As inserções minimamente registradas usam o mecanismo de carregamento em massa, que aloca páginas/extensões totalmente novas (e, portanto, não precisa ler a estrutura de destino da mesma maneira).

    Conclusão

    A conclusão é que o INSERT INTOnão é capaz de usar log mínimo, resultando no log de cada linha inserida individualmente no arquivo de log do tempdb quando usado em combinação com uma tabela temporária global/tabela normal. Considerando que a tabela temporária local/ SELECT INTO/ INSERT INTO ... WITH(TABLOCK)é capaz de usar log mínimo.

    • 11

relate perguntas