Início / dba / Perguntas / 228998
Accepted
sepupic
Asked: 2019-02-07 01:18:55 +0800 CST

Por que o primeiro plano de execução não usa RowCount Spool?

  • 772

Aqui está meu repro que foi testado em SQL Server 2008 R2, 2012, 2016.

A segunda e a terceira consultas usam RowCount Spool, por que a primeira não?

create table dbo.t (id int identity primary key, v int);
--create statistics ST_t__v on dbo.t(v) with norecompute;

insert into dbo.t (v)
select top (10000) rand(checksum(newid())) * 5
from master.dbo.spt_values a cross join
     master.dbo.spt_values b;
go

declare @v int;

set statistics xml, io on;
select @v = v from dbo.t where exists(select 1 from dbo.t where v = 4);
select @v = v from dbo.t where exists(select 1 from dbo.t where v = 4 having count(*) > 0);
select @v = v from dbo.t where exists(select 1 from dbo.t where v = 40);
set statistics xml, io off;
go

drop table dbo.t;
go

Como resultado, as estatísticas de IO são:

Table 't'. Scan count 2, ***logical reads 20024***, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.


Table 't'. Scan count 2, logical reads 48, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.


Table 't'. Scan count 2, logical reads 27, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

insira a descrição da imagem aqui

sql-server optimization

1 respostas

  1. Best Answer

    Esta é apenas uma decisão baseada em custos.

    Há aproximadamente 2.000 linhas na tabela para todos os vvalores de 0até 4inclusive e nenhum outro valor de v.

    A semijunção pode parar de solicitar linhas depois que a primeira for retornada, de modo que o SQL Server assume que só precisará ler 5 linhas antes de encontrar a primeira correspondente v=4.

    Quando você o adiciona having count(*) > 0, agora precisa ler toda a tabela para obter o COUNT(*)e não pode parar depois que a primeira linha correspondente for encontrada. Com v=40isso não existe nos dados, mas o SQL Server novamente precisa ler a tabela inteira para ter certeza disso.

    O custo de ler a tabela inteira para cada linha externa versus apenas ler algumas linhas dela é suficiente para tornar o plano com a otimização do spool de contagem de linhas atraente.

    Você pode tentar o seguinte com v=4e v=40e comparar os resultados em uma ferramenta de comparação de texto.

    SELECT v
FROM   dbo.t
WHERE  EXISTS (SELECT 1 FROM dbo.t WHERE v = 40) 
OPTION (RECOMPILE, QUERYTRACEON 3604, QUERYTRACEON 8615);

    Os resultados do memorando final são muito comparáveis ​​em termos de opções exploradas, mas um resultado final diferente foi escolhido por motivos de custo.

    O plano com o carretel escolheu PhyOp_LoopsJoinx_jtLeftSemi 6.2 13.1e sem ele foi PhyOp_LoopsJoinx_jtLeftSemi 6.2 8.2.

    6.2é a varredura de índice clusterizado que é a mesma em ambos os planos.

    8.2é a varredura com filtro executado 10.000 vezes.

    13.1é o spool construído a partir da execução da varredura com filtro uma vez.

    Essas partes do memorando são copiadas abaixo. Quando v=4o custo do carretel é 1.0043mas o custo sem carretel é menor (em 0.867567)

    v=40

    8.2 PhyOp_Filter 7.1 4.0  Cost(RowGoal 0,ReW 9999,ReB 0,Dist 0,Total 0)= 158.804 (Distance = 1)
13.1 PhyOp_Spool 8.4  Cost(RowGoal 0,ReW 9999,ReB 0,Dist 0,Total 0)= 1.03564 (Distance = 2)

    v=4

    8.2 PhyOp_Filter 7.1 4.0  Cost(RowGoal 1,ReW 9999,ReB 0,Dist 0,Total 0)= 0.867567 (Distance = 1)
13.1 PhyOp_Spool 8.4  Cost(RowGoal 1,ReW 9999,ReB 0,Dist 0,Total 0)= 1.0043 (Distance = 2)

    A dica OPTION (NO_PERFORMANCE_SPOOL)remove o spool de contagem de linhas do v=40plano para fins de comparação.

    Eu esperava que USE HINT ('DISABLE_OPTIMIZER_ROWGOAL')isso fizesse o oposto para o v=4plano, mas apesar do grupo 8.2agora ser custeado no memorando, 158.804ele ainda é selecionado e o plano geral é custeado apenas 0.939204porque o próprio custo anti-semi join ainda reduz os custos para refletir que ele será requerem apenas uma linha dessa entrada. Além disso, com essa dica, não há PhyOp_Spoolopção disponível no memorando final.

    Paul White aponta nos comentários que o carretel de desempenho pode ser alcançado com o uso de OPTION (QUERYTRACEON 8691).

    Se o lote a seguir for executado, a segunda consulta deverá aparecer como sendo mais de 50% do custo do lote (ou seja, mais caro de acordo com o modelo de custo do SQL Server)

    declare @v int;
select @v = v from dbo.t where exists(select 1 from dbo.t where v = 4) 
select @v = v from dbo.t where exists(select 1 from dbo.t where v = 4) OPTION (QUERYTRACEON 8691);
    • 6

relate perguntas