Parâmetro Sniffing vs VARIÁVEIS vs Recompilar vs OPTIMIZE FOR UNKNOWN

a consulta é

SELECT SUM(Amount) AS SummaryTotal
FROM   PDetail WITH(NOLOCK)
WHERE  ClientID = @merchid
       AND PostedDate BETWEEN @datebegin AND @dateend 

A tabela contém 103.129.000 linhas.

O plano rápido pesquisa por ClientId com um predicado residual na data, mas precisa fazer 96 pesquisas para recuperar o arquivo Amount. A <ParameterList>seção no plano é a seguinte.

        <ParameterList>
          <ColumnReference Column="@dateend" 
                           ParameterRuntimeValue="'2013-02-01 23:59:00.000'" />
          <ColumnReference Column="@datebegin" 
                           ParameterRuntimeValue="'2013-01-01 00:00:00.000'" />
          <ColumnReference Column="@merchid" 
                           ParameterRuntimeValue="(78155)" />
        </ParameterList>

O plano lento procura por data e tem pesquisas para avaliar o predicado residual em ClientId e para recuperar o valor (Estimated 1 vs Real 7.388.383). a <ParameterList>seção é

        <ParameterList>
          <ColumnReference Column="@EndDate" 
                           ParameterCompiledValue="'2013-02-01 23:59:00.000'" 
                           ParameterRuntimeValue="'2013-02-01 23:59:00.000'" />
          <ColumnReference Column="@BeginDate" 
                           ParameterCompiledValue="'2013-01-01 00:00:00.000'"               
                           ParameterRuntimeValue="'2013-01-01 00:00:00.000'" />
          <ColumnReference Column="@ClientID" 
                           ParameterCompiledValue="(78155)" 
                           ParameterRuntimeValue="(78155)" />
        </ParameterList>

Neste segundo caso, o nãoParameterCompiledValue está vazio. O SQL Server detectou com êxito os valores usados ​​na consulta.

O livro "SQL Server 2005 Practical Troubleshooting" fala sobre o uso de variáveis ​​locais

Usar variáveis ​​locais para impedir o sniffing de parâmetros é um truque bastante comum, mas as dicas OPTION (RECOMPILE)e OPTION (OPTIMIZE FOR)... geralmente são soluções mais elegantes e um pouco menos arriscadas

---

###Observação

No SQL Server 2005, a compilação em nível de instrução permite que a compilação de uma instrução individual em um procedimento armazenado seja adiada até pouco antes da primeira execução da consulta. A essa altura, o valor da variável local seria conhecido. Teoricamente, o SQL Server poderia tirar proveito disso para farejar valores de variáveis ​​locais da mesma forma que fareja parâmetros. No entanto, como era comum usar variáveis ​​locais para impedir a detecção de parâmetros no SQL Server 7.0 e no SQL Server 2000+, a detecção de variáveis ​​locais não foi habilitada no SQL Server 2005. Ela pode ser habilitada em uma versão futura do SQL Server, o que é uma boa razão para usar uma das outras opções descritas neste capítulo se você tiver uma escolha.

---

A partir de um teste rápido, o comportamento descrito acima ainda é o mesmo em 2008 e 2012 e as variáveis ​​não são detectadas para compilação adiada, mas apenas quando uma OPTION RECOMPILEdica explícita é usada.

DECLARE @N INT = 0

CREATE TABLE #T ( I INT );

/*Reference to #T means this statement is subject to deferred compile*/
SELECT *
FROM   master..spt_values
WHERE  number = @N
       AND EXISTS(SELECT COUNT(*) FROM #T)

SELECT *
FROM   master..spt_values
WHERE  number = @N
OPTION (RECOMPILE)

DROP TABLE #T 

Apesar da compilação adiada, a variável não é detectada e a contagem de linhas estimada é imprecisa

Portanto, presumo que o plano lento esteja relacionado a uma versão parametrizada da consulta.

O ParameterCompiledValueé igual a ParameterRuntimeValuepara todos os parâmetros, portanto, isso não é uma detecção de parâmetro típica (em que o plano foi compilado para um conjunto de valores e executado para outro conjunto de valores).

O problema é que o plano compilado para os valores de parâmetro corretos é inadequado.

Você provavelmente está enfrentando o problema com as datas ascendentes descritas aqui e aqui . Para uma tabela com 100 milhões de linhas, você precisa inserir (ou modificar) 20 milhões antes que o SQL Server atualize automaticamente as estatísticas para você. Parece que da última vez que eles foram atualizados, zero linhas corresponderam ao intervalo de datas na consulta, mas agora 7 milhões correspondem.

Você pode agendar atualizações de estatísticas mais frequentes, considerar sinalizadores de rastreamento 2389 - 90ou usar OPTIMIZE FOR UKNOWNapenas suposições, em vez de poder usar as estatísticas atualmente enganosas na datetimecoluna.

Isso pode não ser necessário na próxima versão do SQL Server (após 2012). Um item Connect relacionado contém a resposta intrigante

Postado pela Microsoft em 28/08/2012 às 13h35
Fizemos um aprimoramento da estimativa de cardinalidade para a próxima versão principal que basicamente corrige isso. Fique atento aos detalhes assim que nossas prévias forem lançadas. Eric

Essa melhoria de 2014 é analisada por Benjamin Nevarez no final do artigo:

Uma primeira olhada no novo estimador de cardinalidade do SQL Server .

Parece que o novo estimador de cardinalidade retrocederá e usará a densidade média neste caso, em vez de fornecer a estimativa de 1 linha.

Alguns detalhes adicionais sobre o estimador de cardinalidade de 2014 e o problema de chave ascendente aqui:

Nova funcionalidade no SQL Server 2014 – Parte 2 – Nova estimativa de cardinalidade

Então, minha pergunta é esta... como o sniffing de parâmetro pode ser o culpado quando obtemos a mesma consulta lenta em um cache de plano vazio... não deveria haver nenhum parâmetro para sniffar?

Quando o SQL Server compila uma consulta contendo valores de parâmetro, ele detecta os valores específicos desses parâmetros para estimativa de cardinalidade (contagem de linhas). No seu caso, os valores específicos de @BeginDate, @EndDatee @ClientIDsão usados ​​ao escolher um plano de execução. Você pode encontrar mais detalhes sobre a detecção de parâmetros aqui e aqui . Estou fornecendo esses links de plano de fundo porque a pergunta acima me faz pensar que o conceito é mal compreendido no momento - sempre há valores de parâmetro para farejar quando um plano é compilado.

De qualquer forma, isso não vem ao caso, porque a detecção de parâmetros não é o problema aqui, como Martin Smith apontou. No momento em que a consulta lenta foi compilada, as estatísticas indicaram que não havia linhas para os valores detectados de @BeginDatee @EndDate:

Os valores detectados são muito recentes, sugerindo o problema da chave ascendente que Martin menciona. Como se estima que a busca de índice nas datas retorne apenas uma única linha, o otimizador escolhe um plano que envia o predicado ClientIDpara o operador Key Lookup como um resíduo.

A estimativa de linha única também é o motivo pelo qual o otimizador para de procurar planos melhores, retornando uma mensagem Good Enough Plan Found. O custo total estimado do plano lento com a estimativa de linha única é de apenas 0,013136 unidades de custo, portanto, não faz sentido tentar encontrar algo melhor. Exceto, é claro, que a busca realmente retorna 7.388.383 linhas em vez de uma, causando o mesmo número de pesquisas de chave.

As estatísticas podem ser difíceis de manter atualizadas e úteis em tabelas grandes e o particionamento apresenta seus próprios desafios a esse respeito. Eu mesmo não tive sucesso particular com sinalizadores de rastreamento 2389 e 2390, mas você pode testá-los. Compilações mais recentes do SQL Server (R2 SP1 e posterior) têm atualizações de estatísticas dinâmicas disponíveis, mas essas atualizações de estatísticas por partição ainda não foram implementadas. Enquanto isso, você pode agendar uma atualização manual de estatísticas sempre que fizer alterações significativas nesta tabela.

Para esta consulta específica, eu pensaria em implementar o índice sugerido pelo otimizador durante a compilação do plano de consulta rápida:

/*
The Query Processor estimates that implementing the following index could improve
the query cost by 98.8091%.

WARNING: This is only an estimate, and the Query Processor is making this 
recommendation based solely upon analysis of this specific query.
It has not considered the resulting index size, or its workload-wide impact,
including its impact on INSERT, UPDATE, DELETE performance.
These factors should be taken into account before creating this index.
*/
CREATE NONCLUSTERED INDEX [<Name of Missing Index>]
ON [dbo].[PDetail] ([ClientID],[PostedDate])
INCLUDE ([Amount]);

O índice deve ser alinhado à partição, com uma ON PartitionSchemeName (PostedDate) cláusula, mas o ponto é que fornecer um caminho de acesso a dados obviamente melhor ajudará o otimizador a evitar escolhas de plano ruins, sem recorrer a OPTIMIZE FOR UNKNOWNdicas ou soluções antiquadas como o uso de variáveis ​​locais.

Com o índice aprimorado, a pesquisa de chave para recuperar a Amountcoluna será eliminada, o processador de consulta ainda pode executar a eliminação de partição dinâmica e usar uma busca para localizar o ClientIDintervalo de datas e específico.

Eu tive exatamente o mesmo problema em que um procedimento armazenado ficou lento OPTIMIZE FOR UNKNOWNe RECOMPILEas dicas de consulta resolveram a lentidão e aceleraram o tempo de execução. No entanto, os dois métodos a seguir não afetaram a lentidão do procedimento armazenado: (i) Limpando o cache (ii) usando WITH RECOMPILE. Então, assim como você disse, não foi realmente um sniffing de parâmetro.

Os sinalizadores de rastreamento 2389 e 2390 também não ajudaram. Apenas atualizar as estatísticas ( EXEC sp_updatestats) fez isso por mim.