Início / dba / Perguntas / 227747
Accepted
Frederik Vanderhaegen
Asked: 2019-01-23 01:46:45 +0800 CST

O loop aninhado tem estimativas muito baixas devido a dados distorcidos

  • 772

No SQL Server 2016 SP2, temos uma consulta que tem uma estimativa muito baixa no operador de loop aninhado. Devido à estimativa baixa, essa consulta também é derramada no tempdb.

Se estiver correto, o SQL Server 2014+ usa a estimativa de histograma grosseiro para calcular o número estimado de linhas em uma junção.
Mas quando executo a consulta, o SQL Server usa o vetor de densidade para calcular o número de linhas estimadas.
O SQL Server está usando apenas a estimativa de histograma grosseiro se não houver nenhuma wherecláusula?

Normalmente eu usaria estatísticas filtradas para melhorar as estimativas quando tenho uma tabela com dados distorcidos. Mas neste caso isso não parece funcionar.

Existe uma maneira de melhorar as estimativas no loop aninhado?

Usando o código a seguir, você pode reproduzir os dados:

create table MyTable
(
    id int identity,
    field varchar(50),
    constraint  pk_id primary  key clustered (id)
)
go

create table SkewedTable
(
    id int identity,
    startdate datetime,
    myTableId int,
    remark varchar(50),
    constraint  pk_id primary  key clustered (id)
)

set nocount on

insert into MyTable select top 1000 [name] from master..spt_values
go

insert into SkewedTable select GETDATE(),FLOOR(RAND()*(1000))+1,REPLICATE(N'A',FLOOR(RAND()*(40))+1)
go 1000

insert into SkewedTable select GETDATE(),FLOOR(RAND()*(1000))+1,REPLICATE(N'A',FLOOR(RAND()*(40))+1)
go 

CREATE NONCLUSTERED INDEX [ix_field] ON [dbo].[MyTable]([field] ASC)
go

CREATE NONCLUSTERED INDEX [ix_mytableid] ON [dbo].[SkewedTable]([myTableId] ASC)
go

--95=varchar in sys.messages
set nocount off

;with cte as
( 
    select GETDATE() as startdate ,95 as myTableId, REPLICATE(N'B',FLOOR(RAND()*(40))+1) as remark
    union all
    select * from cte
)
insert into skewedtable select top 40000 * from cte
option(maxrecursion 0)
go

update statistics mytable with fullscan
go

update statistics skewedtable with fullscan
go
sql-server performance

2 respostas

  1. Best Answer

    Normalmente eu usaria estatísticas filtradas para melhorar as estimativas quando tenho uma tabela com dados distorcidos. Mas neste caso isso não parece funcionar.

    Você deve achar útil a seguinte estatística filtrada:

    CREATE STATISTICS [stats id (field=varchar)]
ON dbo.MyTable (id)
WHERE field = 'varchar'
WITH FULLSCAN;

    Isso fornece ao otimizador informações sobre a distribuição de idvalores que correspondem field = 'varchar' a , fornecendo uma estimativa de seletividade muito melhor para a junção:

    Plano de execução real

    O plano de execução acima mostra estimativas exatamente corretas com a estatística filtrada, levando o otimizador a escolher uma junção de hash (por motivos de custo).

    Esta informação de distribuição é muito mais importante do que o método exato usado pelo estimador para combinar os histogramas de junção ( alinhamento fino ou grosseiro ), ou mesmo as suposições gerais (por exemplo, junção simples, contenção de base).

    Se você não puder fazer isso, suas opções são amplamente descritas na resposta à sua pergunta anterior Classificar spills to tempdb devido a varchar(max) . Minha preferência provavelmente seria uma tabela temporária intermediária.

    • 7

  2. Concordo completamente com o índice filtrado, esta resposta é adicionada para expandir a outra opção que @PaulWhite mencionou, para usar uma tabela temporária intermediária e, consequentemente, se livrar do SORToperador

    Você pode adicionar um índice ou alterar o índice existente:

    CREATE INDEX IX_SkewedTable_MytableId_startdate
ON SkewedTable(myTableId,startdate)
INCLUDE(remark);

    Insira os valores em uma tabela temporária intermediária

    CREATE TABLE  #temp2(param int);
INSERT INTO  #temp2(param)
SELECT t.id 
FROM mytable t 
WHERE t.field = 'varchar';

    insira a descrição da imagem aqui

    Adicionar um índice na tabela temporária 

    CREATE INDEX IX_ID on #temp2(param);

    E, em seguida, use um CTE para remover o operador de classificação do plano de consulta 

    ;WITH CTE AS
(
select TOP(999999999999)
s.myTableId,s.id,s.remark from 
SkewedTable s
order by startdate
)
SELECT s.id , s.remark
from CTE  s
INNER JOIN #temp2 
on s.myTableId = #temp2.param
OPTION(RECOMPILE)

    Conforme mencionado por @Forrest para diminuir a classificação aqui

    Resultado:

    insira a descrição da imagem aqui

    O que remove o operador de classificação.

    • 0

relate perguntas