Olá, pessoal. No artigo desta semana abordarei um assunto muito importante para quem trabalha com bancos de dados considerados grandes: a reindexação. Apresentarei quais são as características de bancos de dados que levam à reindexação, como detectá-la, o planejamento da sua execução e a medição dos resultados.

Em geral, a análise da fragmentação de índices é uma das etapas mais importantes. Não me esqueço de verificar isso durante a análise inicial de um banco de dados, quando trabalho em alguma consultoria relacionada a problemas de desempenho. Em outras palavras, isso quer dizer que, infelizmente, encontro com mais freqüência do que gostaria diversos problemas relacionados à falta de desfragmentação dos índices das tabelas do banco de dados.

Muitos bancos de dados possuem um crescimento considerável ao longo do tempo devido a vários fatores, como o tipo de sistema utilizado, o volume de dados, as operações da empresa, integrações gerais, falta de manutenção e outros. Conforme o banco de dados vai sendo utilizado, a tendência é que ele fique cada vez mais cheio se não houver uma preocupação em “esvaziá-lo”, ou seja, uma manutenção periódica.

Um dos efeitos colaterais da utilização operacional dos bancos de dados para sistemas de ERP, CRM, Ponto de Venda e outros é a modificação da característica de ordenação dos índices. Sim, conforme os dados vão sendo inseridos, alterados ou excluídos das tabelas, há também uma modificação nas páginas de índices, independente do tipo de índice utilizado. A grosso modo, é possível imaginar que logo após a criação de um índice (com fill factor=100%) todas as suas páginas estão corretamente ordenadas e conectadas, como mostra o exemplo da Figura 1.

Figura 1. Páginas de índices recém criadas

Na Figura 1 os retângulos em azul representam as páginas do índice, independente do tipo. As setas pretas mostram como estas páginas estão conectadas de forma ordenada e a seta vermelha indica o sentido que o SQL Server irá utilizar quando for procurar dados neste índice. É claro que esta representação de páginas de índices é uma abstração, pois internamente o SQL Server utiliza árvores binárias e outros recursos para percorrer as páginas de índices. Porém para a explicação de fragmentação este exemplo é útil.

Como dito anteriormente, conforme há modificação, inserção e deleção de dados nas tabelas, o índice começa a ser fragmentando logicamente, ou seja, a páginas de dados deste índice não mais vão ser conectadas em seqüência, como mostrado na Figura 1.

Aqui vamos abrir um pequeno parêntese. Existem diversas formas de fragmentação e provavelmente cada pessoa pensa algo diferente quando escuta essa palavra. Talvez o primeiro conceito que venha à mente de muitos quando escutam a palavra fragmentação é a fragmentação física do disco rígido (HD). Apesar desta fragmentação física ser importante para a localização de dados no disco rígido, este assunto não será tratado neste artigo. Entretanto, recomenda-se utilizar alguma ferramenta especializada para desfragmentar o disco rígido do servidor de acordo com a recomendação do próprio sistema operacional, desde que o banco de dados esteja devidamente parado e que esta desfragmentação seja realizada em um horário de manutenção e sob supervisão.

Voltado para a desfragmentação lógica de páginas de índice no SQL Server, após a utilização operacional durante um período de tempo é comum que a estrutura de páginas de índice de uma tabela do SQL Server fique parecida com o desenho apresentado na figura 2.

Figura 2. Páginas de índices após uso natural da tabela

A figura 2 mostra que algumas páginas de índice não estão mais completas, devido a page splits, e outras páginas foram removidas, devido à modificação dos dados. É possível perceber também que as páginas não estão mais conectas em sequência como na figura 1. Sem entrar em detalhes, a desfragmentação deste índice pode ser útil nesta situação, pois ela fará com que as páginas sejam reconstruídas alocando os dados novamente. Além disso, a desfragmentação criará uma nova ordem de conexão entre as páginas.

O cenário acima provavelmente ocorre em bancos de dados grandes, com tamanhos acima de 50 GB. Obviamente que esta é uma afirmação genérica e cada banco de dados precisa ser analisado para indicar ou não a presença de fragmentação. Contudo, nos bancos de dados grandes geralmente encontramos o seguinte cenário: um pequeno conjunto de tabelas, entre 3 e 10, é responsável pela maior parte do banco de dados enquanto as demais tabelas ocupam pouco espaço. É a famosa regra que diz que 20% do total são responsável por 80% do resto, porém aplicada ao tamanho de algumas tabelas e ao banco de dados. Resultado: o pequeno conjunto de tabelas contém as prováveis tabelas candidatas para a reindexação.

Isso me lembra um pouco da época dos sistemas desenvolvidos em Clipper, ou tecnologia similar, com bancos de dados no padrão xBase. Apesar da nostalgia, lembro que naquela época a reindexação era uma necessidade operacional e que, infelizmente, era uma desculpa muito utilizada para justificar erros no sistema ou comportamentos bizarros. Felizmente as tecnologias de bancos de dados atuais evoluíram ao ponto onde a reindexação não mais pode ser utilizada como desculpa para certas situações ou inconsistências no sistema.

Antes de partir para a reindexação é preciso verificar se ela é realmente necessária. Aqui recomendo analisar primeiro o grupo de tabelas com um tamanho grande de dados antes de partir para a estratégia de reindexação. Para aqueles que não sabem como obter informações sobre o tamanho da tabela, recomendo a utilização da system stored procedure sp_spaceused e também uma lida na documentação da tabela de sistema chamada sysindexes, pois assim é possível identificar rapidamente quais são as tabelas que ocupam muito espaço e que provavelmente possuem problemas de fragmentação lógica.

Após descobrir quais são as tabelas que ocupam muito espaço em disco é preciso verificar a fragmentação dos seus índices. Recomendo utilizar o comando DBCC SHOWCONTIG para cada índice de cada tabela de modo a verificar a fragmentação lógica. Esse comando é tão importante que é comum encontrar nas provas oficiais de certificação da Microsoft algumas questões que cobrem o conhecimento do seu resultado. Sem entrar em detalhes específicos, a Figura 3 apresenta o resultado da execução do comando DBCC SHOWCONTIG reportando a análise do único índice da tabela chamada Tabela1.

Figura 3. Relatório apresentado pelo comando DBCC SHOWCONTIG

O valor destacado pelo retângulo vermelho da Figura 1 indica o quão fragmentado este índice está. Quando mais próximo de 0%, mais fragmentado ele estará, ou seja, valores menores indicam que a desfragmentação pode ser benéfica. Notem que a execução do comando DBCC SHOWCONTIG pode demorar de acordo com o tamanho da tabela e que devemos procurar utilizar este comando durante uma janela de manutenção do banco de dados, assim como a desfragmentação. Obviamente que existem diversos outros valores a serem analisados, porém, sem entrar em maiores detalhes, basta uma rápida olhada no resultado do comando DBCC SHOWCONTIG e já obtemos indícios sobre o a fragmentação do índice.

Aqui cabe indicar que os benefícios de fragmentação são vários: desde a redução do espaço ocupado pela tabela até a melhora no desempenho das instruções que utilizam o índice da tabela em questão. A razão destes benefícios é simples: quando menos páginas são necessárias para manipular os dados das tabelas, a tendência é que a execução das instruções seja mais rápida.

Existem várias maneiras de se desfragmentar um índice. Porém, antes de fazê-lo, é preciso tomar algumas cuidados. Em primeiro lugar deve-se sempre contar com um backup antes de realizar esta manutenção, que deve ser feita durante uma janela de manutenção devido aos impactos negativos no desempenho do banco de dados causados pelo processo de desfragmentação. Em segundo lugar é preciso monitorar e adequar o tamanho do Transacion Log, pois a desfragmentação de índices que ocupam muito espaço requer uma grande movimentação de dados que, sem sombra de dúvida, utiliza muito o Transacion Log. Em terceiro lugar, mas não menos importante, é preciso desfragmentar um índice por vez e medir o tempo e o espaço antes e depois da desfragmentação para comprovar se houve algum ganho ou não.

Seguidas estas recomendações podemos prosseguir no processo de desfragmentação. Como geralmente alguns índices são associados à chaves primárias ou estrangeiras, recomendo a utilização do comando DBCC INDEXDEFRAG, pois assim pode-se desfragmentar o índice sem afetar os objetos associados a ele. Porém esta é uma recomendação genérica que deve ser avaliada de acordo com as características de cada ambiente.

Por fim vale a pena identificar os ganhos ou perdas obtidos com a desfragmentação. Como é de praxe, recomenda-se montar um gráfico ou algum tipo de visualização de dados que auxilie a comprovação e evidencie os resultados. Por exemplo: o gráfico da Figura 4 mostra o ganho que obtive no tamanho de uma base de dados real após a desfragmentação de alguns índices.

Figura 4. Gráfico mostrando o espaço ocupado pela Base 1 antes e depois da desfragmentação.

No gráfico da figura 4 pode-se notar que o banco de dados ocupava aproximadamente 33,1 GB apenas com dados e índices antes da desfragmentação. Após a análise das tabelas e da desfragmentação dos índices de 4 tabelas o tamanho do banco de dados foi reduzido para aproximadamente 29,3 GB, o que dá um ganho de aproximadamente 3,7 GB ou aproximadamente 11,2% de redução em relação ao tamanho inicial. Este resultado de tamanho em disco pôde ser medido imediatamente após a desfragmentação, porém outros efeitos benéficos foram constatados devido à redução da quantidade de páginas de índices.

Em resumo, vale a pena analisar o banco de dados em busca das tabelas que ocupam mais espaço e procurar montar uma estratégia para organizar uma reindexação nos índices, preferencialmente de forma automática e periódica. Quem sabe esta pode ser a pedra no sapato que tanto acaba influenciando no problema de desempenho que ninguém sabe exatamente onde é.

Um grande abraço e até a próxima, pessoal.