Plano de execução ruim após atualização de estatísticas devido à tabela temporária

O plano inválido tem um aviso na tabela temporária de que a coluna de junção não possui estatísticas. O que da?

Pode haver uma razão mais esotérica para isso, mas é mais provável que seja uma simples falha na criação de estatísticas. Isso pode, por exemplo, ocorrer quando a tarefa não consegue obter os recursos de memória necessários ou quando a criação de estatísticas está sendo limitada (muitas compilações simultâneas). Consulte o Microsoft White Paper Statistics Usado pelo Query Optimizer no Microsoft SQL Server 2008 . Você pode depurar isso ainda mais olhando para as estatísticas automáticas do Profiler ou Extended Events e outros eventos na mesma época.

Dito isso, muito mais informações e investigações seriam necessárias para colocar a culpa pela seleção do plano na porta das estatísticas da tabela temporária ausentes. Mesmo sem estatísticas detalhadas, o otimizador ainda pode ver a cardinalidade total da tabela temporária, e isso parece ser um fator importante aqui.

...mas pode ser recompilado para o bom plano logo em seguida. Mesmos parâmetros compilados.

O @Monthsparâmetro pode ser o mesmo, mas o número de linhas na tabela temporária (da visão desconhecida View_Feeder) é diferente e os planos fornecidos não mostram o valor de @MyId.

Partindo das informações disponíveis: O plano 'bom' (somente estimativas, sem dados de desempenho apresentados) é baseado em uma tabela temporária contendo 4 linhas . O 'plano ruim' é baseado em uma tabela temporária com 114 linhas . Certamente a falta de informações de densidade e histograma pode não ser útil, mas é fácil ver como o otimizador pode escolher um plano diferente para 4 versus 114 linhas, embora com densidade e distribuição desconhecidas.

Se as estimativas dos operadores do plano não dependentes da tabela temporária estiverem muito erradas, isso é um forte sinal de que as estatísticas atuais da tabela principal não são representativas dos dados subjacentes. A falta de informação na pergunta torna isso impossível de avaliar.

No entanto, é possível ver que o otimizador está sendo solicitado a escolher entre alternativas sub-ótimas aqui. Nenhum dos planos apresentados representa uma escolha 'obviamente boa', uma vez que ambos envolvem pesquisas (falta de um índice de 'cobertura') e filtragem tardia (veja a seguir). As pesquisas em particular têm um alto custo associado a elas, que depende sensivelmente das estimativas de cardinalidade.

O uso de uma visualização restringe as opções do otimizador e das dicas:

• A visão contém um GROUP BYque impede que o predicado vp.Col3 > DATEADD(MONTH, @Months * -1, GETDATE())seja empurrado para baixo, mesmo que a transformação seja válida neste caso muito específico.
  • O alinhamento da exibição para a consulta forneceria uma maneira natural de filtrar a coluna de data/hora mais cedo (embora a pergunta não indique se a refatoração da consulta é uma opção).
• Não é possível sugerir um índice em uma visão e FORCESEEKsimplesmente pede ao otimizador para encontrar qualquer plano de busca de índice (não necessariamente usando o índice que você preferir). A remoção da visualização também removeria essa restrição.

Permitir que o predicado seja empurrado para baixo também deve abrir oportunidades de indexação na tabela grande. Por exemplo:

CREATE INDEX give_me_a_good_name
ON dbo.LargeTable (Col1, [Status], Col4) 
INCLUDE (P_Id);

...fornece um bom caminho de acesso para a consulta reescrita:

DECLARE @Date datetime = DATEADD(MONTH, @Months * -1, GETDATE());

SELECT
    MT.Col1,
    ST.Col2,
    MAX(LT.Col4)
FROM #MyTemp AS MT
JOIN dbo.LargeTable AS LT
    ON LT.Col1 = MT.Col1
JOIN dbo.SmallTable AS ST
    ON ST.P_id = LT.P_Id
WHERE
    LT.[Status] IN (3, 4)
    AND LT.Col4 > @Date
GROUP BY
    MT.Col1,
    ST.Col2
OPTION (RECOMPILE);

Outra consideração é o efeito do cache temporário de tabelas e estatísticas, conforme descrito em meus artigos Tabelas temporárias em procedimentos armazenados e Cache de tabela temporária explicados . Se um bom plano depende do conteúdo atual do objeto temporário, um explícito UPDATE STATISTICS #MyTemp;antes da consulta principal e adicionar OPTION (RECOMPILE)à consulta principal pode ser uma boa solução.

Como alternativa, se uma forma de plano específica for sempre ideal para essa consulta, você terá muitas opções disponíveis, incluindo uma variedade de dicas, guias de plano e imposição de plano de armazenamento de consultas. Você pode achar que usar uma variável de tabela em vez de uma tabela temporária é a melhor escolha, pois favorece o caso de baixa cardinalidade e não fornece (ou depende de) estatísticas.

Em resumo, existem várias melhorias gerais que devem ser realizadas antes de se preocupar com as razões para (do efeito de) estatísticas ausentes ocasionais na tabela temporária:

• Certifique-se de que as estatísticas sejam representativas e úteis para o otimizador
• Verifique os valores reais versus as estimativas para um intervalo de valores de parâmetros
• Forneça bons caminhos de acesso a dados para a consulta, melhorando os índices existentes
• Remova a vista se possível; ou considere uma 'exibição parametrizada' (função com valor de tabela embutida) com um predicado explícito para o parâmetro de data/hora.
• Certifique-se de que a criação automática de estatísticas não esteja sendo limitada desnecessariamente
• Use o tipo certo de objeto temporário para a tarefa (tabela vs. variável)
• Considere RECOMPILEse a escolha do plano é muito sensível aos valores dos parâmetros
• Adicione UPDATE STATISTICSe RECOMPILEse as estatísticas em cache forem um problema
• Considere uma tabela temporária com uma chave primária em vez de SELECT INTOfornecer informações úteis para o otimizador
• Revise o esquema para garantir que o otimizador tenha o máximo de informações possível (por exemplo, chaves estrangeiras, outras restrições)
• Considere a adequação dos índices/estatísticas filtradas com base no seu conhecimento dos dados
• Não espalhe NOLOCKdicas para aumentar o desempenho

Reprodução

O seguinte foi construído a partir das informações limitadas disponíveis nos planos de execução redigidos fornecidos:

DROP VIEW IF EXISTS dbo.View_VP;
DROP TABLE IF EXISTS dbo.SmallTable, dbo.LargeTable, #MyTemp;
GO
CREATE TABLE LargeTable (P_Id integer NOT NULL, Status integer NOT NULL, Col1 integer NOT NULL, Col4 datetime NOT NULL);
CREATE TABLE SmallTable (P_id integer NOT NULL, Col2 integer NOT NULL)
CREATE TABLE #MyTemp (Col1 integer NOT NULL);
GO
CREATE VIEW dbo.View_VP 
AS
    SELECT
        pp.Col1,
        pd.Col2 AS Col2,
        MAX(pp.Col4) AS Col3
    FROM LargeTable pp
    JOIN SmallTable pd
        ON pd.P_id = pp.P_Id
    WHERE 
        pp.[Status] IN (3, 4)
    GROUP BY 
        pp.Col1, pd.Col2;
GO
CREATE UNIQUE CLUSTERED INDEX PK_SmallTable ON dbo.SmallTable (P_id)
CREATE CLUSTERED INDEX ix_P_id ON dbo.LargeTable (P_Id)
CREATE INDEX ix_Col1 ON dbo.LargeTable (Col1)
CREATE INDEX ix_Status ON dbo.LargeTable ([Status])
GO
UPDATE STATISTICS dbo.LargeTable WITH ROWCOUNT = 32268200, PAGECOUNT = 322682;
UPDATE STATISTICS dbo.SmallTable WITH ROWCOUNT = 6349, PAGECOUNT = 63;
UPDATE STATISTICS #MyTemp WITH ROWCOUNT = 4;

A consulta é:

DECLARE @Months integer = 6;

SELECT wd.Col1
         , vp.Col2
         , vp.Col3 
    FROM dbo.View_VP vp WITH (FORCESEEK)
    INNER JOIN #MyTemp wd ON wd.Col1 = vp.Col1
    WHERE vp.Col3 > DATEADD(MONTH, @Months * -1, GETDATE())

Sem estatísticas reais nas tabelas base, isso favorece planos próximos ao exemplo do 'plano ruim' (usandoix_Status ):

Isso sugere que a informação sobre a seletividade de Col1é um fator importante na escolha do otimizador.