A seguinte consulta simples atingiu o tempo limite várias vezes durante os últimos dias:
SELECT Object1.*,
Object2.Column1,
Object2.Column2 AS Column3
FROM Object2 INNER JOIN Object1 ON Object2.Column2 = Object1.Column3
WHERE Object2.Column4=Variable1 AND
Object2.Column5=Variable2 AND
Object1.Column6=Variable3
Eu poderia pegá-lo no SentryOne e o vi sendo executado com frequência, executando o tempo limite de consulta de 60 segundos definido pelo aplicativo e causando constantemente 12 milhões de leituras.
Não consegui ver nada relacionado, como bloqueios ou impasses, causando o tempo limite.
Copiei a consulta e a executei no SSMS. Ele retornou em poucos ms e retornou zero linhas. Este é o plano de execução que recebi: https://www.brentozar.com/pastetheplan/?id=SJ-LK8jug
Mais tarde, fiz esta etapa novamente com a mesma consulta e os mesmos valores de parâmetro. De repente, ele funcionou por cerca de 90 segundos retornando zero linhas e obtive um plano diferente da seguinte forma: https://www.brentozar.com/pastetheplan/?id=HyVu58i_l
Como você pode ver, o número de linhas estimadas é 1 e o número de linhas reais é enorme. Isso me fez adivinhar que muitas mudanças foram feitas na tabela. Então eu olhei para os valores [sys].[dm_db_stats_properties] para as tabelas envolvidas, especialmente para OBJECT1 e os índices usados.
[Observe para evitar confusão, os planos anônimos usam os mesmos nomes para diferentes índices (Object1.Index1)]
Neste ponto, vi os seguintes valores estatísticos ...
Object1.Index1 ( referenciando o segundo plano de execução ineficiente ):
RowsInTable=3826101
RowsSampled=103245
UnfilteredRows=3826101
RowMods=2140
Histogram Steps 200
PercentChanged=0.0
Para Object1.Index2 (Índice Agrupado):
RowsInTable=3826101
RowsSampled=103734
UnfilteredRows=3826101
RowMods=2140
HistoSteps=199
PercentChanged=0.0
Então percebi que acidentalmente adicionei uma quebra de linha na primeira execução e acho que isso me fez ter um plano de execução diferente e novo.
Decidi atualizar todas as estatísticas da tabela OBJECT1. Depois disso, executei a consulta inicial novamente quando a peguei do SentryOne sem QUALQUER ALTERAÇÃO, sem quebras de linha ...
Desta vez foi rápido como esperado e o plano de execução foi idêntico ao primeiro plano eficiente. Isso me faz suspeitar que as estatísticas eram meio obsoletas.
Eu consultei as meta-informações estatísticas novamente com os seguintes resultados ( referenciando o primeiro plano eficiente) :
Object1.Index1 (Índice Agrupado)
RowsinTable=3828157
RowsSampled=104017
UnfilteredRows=3828157
RowModifications=14
HistoSteps=199
PercentCahnge=0.0
Para Object1.Index2 (Índice não clusterizado)
RowsInTable=3828157
RowsSampled=103275
UnfilteredRows=3828157
RowMods=14
HistogrSteps=127
PercentChanged=0.0
O aplicativo depois foi executado como esperado, rapidamente sem timeouts. Então eu acho que STATISTICS UPDATE ajudou aqui.
Deixe-me salientar adicionalmente que, como parte da minha manutenção automatizada de índices e estatísticas durante a noite, todos os índices da tabela foram mantidos/atualizados com sucesso durante a última noite.
Agora minhas perguntas:
Eu sei que os planos de execução são problemáticos se eles esperam poucas linhas e realmente retornam muitas linhas a mais do que o esperado. Não entendo como um plano de execução pode revelar 3.141.000 linhas se realmente retornar ZERO linhas. Como isso é possível?
A investigação na tabela OBJECT 1 e suas estatísticas não mostraram nenhuma dica para alterações maiores ou linhas adicionadas. Consultei linhas adicionadas ou alteradas desde a última manutenção automatizada de índice e estatísticas e parece que 2.370 linhas foram alteradas enquanto ~ 3.800.000 linhas estão na tabela. Esta é uma pequena quantidade alterada como os valores de [sys].[dm_db_stats_properties] também mostraram. As estatísticas poderiam realmente ser um problema aqui? os números que citei acima mostram alguma boa razão para uma atualização de estatísticas?
ATUALIZAÇÃO: Os valores para ParameterCompiledValue e ParameterRuntimeValue são idênticos no GOOD PLAN, mas diferentes no BAD PLAN. A Tabela OBJECT1 tem um valor na Coluna6 que fornece > 3 milhões de linhas, enquanto todos os outros valores fornecem um máximo de cerca de 60 mil linhas. O Plano BAD usou exatamente esse valor >3 Mio Rows para ParameterRuntimeValue enquanto foi compilado com um valor que entregaria apenas 160 Rows. Então, parece que eu preciso de um plano que aborde os dois cenários ou uma solução mais flexível que crie um plano adequado de qualquer maneira...?
Em relação à sua primeira pergunta em geral:
não entendo como um plano de execução pode revelar 3.141.000 linhas se realmente retornar ZERO linhas. Como isso é possível?
A contagem final de linhas de saída não é conhecida pelo otimizador quando ele gera um plano. Portanto, tudo o que pode considerar são as estimativas que pode calcular a partir de estatísticas. (No caso do seu plano "ruim", as linhas de saída estimadas eram na verdade 4,4, com base na primeira estimativa do plano.)
Se essas estimativas estiverem desatualizadas ou insuficientemente precisas (amostra versus varredura completa de dados distribuídos de forma desigual, por exemplo), um plano ruim poderá ser gerado mesmo com uma consulta simples.
Além disso, se um plano for reutilizado com diferentes variáveis, as estimativas das quais o plano foi gerado podem ser extremamente imprecisas. (E acho que é para isso que sp_BlitzErik está se inclinando como a causa no seu caso particular.)
ATUALIZAR:
Sua atualização mais recente mostra que os problemas que você está vendo são causados pelo sniffing de parâmetro inapropriado clássico.
A solução mais simples (se você controlar o código) é adicionar OPTION (RECOPILE) ao final da consulta problemática. Isso garantirá que o plano de instrução seja recriado em cada execução e também permitirá que certos atalhos sejam usados na criação do plano.
A desvantagem é CPU adicional e tempo gasto criando o plano em cada execução, portanto, essa solução pode não ser adequada.
Considerando a inclinação de seus dados (3 mill para um valor versus 160k max para outros), e assumindo que a inclinação não mudará muito, ramificações como esta podem resolver o problema:
Observe que "3MillValue" em dois lugares precisaria ser codificado com o valor que retorna esse valor e que OPTIMIZE FOR é a chave para essa técnica.