Início / dba / Perguntas / 230722
Accepted
Joe Obbish
Asked: 2019-02-26 17:27:45 +0800 CST

Por que uma mesa temporária é uma solução mais eficiente para o problema do Halloween do que um carretel ansioso?

  • 772

Considere a seguinte consulta que insere linhas de uma tabela de origem somente se elas ainda não estiverem na tabela de destino:

INSERT INTO dbo.HALLOWEEN_IS_COMING_EARLY_THIS_YEAR WITH (TABLOCK)
SELECT maybe_new_rows.ID
FROM dbo.A_HEAP_OF_MOSTLY_NEW_ROWS maybe_new_rows
WHERE NOT EXISTS (
    SELECT 1
    FROM dbo.HALLOWEEN_IS_COMING_EARLY_THIS_YEAR halloween
    WHERE maybe_new_rows.ID = halloween.ID
)
OPTION (MAXDOP 1, QUERYTRACEON 7470);

Uma forma de plano possível inclui uma junção de mesclagem e um spool antecipado. O operador de carretel ansioso está presente para resolver o problema do Halloween :

primeiro plano

Na minha máquina, o código acima é executado em cerca de 6900 ms. O código de reprodução para criar as tabelas está incluído na parte inferior da pergunta. Se estou insatisfeito com o desempenho, posso tentar carregar as linhas a serem inseridas em uma tabela temporária em vez de depender do spool ansioso. Aqui está uma implementação possível:

DROP TABLE IF EXISTS #CONSULTANT_RECOMMENDED_TEMP_TABLE;
CREATE TABLE #CONSULTANT_RECOMMENDED_TEMP_TABLE (
    ID BIGINT,
    PRIMARY KEY (ID)
);

INSERT INTO #CONSULTANT_RECOMMENDED_TEMP_TABLE WITH (TABLOCK)
SELECT maybe_new_rows.ID
FROM dbo.A_HEAP_OF_MOSTLY_NEW_ROWS maybe_new_rows
WHERE NOT EXISTS (
    SELECT 1
    FROM dbo.HALLOWEEN_IS_COMING_EARLY_THIS_YEAR halloween
    WHERE maybe_new_rows.ID = halloween.ID
)
OPTION (MAXDOP 1, QUERYTRACEON 7470);

INSERT INTO dbo.HALLOWEEN_IS_COMING_EARLY_THIS_YEAR WITH (TABLOCK)
SELECT new_rows.ID
FROM #CONSULTANT_RECOMMENDED_TEMP_TABLE new_rows
OPTION (MAXDOP 1);

O novo código é executado em cerca de 4400 ms. Posso obter planos reais e usar Actual Time Statistics™ para examinar onde o tempo é gasto no nível do operador. Observe que solicitar um plano real adiciona uma sobrecarga significativa para essas consultas, de modo que os totais não corresponderão aos resultados anteriores.

╔═════════════╦═════════════╦══════════════╗
║  operator   ║ first query ║ second query ║
╠═════════════╬═════════════╬══════════════╣
║ big scan    ║ 1771        ║ 1744         ║
║ little scan ║ 163         ║ 166          ║
║ sort        ║ 531         ║ 530          ║
║ merge join  ║ 709         ║ 669          ║
║ spool       ║ 3202        ║ N/A          ║
║ temp insert ║ N/A         ║ 422          ║
║ temp scan   ║ N/A         ║ 187          ║
║ insert      ║ 3122        ║ 1545         ║
╚═════════════╩═════════════╩══════════════╝

O plano de consulta com o spool ansioso parece gastar muito mais tempo nos operadores de inserção e spool em comparação com o plano que usa a tabela temporária.

Por que o plano com a tabela temporária é mais eficiente? Um carretel ansioso não é principalmente apenas uma tabela temporária interna? Acredito que estou procurando respostas que se concentrem nos internos. Consigo ver como as pilhas de chamadas são diferentes, mas não consigo entender o quadro geral.

Estou no SQL Server 2017 CU 11 caso alguém queira saber. Aqui está o código para preencher as tabelas usadas nas consultas acima:

DROP TABLE IF EXISTS dbo.HALLOWEEN_IS_COMING_EARLY_THIS_YEAR;

CREATE TABLE dbo.HALLOWEEN_IS_COMING_EARLY_THIS_YEAR (
ID BIGINT NOT NULL,
PRIMARY KEY (ID)
);

INSERT INTO dbo.HALLOWEEN_IS_COMING_EARLY_THIS_YEAR WITH (TABLOCK)
SELECT TOP (20000000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM master..spt_values t1
CROSS JOIN master..spt_values t2
CROSS JOIN master..spt_values t3
OPTION (MAXDOP 1);


DROP TABLE IF EXISTS dbo.A_HEAP_OF_MOSTLY_NEW_ROWS;

CREATE TABLE dbo.A_HEAP_OF_MOSTLY_NEW_ROWS (
ID BIGINT NOT NULL
);

INSERT INTO dbo.A_HEAP_OF_MOSTLY_NEW_ROWS WITH (TABLOCK)
SELECT TOP (1900000) 19999999 + ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM master..spt_values t1
CROSS JOIN master..spt_values t2;
sql-server sql-server-2017

2 respostas

  1. Best Answer

    Isso é o que eu chamo de Proteção Manual de Halloween .

    Você pode encontrar um exemplo disso sendo usado com uma instrução de atualização em meu artigo Otimizando consultas de atualização . É preciso ter um pouco de cuidado para preservar a mesma semântica, por exemplo, bloqueando a tabela de destino contra todas as modificações simultâneas enquanto as consultas separadas são executadas, se isso for relevante em seu cenário.

    Por que o plano com a tabela temporária é mais eficiente? Um carretel ansioso não é principalmente apenas uma tabela temporária interna?

    Um carretel tem algumas das características de uma tabela temporária, mas os dois não são equivalentes exatos. Em particular, um spool é essencialmente uma inserção não ordenada linha por linha em uma estrutura b-tree . Ele se beneficia de otimizações de bloqueio e registro, mas não oferece suporte a otimizações de carregamento em massa .

    Conseqüentemente, muitas vezes é possível obter melhor desempenho dividindo a consulta de maneira natural: carregamento em massa das novas linhas em uma tabela ou variável temporária e, em seguida, realizando uma inserção otimizada (sem proteção explícita de Halloween) do objeto temporário.

    Fazer essa separação também permite liberdade extra para ajustar as partes de leitura e gravação da instrução original separadamente.

    Como observação lateral, é interessante pensar em como o problema do Halloween pode ser resolvido usando versões de linha. Talvez uma versão futura do SQL Server forneça esse recurso em circunstâncias adequadas.

    Como Michael Kutz mencionou em um comentário, você também pode explorar a possibilidade de explorar a otimização de preenchimento de buracos para evitar HP explícito. Uma maneira de conseguir isso para a demonstração é criar um índice exclusivo (agrupado, se desejar) na IDcoluna de A_HEAP_OF_MOSTLY_NEW_ROWS.

    CREATE UNIQUE INDEX i ON dbo.A_HEAP_OF_MOSTLY_NEW_ROWS (ID);

    Com essa garantia, o otimizador pode usar preenchimento de furos e compartilhamento de conjunto de linhas:

    MERGE dbo.HALLOWEEN_IS_COMING_EARLY_THIS_YEAR WITH (SERIALIZABLE) AS HICETY
USING dbo.A_HEAP_OF_MOSTLY_NEW_ROWS AS AHOMNR
    ON AHOMNR.ID = HICETY.ID
WHEN NOT MATCHED BY TARGET
THEN INSERT (ID) VALUES (AHOMNR.ID);

    MERGE plano

    Embora interessante, você ainda poderá obter um melhor desempenho em muitos casos, empregando a Proteção Manual de Halloween cuidadosamente implementada.

    • 14

  2. Para expandir um pouco a resposta de Paul, parte da diferença no tempo decorrido entre as abordagens de spool e de tabela temporária parece se resumir à falta de suporte para a DML Request Sortopção no plano de spool. Com o sinalizador de rastreamento não documentado 8795, o tempo decorrido para a abordagem da tabela temporária salta de 4.400 ms para 5.600 ms.

    INSERT INTO dbo.HALLOWEEN_IS_COMING_EARLY_THIS_YEAR WITH (TABLOCK)
SELECT new_rows.ID
FROM #CONSULTANT_RECOMMENDED_TEMP_TABLE new_rows
OPTION (MAXDOP 1, QUERYTRACEON 8795);

    Observe que isso não é exatamente equivalente à inserção realizada pelo plano de spool. Essa consulta grava significativamente mais dados no log de transações.

    O mesmo efeito pode ser visto ao contrário com alguns truques. É possível incentivar o SQL Server a usar uma classificação em vez de um spool para proteção de Halloween. Uma implementação:

    INSERT INTO dbo.HALLOWEEN_IS_COMING_EARLY_THIS_YEAR WITH (TABLOCK)
SELECT TOP (987654321) 
maybe_new_rows.ID
FROM dbo.A_HEAP_OF_MOSTLY_NEW_ROWS maybe_new_rows
WHERE NOT EXISTS (
    SELECT 1
    FROM dbo.HALLOWEEN_IS_COMING_EARLY_THIS_YEAR halloween
    WHERE maybe_new_rows.ID = halloween.ID
)
ORDER BY maybe_new_rows.ID, maybe_new_rows.ID + 1
OPTION (MAXDOP 1, QUERYTRACEON 7470, MERGE JOIN);

    Agora o plano tem um operador TOP N Sort no lugar do carretel. A classificação é um operador de bloqueio, portanto, o spool não é mais necessário:

    insira a descrição da imagem aqui

    Mais importante, agora temos suporte para a DML Request Sortopção. Observando as estatísticas de tempo real novamente, o operador de inserção agora leva apenas 1623 ms. Todo o plano leva cerca de 5400 ms para ser executado sem solicitar um plano real.

    Como explica Hugo , o operador Eager Spool preserva a ordem. Isso pode ser visto mais facilmente com um TOP PERCENTplano. É lamentável que a consulta original com o spool não possa aproveitar melhor a natureza classificada dos dados no spool.

    • 5

relate perguntas