Início / dba / Perguntas / 230651
Accepted
Paul White
Asked: 2019-02-26 02:22:55 +0800 CST

INSERT...SELECT de linha única muito mais lento que SELECT separado

  • 772

Dada a seguinte tabela de heap com 400 linhas numeradas de 1 a 400:

DROP TABLE IF EXISTS dbo.N;
GO
SELECT 
    SV.number
INTO dbo.N 
FROM master.dbo.spt_values AS SV
WHERE 
    SV.[type] = N'P'
    AND SV.number BETWEEN 1 AND 400;

e as seguintes configurações:

SET NOCOUNT ON;
SET STATISTICS IO, TIME OFF;
SET STATISTICS XML OFF;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

A SELECTinstrução a seguir é concluída em cerca de 6 segundos ( demo , plan ):

DECLARE @n integer = 400;

SELECT
    c = COUNT_BIG(*) 
FROM dbo.N AS N
CROSS JOIN dbo.N AS N2
CROSS JOIN dbo.N AS N3
WHERE 
    N.number <= @n
    AND N2.number <= @n
    AND N3.number <= @n
OPTION
    (OPTIMIZE FOR (@n = 1));

Observação: @A OPTIMIZE FORcláusula é apenas para produzir uma reprodução de tamanho sensato que capture os detalhes essenciais do problema real, incluindo uma estimativa incorreta de cardinalidade que pode surgir por vários motivos.

Quando a saída de linha única é gravada em uma tabela, leva 19 segundos ( demo , plan ):

DECLARE @T table (c bigint NOT NULL);

DECLARE @n integer = 400;

INSERT @T
    (c)
SELECT
    c = COUNT_BIG(*) 
FROM dbo.N AS N
CROSS JOIN dbo.N AS N2
CROSS JOIN dbo.N AS N3
WHERE 
    N.number <= @n
    AND N2.number <= @n
    AND N3.number <= @n
OPTION
    (OPTIMIZE FOR (@n = 1));

Os planos de execução parecem idênticos, exceto pela inserção de uma linha.

Todo o tempo extra parece ser consumido pelo uso da CPU.

Por que a INSERTdeclaração é muito mais lenta?

sql-server performance

1 respostas

  1. Best Answer

    O SQL Server opta por verificar as tabelas de heap no lado interno das junções de loops usando bloqueios em nível de linha. Uma varredura completa normalmente escolheria o bloqueio no nível da página, mas uma combinação do tamanho da tabela e do predicado significa que o mecanismo de armazenamento escolhe bloqueios de linha, pois essa parece ser a estratégia mais barata.

    A estimativa incorreta de cardinalidade introduzida deliberadamente pelo OPTIMIZE FORmeio que os heaps são verificados muito mais vezes do que o otimizador espera e não introduz um spool como faria normalmente.

    Essa combinação de fatores significa que o desempenho é muito sensível ao número de bloqueios necessários em tempo de execução.

    A SELECTinstrução se beneficia de uma otimização que permite que bloqueios compartilhados em nível de linha sejam ignorados (tomando apenas bloqueios em nível de página compartilhados por intenção) quando não há perigo de ler dados não confirmados e não há dados fora da linha.

    A INSERT...SELECTinstrução não se beneficia dessa otimização, portanto, milhões de bloqueios RID são obtidos e liberados a cada segundo no segundo caso, juntamente com os bloqueios de nível de página compartilhados por intenção.

    A enorme quantidade de atividade de bloqueio é responsável pela CPU extra e pelo tempo decorrido.

    A solução mais natural é garantir que o otimizador (e o mecanismo de armazenamento) obtenham estimativas de cardinalidade decentes para que possam fazer boas escolhas.

    Se isso não for prático no caso de uso real, as instruções INSERTe SELECTpodem ser separadas, com o resultado do SELECTmantido em uma variável. Isso permitirá que a SELECTinstrução se beneficie da otimização de salto de bloqueio.

    A alteração do nível de isolamento também pode funcionar, seja não usando bloqueios compartilhados ou garantindo que o escalonamento de bloqueio ocorra rapidamente.

    Como um ponto de interesse final, a consulta pode ser executada ainda mais rápido do que o SELECTcaso otimizado, forçando o uso de spools usando o sinalizador de rastreamento não documentado 8691.

    • 21

relate perguntas