Noções básicas sobre estatísticas, planos de execução e 'problema de chave ascendente'

Estou correto ao dizer que as estatísticas são usadas apenas ao criar o plano de execução

Não, estatísticas desatualizadas podem causar uma recompilação relacionada à otimização da instrução afetada.

Temos uma coluna de data/hora ascendente em uma de nossas maiores tabelas que consultamos regularmente

Planos de execução abaixo do ideal causados ​​por valores de predicados fora (especificamente acima) do intervalo de valores armazenados no histograma de estatísticas correspondente é conhecido como problema de chave ascendente . A reconstrução de estatísticas é uma solução possível, mas pode consumir muitos recursos. As alternativas incluem:

• Rastrear sinalizadores 2389 e 2390 . Isso requer que exista um índice com a coluna problemática como a chave principal. Ele não funciona com tabelas particionadas e só é eficaz no SQL Server 2014 se o estimador de cardinalidade original for usado. O sinalizador de rastreamento 4139 também pode ser necessário se o objeto de estatísticas for marcado como estacionário.

• Atualize para o SQL Server 2014. O novo estimador de cardinalidade inclui lógica para estimar além do histograma usando informações de densidade média. Isso pode ser menos preciso do que os sinalizadores de rastreamento 2389/2390 em algumas circunstâncias importantes.

• Habilite atualizações automáticas de estatísticas mais frequentes para tabelas grandes com sinalizador de rastreamento 2371 . Com este sinalizador de rastreamento, em vez de atualizar após 20% + 500 alterações, apenas SQRT(1000 * Table rows)modificações são necessárias. Essa não é uma solução tão completa quanto as mencionadas anteriormente, pois as atualizações ainda podem não ser acionadas com frequência suficiente.

Se a origem do seu problema não for tanto compilações de planos frequentes com base em valores predicados além do histograma, mas mais sobre os efeitos de ocasionalmente armazenar em cache um plano tão ruim como resultado da detecção de parâmetros, você também pode considerar:

• Desativando o sniffing de parâmetro usando o sinalizador de rastreamento 4136
• Usando OPTIMIZE FOR (@parameter = value)para compilar um plano para um valor representativo conhecido
• Usando OPTIMIZE FOR (@parameter UNKNOWN)para otimizar usando a distribuição média
• Usando OPTIMIZE FOR UNKNOWN(o mesmo que 4136, mas por consulta)
• Usando OPTION (RECOMPILE)para compilar todas as vezes, farejando o valor específico. Se a grande maioria dos valores de tempo de execução estiver dentro do histograma, isso pode ser eficaz.

Para obter mais informações sobre detecção de parâmetros, incorporação e opções de recompilação, consulte meu artigo em SQLperformance.com.

Estou correto ao dizer que as estatísticas são usadas apenas ao criar o plano de execução para um procedimento armazenado e não são usadas no contexto de execução real?

Não, o que acontece é que o plano de execução de um procedimento armazenado é armazenado em cache. Supondo que haja memória disponível suficiente para continuar mantendo o plano, ele não será alterado, a menos que ocorra uma das seguintes situações (de Cache e Reutilização do Plano de Execução na documentação do SQL Server, ênfase adicionada):

• Alterações feitas em uma tabela ou exibição referenciada pela consulta (ALTER TABLE e ALTER VIEW).
• Alterações feitas em um único procedimento, que eliminariam todos os planos desse procedimento do cache (ALTER PROCEDURE).
• Alterações em quaisquer índices usados ​​pelo plano de execução.
• Atualizações nas estatísticas usadas pelo plano de execução, geradas explicitamente a partir de uma instrução, como UPDATE STATISTICS, ou geradas automaticamente.
• Descartando um índice usado pelo plano de execução.
• Uma chamada explícita para sp_recompile.
• Grande número de alterações nas chaves (geradas por instruções INSERT ou DELETE de outros usuários que modificam uma tabela referenciada pela consulta).
• Para tabelas com gatilhos, se o número de linhas nas tabelas inseridas ou excluídas aumentar significativamente.
• Executando um procedimento armazenado usando a opção WITH RECOMPILE.

Portanto, se as estatísticas forem atualizadas, o plano em cache levará automaticamente em consideração as novas estatísticas e será recompilado.

Como você evita que os planos de execução fiquem obsoletos quando você tem cem mil linhas sendo adicionadas por dia?

Uma maneira é se houver muitas atualizações na tabela, conforme mencionado acima. Algumas centenas de milhares de linhas alteradas podem satisfazer essa condição. Mas se você quiser ter certeza ou ter um controle mais granular: atualize suas estatísticas. Você pode permitir que o SQL Server crie e gerencie estatísticas automaticamente ou faça você mesmo manualmente. Você pode encontrar mais informações sobre qualquer um dos métodos em SQL Server Auto Update e Auto Create Statistics Options . Quando/se você fizer uma reconstrução semanal de índices, isso também acionará a atualização dos planos. Faça alguns testes para ver o que é mais benéfico para você, pois atualizar as estatísticas com muita frequência pode não produzir nenhum resultado real de desempenho.

Se estivermos atualizando as estatísticas com frequência para combater esse problema, faria sentido usar a dica OPTION (RECOMPILE) na consulta desse procedimento armazenado?

Você não precisa usar RECOMPILE, pois com base no trecho acima, você pode ver que o plano de execução é atualizado adequadamente sempre que novas estatísticas estão disponíveis. Você pode estar bem com uma atualização de estatísticas do final do dia (se estiver realmente preocupado), mas não acho que seja explicitamente uma necessidade com base no que você disse até agora. Mais uma vez, porém, eu testaria para ver o impacto que isso pode ter no desempenho do procedimento armazenado e planejar de acordo.