Estou usando dbcc show_statistics procurando dados de distorção no meu histograma e melhorando a qualidade das minhas estatísticas .
OPTIMIZE FOR UNKNOWN não usa um valor - em vez disso, usa o vetor de densidade.
Se você executar DBCC SHOWSTATISTICS , será o valor listado na coluna "All density " do segundo conjunto de resultados.
Nas imagens abaixo, devido à distorção dos dados, há uma diferença entre a estimativa e o número real de linhas.
Como lidar efetivamente com dados muito distorcidos?
isso fala sobre @variables e recompile , o que pode ajudar e fazer parte de uma solução.
Estatísticas filtradas parecem ser a solução mais provável neste caso .
Pergunta:
Como encontro no plano de execução em cache , as consultas que possuem diferença entre estimativa e número real de linhas?
Não há resposta perfeita. Primeiro, e mais importante, trabalhe para descobrir quanto, com que frequência e de que maneira você pode precisar atualizar suas estatísticas manualmente. A manutenção automática é boa o suficiente para 80-90% de suas estatísticas, mas é totalmente inadequada para o restante, então, passo um, envolva atualizações manuais. A frequência depende realmente de seus dados e da taxa de alteração. Além disso, varredura de amostra versus varredura completa, outra coisa que você terá que determinar com base em seus dados, na distribuição e na taxa de alteração.
Feito isso, você ainda pode ver a distorção e vê-la afetando negativamente o desempenho da sua consulta. Existem várias ferramentas possíveis para lidar com isso, nenhuma delas do tipo "impecável, tamanho único" de soluções. Em vez disso, tudo é "depende".
Uma maneira é RECOMPILAR (no nível de instrução, por favor) dicas. No entanto, isso adiciona sobrecarga ao processador, pois ele estará recompilando o plano. Também afeta negativamente a reutilização do plano, já que você não verá nenhum.
Outra é usar a dica do otimizador, OPTIMIZE FOR, UNKNOWN se uma média dos dados funcionar melhor, ou um valor se um plano mais específico funcionar. Novamente, mais testes.
Outra ferramenta é usar o Repositório de Consultas e o Plan Forcing se você estiver executando o SQL Server 2016 ou superior. Semelhante ao uso de OPTIMIZE FOR um valor, mas nenhuma alteração de código é necessária, então é uma vitória.
O SQL Server 2022, que será lançado em breve, também possui ajuste automatizado de plano sensível a parâmetros que automatiza o forçamento de plano do Query Store (é legal, muito legal, mas não resolve todos os problemas).
Por fim, você também pode usar estatísticas filtradas ou índices filtrados para criar estatísticas específicas para consultas específicas. Dá muito trabalho, mas pode resolver o problema. Novamente, testando.
Qualquer um deles pode funcionar, mas nenhum deles funcionará o tempo todo. Você terá que estar preparado para tentar uma solução diferente, dependendo da situação.
Quanto à consulta de estimativas versus reais, o problema que você encontra é que os planos em cache e o armazenamento de consultas não mantêm as métricas de tempo de execução, portanto, não há como usá-las para comparar. No entanto, se você estiver executando o SQL Server 2019, CTP 2.4 ou superior, poderá usar sys.dm_exec_query_plan_stats para ver o último plano com métricas de tempo de execução. Isso não é perfeito, pois uma execução pode corresponder às estimativas, enquanto a próxima não, mas essa é a maneira mais fácil de resolver esse problema. Caso contrário, e eu REALMENTE recomendo contra isso, você teria que capturar os planos usando Eventos Estendidos para poder executar comparações.