Estou procurando aqui conselhos de especialistas sobre como gerenciar estatísticas de atualização para banco de dados muito grande, aproximadamente 18 TB.
Recentemente, começamos a enfrentar problemas de desempenho e acreditamos que isso se deve a estatísticas antigas.
Na verdade, temos um trabalho que executa exec sp_update stats e atualiza com taxa de amostragem padrão, no nosso caso 1,2%. Portanto, temos que atualizar manualmente as estatísticas e ver algumas melhorias.
Tenho certeza que agendar FULL SCAN seria um desafio. De acordo com meu conhecimento, estou comparando linhas com linhas amostradas. Por exemplo, em uma tabela com tamanho de 400 GB e mais de 100 milhões de linhas, posso ver que as linhas de amostra são cerca de 2 a 4 milhões. As grandes mesas são particionadas.
Estamos no SQL Server 2012 Enterprise Edition. O sinalizador de rastreamento 2371 não está habilitado.
Por favor, sugira como posso usar a atualização de estatísticas de uma maneira muito melhor para um banco de dados tão grande e como jogar com essa taxa de amostragem?
Com base na sua pergunta, posso pensar em quatro possíveis problemas com estatísticas que podem estar causando os problemas que você está enfrentando.
1. As estatísticas não são atualizadas automaticamente com frequência suficiente.
No SQL Server 2012, as estatísticas são atualizadas somente após 20% ou mais das linhas da tabela serem alteradas. Isso significa que, para uma tabela de bilhões de linhas, você precisará modificar 200 milhões de linhas antes que ocorra uma atualização de estatísticas. À medida que a tabela cresce, suas atualizações de estatísticas se tornam menos frequentes, portanto, o SQL Server pode passar anos sem atualizar estatísticas em tabelas grandes.
O TF 2371 altera o limite para que as atualizações de estatísticas ocorram com mais frequência. No SQL Server 2016, essa alteração se tornou meu padrão.
2. As consultas em sua carga de trabalho são vulneráveis ao problema de chave ascendente .
Considere uma tabela que tenha novos dados carregados diariamente e consultas que filtrem no último dia de dados. A menos que as estatísticas sejam atualizadas imediatamente após o carregamento dos dados, os novos dados não estarão presentes em nenhum dos histogramas de estatísticas. Isso pode resultar em um desempenho de consulta muito ruim devido a estimativas de baixa cardinalidade.
O novo CE no SQL Server 2014 traz melhorias nessa área. Se você solicitar dados fora do intervalo do histograma, poderá fazer uma estimativa mais otimista e assumir que há dados na tabela, mas não no histograma. No SQL Server 2012, você pode resolver esse problema, se o tiver, atualizando as estatísticas com mais frequência ou habilitando o TF 4139 . O TF 4139 só funciona em colunas com um índice nelas. O SQL Server pode executar uma consulta muito rápida no índice para obter o valor mais alto ou mais baixo e alterará temporariamente o histograma do objeto de estatísticas relevante. Isso pode resultar em planos muito melhores para algumas consultas.
3. Suas consultas aguardam atualizações de estatísticas.
Por padrão, se uma consulta carregar uma atualização de estatísticas obsoleta, ela atualizará esse objeto de estatística antes de criar um plano de consulta. No SQL Server 2012, a atualização de estatísticas de amostra será executada com o
MAXDOP 1. Se iniciado em uma tabela grande, o processo pode atingir o tempo limite enquanto aguarda a conclusão da atualização das estatísticas. Depois de atualizar as estatísticas em relação às tabelas, a consulta funciona melhor porque não precisa mais esperar pela atualização das estatísticas.Se você estiver enfrentando esse problema, isso pode ser resolvido com uma manutenção de estatísticas mais proativa com a
NORECOMPUTEopção. Como alternativa, você pode tentar agilizar a atualização das estatísticas atualizando para o SQL Server 2016. No SQL Server 2016, as atualizações de estatísticas de amostra podem ser executadas em paralelo.Outra opção é ativar a
AUTO_UPDATE_STATISTICS_ASYNCopção. Se um plano de consulta encontrar um objeto de estatística obsoleto, ele enfileirará esse objeto de estatística para ser atualizado por um trabalho em segundo plano. Isso pode parecer ruim e é. A consulta pode ser executada com estatísticas obsoletas. Esse é o tipo de recurso que você deseja ativar quando não tem uma escolha melhor, como ao trabalhar com sistemas grandes em que as atualizações de estatísticas automáticas são muito caras ou simplesmente não ajudam o suficiente na forma do plano. Jack Li blogou sobre um cliente que foi ajudado com esta opção aqui .4. Sua carga de trabalho se beneficiaria de atualizações manuais de estatísticas com uma taxa de amostragem mais alta do que a taxa de amostragem automática.
Algumas consultas e cargas de trabalho precisam de mais do que a taxa de amostragem padrão de estatísticas para obter um desempenho aceitável. Isso pode ser difícil de fazer em um banco de dados grande, mas há alguns truques e alguns aprimoramentos em versões posteriores do SQL Server que ajudarão.
Se você conhece seus dados e carga de trabalho muito bem, pode desativar as atualizações automáticas de estatísticas. Você pode coletar as estatísticas necessárias
FULLSCANe atualizá-las quando apropriado. Essa abordagem exigirá muito trabalho e muita atenção ao servidor.Se você tiver um processo de manutenção existente que reconstrói índices (a sabedoria disso é debatida ), observe que a reconstrução de índices atualiza automaticamente as estatísticas com
FULLSCAN, portanto, talvez você possa aproveitar isso se criar uma solução de manutenção para atualizar estatísticas.Observe que a coleta de estatísticas de amostra pode não ser mais rápida do que as estatísticas de varredura completa, especialmente se a coluna do histograma estiver indexada. O SQL Server pode fazer atualizações de estatísticas de varredura completa em paralelo. Ele também pode evitar uma classificação ao fazer uma varredura completa de uma coluna indexada, mas não evitará a classificação ao fazer a amostragem da coluna. Na verdade, para tabelas grandes o suficiente, as atualizações de estatísticas em colunas não indexadas podem falhar se preencherem tempdb.
O SQL Server 2014 introduziu estatísticas incrementais . Suponha que você tenha uma tabela particionada e muitos dados sejam modificados em apenas uma partição. Anteriormente, para atualizar as estatísticas na tabela, você teria que examinar todas as partições. Com este novo recurso é possível apenas reunir novas estatísticas sobre a partição alterada. O SQL Server é capaz de acumular as estatísticas das partições em um objeto de nível de tabela.
Se você não conseguir atualizar, considere converter algumas tabelas em exibições particionadas . Cada tabela dentro da visualização terá seus próprios objetos de estatísticas, portanto, se você carregar dados de acordo com a data, talvez seja necessário atualizar as estatísticas apenas na tabela mais recente da visualização, em vez de todas as tabelas da visualização.
Por fim, conforme mencionado anteriormente, o SQL Server 2016 pode atualizar estatísticas de amostra em paralelo :