Eu tenho uma tabela que contém 22 milhões de registros.
Percebi que as estatísticas de uma coluna estavam muito erradas, mesmo que essa coluna tenha uma distribuição constante: na verdade, cada valor é repetido duas vezes.
Para ajudá-lo a visualizar esse cenário, pense em uma tabela com datas de check-in e check-out (2 registros distintos) para cada id exclusivo de outra tabela.
Estas são as estatísticas com uma verificação completa:
Estas são as estatísticas com amostragem automática:
Por que esse comportamento estranho? Existe alguma regra prática para determinar uma taxa de amostragem correta ou o fullscan é necessário? Devo criar um trabalho de atualização de estatísticas com fullscan para algumas estatísticas? E se for o caso, como saber quais são as estatísticas que precisam desse tipo de tratamento?
Informações adicionais:
- Não há restrição de chave estrangeira nessa coluna
- A coluna é uma
numeric(14,0) NULL
Suponha que alguém lhe dê um livro de um milhão de números inteiros e lhe dissesse para fazer um palpite sobre a distribuição geral com base em dez números amostrados. Os dez números amostrados que você recebe são 1, 1, 125.000, 125.000, 250.000, 250.000, 375.000, 375.000, 500.000 e 500.000. Uma suposição perfeitamente razoável é que o livro contém 500.000 inteiros únicos em pares que variam de 1 a 500.000. Uma suposição igualmente válida é que o livro contém cinco inteiros únicos com cada inteiro presente 200.000 vezes. Para sua distribuição de dados, o SQL Server está fazendo uma inferência sobre seus dados com base na amostra que está mais próxima da última interpretação. Os algoritmos para construir histogramas não funcionam bem para todas as distribuições de dados amostrados possíveis, incluindo uma com cada inteiro único aparecendo exatamente duas vezes.
A única regra prática que conheço é que a taxa de amostragem correta é pelo menos igual à taxa de amostragem que fornece a consulta ou desempenho do sistema aceitável. Descobri que os DBAs usam uma taxa de amostragem de 100% para evitar essa análise, e é um atalho perfeitamente razoável para tabelas que não são enormes.
Você pode substituir sua tabela por uma exibição atualizável criada em duas tabelas subjacentes. Cada tabela subjacente pode conter metade dos dados. O SQL Server criará objetos de estatísticas separados para cada tabela e você poderá obter melhor desempenho sem precisar ajustar as estatísticas. Não posso dizer se esta é uma boa abordagem para o seu cenário específico.
Essa é uma solução comum para o problema que você está descrevendo. Para outra opção, o SQL Server 2016 SP1 CU4 apresenta a palavra-chave PERSIST_SAMPLE_PERCENT para
STATISTICScomandos. Você pode usá-lo para instruir o SQL Server a sempre amostrar estatísticas em uma determinada taxa para esta coluna sempre que uma atualização de estatísticas automáticas for acionada. Se as atualizações de estatísticas assíncronas estiverem desativadas, lembre-se de que os tempos de compilação de consulta podem ocasionalmente ser longos para consultas que usam sua coluna.A resposta usual é monitorar sua carga de trabalho quanto a um desempenho de consulta inaceitável, determinar quais consultas têm problemas de estatísticas e corrigir esses problemas com alterações de estatísticas. Se você deseja ser proativo, considere analisar estatísticas em uma cópia da produção. Primeiro, obtenha estatísticas de amostra para todas as colunas relevantes no banco de dados e salve todos os vetores de densidade em uma tabela. Você pode usar
DBCC SHOW_STATISTICS WITH DENSITY_VECTORpara fazer isso. Em seguida, atualize todas as mesmas estatísticasFULLSCANe salve esses vetores de densidade também. Você pode então comparar os vetores de densidade para encontrar os objetos estatísticos com as maiores diferenças. Isso não encontrará todos os problemas de estatísticas, mas encontrará aqueles semelhantes ao que você observou nesta pergunta.A execução de uma atualização de verificação completa das estatísticas de uma coluna permite que o SQL Server entenda a natureza exata da coluna, enquanto a verificação padrão examina uma amostra "estatisticamente significativa" de linhas da tabela. Em sua tabela, a amostra de linhas "estatisticamente significativa" resulta em uma imagem incompleta dos dados.
Para tabelas com distribuições de valores atípicas, as atualizações completas das estatísticas de varredura quase sempre resultarão em um histograma muito mais preciso. Não existe uma "melhor" escolha geral; a taxa de amostragem padrão é um equilíbrio útil entre o desempenho de atualização de estatísticas e o desempenho de consulta para a maioria das tabelas.
Compreender os dados contidos em uma coluna permitirá determinar se uma verificação completa resulta em estatísticas melhores.
Como regra geral, sempre configuro um trabalho de nova verificação de estatísticas que é executado todas as noites ou semanalmente e, salvo algum requisito para minimizar a E/S, configuro esse trabalho para fazer atualizações FULLSCAN dessas estatísticas. O trabalho que crio atualiza apenas as tabelas que receberam um número "x" de atualizações/inserções/exclusões e que não tiveram uma atualização de estatísticas em um período de tempo "x". A execução de um trabalho em uma agenda durante períodos de atividade mais baixa, na maioria das vezes, impedirá que o SQL Server execute atualizações de estatísticas durante períodos de alta atividade.
Escrevi uma postagem no blog mostrando como atualizo estatísticas por meio de um proc armazenado em
mastere um trabalho do SQL Server Agent: http://www.sqlserver.science/maintenance/statistics-update-job/