Administração de índices (Parte 2).

Depois de vários dias sem postar, finalmente consegui um tempinho para escrever a segunda parte da série de posts que falarão sobre a sua rotina de desfragmentação dos índices.

Como foi falado no Post anterior, muitos DBA’s usam o seguinte script para realizar a desfragmentação de seus índices: http://technet.microsoft.com/en-us/library/bb838727(v=office.12).aspx

Eu também já usei, mas hoje no ambiente em que trabalho esse script não serve mais e listo aqui o porquê que essa rotina já não é tão eficiente para mim.

  1. O ambiente não há uma janela muito flexível para esse tipo de manutenção;
  2. O ambiente é muito grande, pois temos várias instâncias e em cada instância temos no mínimo 50 bases de dados;
  3. Apesar de existirem tabelas que fragmentam mais de 15% diariamente, nem todas podem entrar na rotina, pois são muito grande (20GB cada uma) e se elas fizerem parte dessa rotina automática, podem causar um estouro no disco (justamente por não ter um controle de quais tabelas serão fragmentadas no dia);
  4. Essa rotina não faz a validação do tamanho da tabela. Apesar de a tabela estar fragmentada, se ela for pequena o SQL Server não realiza o REBUILD da mesma.

Pelo que eu me lembre, eu trabalhava em um ambiente em que a rotina de desfragmentação dos índices era esse script. Na época a base de dados tinha em torno de 200GB e essa rotina executava diariamente. O tempo de duração em média era 2h podendo demorar 1h e tinha dias de demorava até 4h, porém tinha dias também que essa rotina não executava, pois ocorria um DeadLock.  Naquele ambiente, apesar de demorar muito e ter essa concorrência no banco, essa rotina não tinha nenhum efeito colateral, pois a janela era realmente grande e não causava impacto para o usuário final.

Atualmente temos que reindexar algumas tabelas relativamente grandes, porém a minha janela diária de manutenção é de no máximo 30min, se passar disso já tem um impacto para o usuário final podem causar até um prejuízo financeiro para instituição. Nesse caso qual é a minha solução? Atualmente faço o Rebuild da forma “simples” com o seguinte comando:

 ALTER INDEX ALL ON Tabela REBUILD WITH (ONLINE=ON) 

Sim! É um trabalho bastante manual, pois tenho que saber direitinho quais são as tabelas que necessitam ser desfragmentadas, saber a frequência de fragmentação das mesmas, assim como o seu tamanho e dividir a rotina fazendo com que ela não estoure o nosso disco de log (isso porque tenho bastante espaço disponível). Lembrando que na minha rotina deve ter no máximo umas 30 tabelas (sendo que cada banco eu tenho em volta de 1700 tabelas). Essas 30 tabelas não são desfragmentadas tudo de uma única vez, temos rotinas que rodam diariamente, outras 2 vezes na semana e outra no Domingo.

Então, levando em consideração que temos 50 bases por instâncias, 1700 tabelas por base e somente 30min de janela por dia para realizar o Rebuild, concluímos que é muito arriscado colocar uma rotina automática para realizar a desfragmentação, pois não temos controle de quanto de log será gerado no momento da rotina…

Quero que pensem bem, sei que muitos ao realizarem essa rotina, não se preocupam com o log que será gerado nem com o espaço em disco utilizado por esse arquivo… Pensem um pouco sobre o quanto o Log pode sofrer com essas rotinas, lembrando que em um banco de dados, temos várias funcionalidades que utilizam o Log, alguém se habilita a dizer qual dessas funcionalidades “sofre” bastante com essa rotina de Rebuild? No próximo post (daqui um mês quem sabe rsrs) eu falo mais. Falarei também qual solução achamos para que os índices não fragmentem tanto, diminuindo assim, a frequência da rotina de desfragmentação.

Anúncios

Query Tuning: Set-based Thinking

Quando começamos a trabalhar com o processo de optimização de consultas, ou Query Tuning, logo descobrimos que existem várias “camadas” a serem estudadas. Cada um tem sua própria metodologia de tuning e não pretendo descrever uma hoje. No entanto, em sua maioria, o processo envolve a análise dos Waits a nível de instância, relacionar os principais Waits com os gargalos para definir os recursos sob pressão, encontrar o processo responsável e então, analisá-lo para optar por uma indexação adequada, uma revisão do código ou ambos. Neste artigo irei abordar apenas a revisão do código da consulta, me baseando numa solução orientada a conjuntos (set-based solution).

“Mas o que é uma solução orientada a conjuntos?” Esta é uma solução que realiza as operações necessárias (consultas, modificações de dados, etc) tratando o conjunto de dados como um todo, ao invés de processar os dados através de uma lógica iterativa (linha a linha). Se por “lógica iterativa” você entendeu cursores e loops, você está certo.

“Qual o problema com os cursores?” Basicamente, cursores vão contra a ideia do modelo relacional. O modelo relacional trabalha com conjuntos ao invés de linhas e registros. E o SQL Server é muito eficiente ao lidar com conjuntos. Já os cursores no SQL Server têm um overhead por iteração. Então, se o custo de varrer N linhas com uma consulta for dado por C, é possível expressar o custo da mesma consulta feita através de um cursor por C + N*O, onde O é o overhead por iteração. Existe uma forma prática de se medir o overhead de seu servidor que pode ser encontrado no capítulo 08 do livro Inside Microsoft SQL Server 2008: T-SQL Programming do Itzik Ben-Gan, mas deixo isso a título da curiosidade do leitor.

É claro que existem situações em que os cursores serão a solução mais performática, mas esses casos são tão poucos que sugiro que os deixem como última opção. Confesso que quando comecei a trabalhar com banco de dados tinha o hábito de usar cursores, hábito adquirido com o tempo que trabalhei como programador. Afinal de contas, é muito fácil para um programador lidar com ferramentas conhecidas como os cursores. Mas insisto: aprender a moldar suas soluções a uma lógica orientada a conjuntos é dar passos largos em direção à eficiência e à escalabilidade. É tempo muito bem investido.

Deixando a conversa de lado, vamos a um caso real que enfrentei recentemente onde trabalho. A situação é a seguinte: Após a conversão para a última versão de uma ferramenta de terceiros, o usuário responsável por um processo de importação de dados do fechamento contábil anual informou que o processo que antes rodava em até 20 minutos, agora estava levando o dia todo. Assim, a equipe de banco de dados foi acionada.

Depois de encontrar a stored procedure responsável pelo desespero do usuário, repetimos o processo na base de teste para identificar onde doía mais. Assim que iniciamos o processo, utilizei a sp_whoisactive do Adam Machanic para acompanhar a execução. Eis que percebo uma demora num UPDATE. Abro o comando (adaptado, para não me dar dores de cabeça ou processos futuros =) que está sendo executado no momento e vejo:

UPDATE Table1
SET NR_LINHA = @LINHA_INICIAL
WHERE ID       = @ID
AND ID_USUARIO = @ID_USUARIO
AND EXISTS( SELECT 1 FROM #Table2 R (NOLOCK)
             WHERE R.ID_LINHA   = @LINHA_INICIAL
               AND R.ID         = Table1.ID
               AND R.CONTA      = Table1.CONTA
               AND R.CUSTO      = Table1.CUSTO
               AND R.SALDO      = Table1.SALDO
               AND R.PERIODO    = Table1.PERIODO
               AND R.ID_USUARIO = Table1.ID_USUARIO)

Curioso com a utilização de uma tabela temporária para fazer o UPDATE, abro o plano de execução para ver o custo deste comando.

PlanoExecucao_UpdateCost

Para minha surpresa, o custo desta operação era muito baixo (0,1456960), mas executando novamente a sp_whoisactive, vi que o mesmo UPDATE ainda estava sendo executado, enquanto o número de leituras e tempo de CPU apenas aumentavam. Então, abri o código da stored procedure e busquei o trecho de código onde estava localizado este UPDATE. Segue o trecho com o verdadeiro problema.

SELECT IDENTITY(INT,1,1) AS ID_LINHA,* INTO #Table2
FROM Table1 (NOLOCK)
WHERE ID       = @ID
AND ID_USUARIO = @ID_USUARIO;
--
SELECT @LINHA_INICIAL = MIN(ID_LINHA)
      ,@LINHA_FINAL   = MAX(ID_LINHA)
FROM #ROWID (NOLOCK);
--
WHILE @LINHA_INICIAL <= @LINHA_FINAL
BEGIN
--
UPDATE Table1
SET NR_LINHA = @LINHA_INICIAL
WHERE ID       = @ID
AND ID_USUARIO = @ID_USUARIO
AND EXISTS( SELECT 1 FROM #Table2 R (NOLOCK)
             WHERE R.ID_LINHA   = @LINHA_INICIAL
               AND R.ID         = Table1.ID
               AND R.CONTA      = Table1.CONTA
               AND R.CUSTO      = Table1.CUSTO
               AND R.SALDO      = Table1.SALDO
               AND R.PERIODO    = Table1.PERIODO
               AND R.ID_USUARIO = Table1.ID_USUARIO)
--
SET @LINHA_INICIAL = @LINHA_INICIAL + 1
--
END

Tentei por um bom tempo entender o que o desenvolvedor deste trecho quis fazer, mas acho que até agora não tenho certeza. No entanto, o que este trecho faz é gerar uma tabela temporária (#Table2) com registros enumerados com base num subconjunto da Table1 e depois entra num loop e atualiza cada registro da Table1 que tenha um correspondente na #Table2, uma linha de cada vez. Adivinhe quantos registros eram carregados na #Table2 para o menor período de fechamento contábil? Nada menos que 92 MIL registros. Então, lembrando-se da formula para calcular o custo de um cursor, C + N*O, você já imagina a monstruosidade que custava esta parte do processo.

A solução foi mudar a lógica de iterativa para orientada a conjuntos. Procurando manter as regras de negócio deste trecho, depois de alguns minutos escrevi o trecho de código abaixo e substituí pelo trecho acima.

;WITH Table2 AS
(
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS ID_LINHA,*
FROM Table1
WHERE ID       = @ID
AND ID_USUARIO = @ID_USUARIO
)
UPDATE Table1
SET NR_LINHA = R.ID_LINHA
FROM Table2 R
WHERE R.ID       = Table1.ID
AND R.CONTA      = Table1.CONTA
AND R.CUSTO      = Table1.CUSTO
AND R.SALDO      = Table1.SALDO
AND R.PERIODO    = Table1.PERIODO
AND R.ID_USUARIO = Table1.ID_USUARIO;

A função da tabela temporária foi substituída por uma CTE e a lógica de atualização da Table1 foi mantida. E você acha que o plano de execução ficou caro? Olha só o resultado:

PlanoExecucao_UpdateSetBasedCost

O custo que era de 0,1456960 por iteração, foi para 0,6156450 para todo o conjunto. Qual o resultado final após a alteração? O processo que demorava o dia todo (em média 6 horas) foi executado em menos de 1 minuto.

Após a validação da equipe de desenvolvimento da ferramenta (pois é… ainda tivemos que esperar a validação para não perdermos o suporte deles) a alteração foi implantada em produção e o usuário teve um final feliz. Mais uma vez, a solução orientada a conjuntos salva o dia! E você, tem algum caso em que o tuning salvou o dia?