Início / dba / Perguntas / 187485
Accepted
Philip
Asked: 2017-10-03 15:19:46 +0800 CST

Como gerenciar melhor as compilações de índice com atualizações de estatísticas?

  • 772

Esta é uma continuação do que li em Existe algum motivo para interromper os backups do log de transações durante uma janela de manutenção? na resposta proposta por sp_BlitzErik.

Eu uso o script de indexação de Ola Hallengren e tenho as configurações especificadas conforme abaixo. Eu corro isso uma vez por semana através de um trabalho de agente.

@Databases nvarchar(max),
@FragmentationLow nvarchar(max) = NULL,
@FragmentationMedium nvarchar(max) = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationHigh nvarchar(max) = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationLevel1 int = 5,
@FragmentationLevel2 int = 30,
@PageCountLevel int = 500,

Eu sei que, além de uma reconstrução de índice, uma atualização de estatísticas é feita automaticamente, mas tenho as seguintes propriedades de banco de dados definidas em todos os meus bancos de dados que acredito que devem cuidar da atualização das estatísticas:

Auto Create Statistics = True
Auto Update Statistics = True
Auto Update Statistics Asynchronously = True

O que geralmente é a melhor prática aqui para essas configurações e atualizar as estatísticas regularmente? Você deve atualizar as estatísticas todas as noites? Não tenho certeza de como medir se as estatísticas devem ser atualizadas ou não, e é por isso que tenho essas propriedades do banco de dados definidas.

Eu vejo na resposta de sp_BlitzErik que ele menciona " Você ainda vai querer atualizar as estatísticas regularmente, e você pode fazer isso com este comando:"

com o seguinte comando, mas atualizar estatísticas regularmente é muito geral.

EXECUTE dbo.IndexOptimize @Databases = 'USER_DATABASES',
                          @FragmentationLow = NULL,
                          @FragmentationMedium = NULL,
                          @FragmentationHigh = NULL,
                          @UpdateStatistics = 'ALL',
                          @OnlyModifiedStatistics = 'Y',
                          @StatisticsSample = NULL,
                          @LogToTable = 'Y';
sql-server statistics

1 respostas

  1. Best Answer

    Eu uso o script de indexação de Ola Hallengren e tenho as configurações especificadas conforme abaixo. Eu corro isso uma vez por semana através de um trabalho de agente.

    Vamos detalhar o comando que você está executando:

    @FragmentationLevel1 int = 5,
@FragmentationLevel2 int = 30,
@PageCountLevel int = 500,

    Mesmo uma vez por semana, isso é absurdamente agressivo. Eu sei que 5/30% vem de conselhos antigos da Microsoft, mas é apenas isso --antigo. É hora de seguir em frente.

    Você está gastando tempo para desfragmentar índices com 500 páginas de 8 KB. Essa é uma tabela de 4 MB. Se o seu hardware tiver problemas para ler 4 MB na memória ou manter uma tabela de 4 MB na memória, a resposta não é a manutenção do índice. É por isso que eu aumento isso para pelo menos 5000.

    Hardwares modernos, como SANs, SSDs e servidores não de 32 bits, que podem armazenar quantidades de RAM > 4 GB, simplesmente não têm os mesmos problemas de acesso a dados que as unidades de disco giratório do início dos anos 2000.

    Estamos falando de toca-discos versus CDs.

    As recompilações de índice devem ocorrer com pouca frequência e apenas para corrigir um problema com um índice ou alterar uma configuração. Aqui está a coisa: 30% de fragmentação não é um problema real. É apenas algo que os DBAs fazem porque foram instruídos, e é algo que eles podem medir.

    Então, aqui está minha pergunta para você: quanto tempo e recursos seu servidor gasta na manutenção do índice e quanto isso reduz o tempo e os recursos consumidos pelas consultas?

    Se você puder medir isso, poderá reconstruir e reorganizar o quanto quiser.

    ... mas tenho as seguintes propriedades de banco de dados definidas em todos os meus bancos de dados que acredito que devem cuidar da atualização das estatísticas ...

    Tipo meio. As estatísticas de atualização automática (mesmo assíncronas) acontecem quando 20% dos dados da tabela + 500 linhas (supondo que a tabela tenha > 500 linhas) são alterados. Se você tiver uma tabela com um milhão de linhas, são 200 mil linhas. Você pode diminuir dinamicamente esse limite usando o sinalizador de rastreamento 2371, mas as estatísticas de atualização automática usam o algoritmo de amostragem padrão, que pode não ser suficiente para tabelas com dados muito distorcidos.

    mas atualizar estatísticas regularmente é muito geral.

    Bem, sim, estou respondendo perguntas sobre um servidor que nunca vi. Prefiro atualizações de estatísticas noturnas, mas já vi servidores que precisavam delas com mais regularidade do que isso.

    Então o que você deveria fazer?

    1. Comece discando de volta a manutenção do índice para os comandos que postei na pergunta que você está referenciando

      Se você tiver problemas, diminua os limites de fragmentação até que eles parem. Se ninguém disser nada, execute a manutenção do índice com menos frequência, até que talvez, apenas talvez, você nunca a execute.

    2. Atualizar estatísticas todas as noites

      Comece com o limite padrão. Se você achar que a atualização completa das estatísticas que ocorre com uma reconstrução é necessária, use a CommandLogtabela para descobrir quais tabelas e índices estavam sendo reconstruídos regularmente e comece a se concentrar neles. Eles normalmente serão índices para tabelas "grandes", com dezenas de milhões de linhas.

    Isso é o mais específico que posso obter sobre um servidor que nunca vi. Você vai ter que fazer a experimentação daqui.

    Veja também meu post Por que a maioria de vocês deve deixar as estatísticas de atualização automática ativadas

    • 3

relate perguntas