segunda-feira, 1 de setembro de 2014

Atualizando estatísticas em paralelo - usp_update_statistics_v1

Olá senhores!

Hoje vou falar de estatísticas de tabelas, mas não de como o SQL Server utiliza ela nos planos, sobre este assunto eu sugiro se inscreverem no Webcast do meu amigo Thiago Alencar neste link. Irei compatilhar hoje uma rotina que utilizo para atualizar as estatísticas das tabelas dos bancos que administro.

Antes de iniciar, recomendo também olharem os scripts do Ola Hallengren neste link. Na página dele temos três rotinas distintas que compõem uma única solução, uma de backup, uma de verificação de consistência e uma de manutenção de índices, minha rotina se interliga com a rotina de manutenção de índices dele em certo momento, denominada IndexOptimize.

Finalmente falando sobre minha rotina, o processo de atualização de estatísticas é uma preocupação que todo DBA (acidental ou não) deve ter. Eu já vi em muitos locais serem utilizados os Maintenance Plans do SQL Server para tal finalidade, porém, eu acho esse recurso muito limitado em alguns pontos: não ter como efetuar a atualização somente de estatísticas que sofreram modificação, definir somente um fullscan ou uma porcentagem para todas as estatísticas e não poder atualizar mais de uma estatística ao mesmo tempo em paralelo.

Mesmo a rotina do Ola Hallengren, que tem algumas features a mais, ainda me limita a atualizar as estatísticas uma por vez. Em minha visão, em um servidor devidamente dimensionado para aguentar seu workload do dia a dia, efetuar a atualização de uma estatística por vez em uma janela de manutenção é desperdiçar poder computacional. Com isso em mente, desenvolvi uma rotina com a qual posso paralelizar este processo, ou seja, executar a atualização de estatística em mais de uma tabela ao mesmo tempo em paralelo.

Os scripts disponíveis foram desenvolvidos imaginando que os objetos seriam criados em um banco de dados chamado DBA, sendo assim se for criá-los em um banco de dados com outro nome, recomendo que modifique o script nestes pontos. Abaixo uma listagem dos objetos envolvidos:

  • dbo.utb_update_statistics_queue - tabela que armazena a identificação das tabelas que terão as estatísticas atualizadas.
  • dbo.utb_update_statistics_queue_sub_item - tabela que armazena a identificação das estatísticas que serão atualizadas.
  • dbo.utb_update_statistics_queue_thead_log - tabela de log que recebe os dados de como a estatística foi atualizada.
  • dbo.usp_update_statistics_enqueue - procedure que "enfileira" as estatísticas.
  • dbo.usp_restart_queue - para o caso de uma parada dos processos no meio de sua execução, esta procedure mantem os mesmo objetos na "fila" mas permite continuar a atualização do ponto no qual a execução anterior estava.
  • dbo.usp_update_statistics - procedure que efetivamente consome as estatísticas e tabelas da "fila" e realiza as atualizações.

Especificando um pouco mais o comportamento das procedures:

  • dbo.usp_update_statistics_enqueue - Esta procedure por padrão só coloca na "fila" de atualização as estatísticas que sofreram atualização, estatísticas de índices ativos e estatísticas que não estão marcadas como "NO_RECOMPUTE". Deve receber dois parâmetros:
    • @index_only - Deve ser 0 ou 1. Quando 1 este item define que devem ser "enfileiradas" somente estatísticas vinculadas a índices.
    • @verify_index_optmize - Deve ser 0 ou 1. Quando 1 a procedure irá verificar as tabelas de log da rotina IndexOptmize, toda estatística vinculada a um índice que sofreu rebuild será descartada do processo de atualização de estatísticas. Alguns detalhes importantes são: 
      • é necessário que a IndexOptimize esteja no mesmo banco que os objetos da update_statistics.
      • é necessário ativar a IndexOptimize para gerar log em tabela.
      • sera verificado somente a execução da IndexOptimize no mesmo dia em que se está executando a dbo.usp_update_statistics_enqueue.
  • dbo.usp_update_statistics - Esta procedure realiza a atualização de estatísticas seguindo duas regras: fullscan para todas as estatísticas com vínculo em índices e cálculo dinâmico de sample (update statistics sem nenhum argumento) para as estatísticas sem vínculo em índice. Ela deve receber três parâmetros:
    • @execute - Deve ser 0 ou 1. Quando 0 a procedure apenas simula a execução do processo, populando as tabelas de controle e de log, porém não efetua nenhuma atualização de estatística. Deve-se utilizar 0 para aprendizado ou para debug.
    • @order_by - Deve ser 'ASC ou 'DESC. Este item define se na execução a procedure deve pegar da "fila" a tabela com maior número de linhas ('ASC) ou com menor número de linhas ('DESC') para atualizar. Este parâmetro visa um equilíbrio para tentar otimizar os recursos no momento do paralelismo.
    • @thread_id - deve ser um SMALLINT, este item serve para popular a dbo.utb_update_statistics_queue_thead_log com o número da execução da procedure. Este parâmetro visa identificarmos se está ocorrendo a atualização das estatísticas em duplicidade pela rotina.

Tendo os objetos devidamente criados, temos que montar o ambiente para que as procedures sejam executadas em paralelo. Irei usar jobs simples do SQL Server, mas vale mencionar que pode-se usar também o Integration Services e o Powershell para tal finalizadade.

O primeiro passo é criarmos um job para que popule as tabelas de controle e orquestre esta execução. No primeiro item, podemos ter variações, como os dois exemplos abaixo:

/*Job: update_statistics_enqueue
Comando:*/

EXECUTE DBA.dbo.usp_update_statistics_enqueue(
  
@index_only = 0
  
, @verify_index_optmize = 1)

Desta forma, serão atualizadas todas as estatísticas exceto as estatísticas dos índices que sofreram rebuild pela IndexOptimize. Normalmente será usada em um job que inicie logo após a execução da IndexOptimize no servidor.

/*Job: update_statistics_enqueue
Comando:*/

EXECUTE DBA.dbo.usp_update_statistics_enqueue(
  
@index_only =1
  
, @verify_index_optmize = 0)

Desta forma serão atualizadas somente as estatísticas vinculadas a índices, sem verificar a IndexOptimize. Normalmente será usada em horários de baixa demanda do ambiente, como por exemplo, horário de almoço ou de troca de turnos.

Agora crie 4 jobs (podem ser mais ou menos dependendo do seu ambiente), vou utilizar como exemplo os seguintes nomes de job e comandos:

/*Job: update_statistics_1
Comando:*/

EXECUTE DBA.dbo.usp_update_statistics(
    
@thread_id = 1
    
, @order_by  = 'ASC'
    
, @execute = 1)

/*Job: update_statistics_2
Comando:*/

EXECUTE DBA.dbo.usp_update_statistics(
  
@thread_id = 2
  
, @order_by  = 'DESC'
  
, @execute = 1)

/*Job: update_statistics_3
Comando:*/

EXECUTE DBA.dbo.usp_update_statistics(
  
@thread_id = 3
  
, @order_by  = 'ASC'
  
, @execute = 1)

/*Job: update_statistics_4
Comando:*/

EXECUTE DBA.dbo.usp_update_statistics(
  
@thread_id = 4
  
, @order_by  = 'DESC'
  
, @execute = 1)

Através do "@thread_id" eu crio uma referencia direta entre a execução da procedure e o número do job, item que poderá ser util  ao consultarmos a tabela "dbo.utb_update_statistics_queue_thead_log" para troubleshooting. Com o jobs criados, irei utilizar uma procedure built-in do SQL Server que gosto muito, a "sp_start_job" com ela podemos iniciar jobs passando como parâmetros nomes ou identificadores, sendo assim, complemento o comando do job "update_statistics_enqueue" para o abaixo:

/*Job: update_statistics_enqueue
Comando:*/

EXECUTE DBA.dbo.usp_update_statistics_enqueue(
  
@index_only = 0
  
, @verify_index_optmize = 1);

EXECUTE msdb.dbo.sp_start_job
  
@job_name = 'update_statistics_1'; 

EXECUTE msdb.dbo.sp_start_job
  
@job_name = 'update_statistics_2';

EXECUTE msdb.dbo.sp_start_job
  
@job_name = 'update_statistics_3';

EXECUTE msdb.dbo.sp_start_job
  
@job_name = 'update_statistics_4';

E com isso teremos a mágica. O job update_statistics_enqueue irá popular as tabelas de controle e iniciará os demais jobs para que sejam executados em paralelo, devido um controle de lock nos comandos da procedure cada um dos jobs atualizará uma tabela da fila em paralelo. Em meu ambiente, um update de estatísticas com fullscan via Maintenance Plan demorava entre três e quatro horas para executar, utilizando esta rotina com seus devidos filtros e comportamentos reduzi este tempo para quinze minutos, o que me permitiu colocar atualizações de estatísticas no meio do dia em momentos de baixo consumo do servidor.

Para fazer o download do script clique aqui.

Até logo e obrigado pelos peixes!

Um comentário: