INDEXDEFRAG VS. DBREINDEX

No webcast da semana passada eu aproveitei o tempo para explorar as estruturas internas de tabelas e índices do SQL Server, onde utilizei o comando DBCC PAGE para mostrar o que temos fisicamente nos arquivos de dados e ainda aproveitei para navegar pelos índices (não-cluster e cluster), também usando o DBCC PAGE. A idéia era mostrar efetivamente como o SQL Server trabalha, sem ficar explicando aquele PPT básico sobre índices que todo mundo já está cansado de ver. Espero que a quantidade de informações e aqueles hexadecimais voando na tela não tenham sido muito cansativos... Em me diverti até! J

Para quem perdeu e quer ver a gravação, o link para o webcast é: https://msevents.microsoft.com/CUI/EventDetail.aspx?EventID=1032357386&culture=pt-BR&Action=Preview

Depois do webcast eu recebi um e-mail com alguns questionamentos. Já que a resposta não ficou nada pequena, mas interessante (assim espero), resolvi compartilhar com todos...

Perguntas:

1) Qual a principal diferença entre DBCC INDEXDEFRAG e DBCC DBREINDEX e qual é o mais adequado para se utilizar no SQL Server 2005? Ou melhor, quando é mais adequado utilizar um ou o outro.

2) O quanto o parâmetro WITH FAST pode prejudicar sua execução?

3) Para tabelas com mais de 10.000.000 de registros, qual é o melhor método para se utilizar? Questiono, pois já tive sérios problemas de perfomance numa empresa no qual eu trabalhei; onde acabei adotando soluções auxiliares (como manter uma tabela menos populada com dados mais frequentemente acessados) e eu sempre pensava em reorganizar os índices mais eu sempre tinha o problema de indisponibilidade e perfomance; que eu acabava remediando com alguma outra saída mas nunca encontrei a forma excelente.

Respostas:

Questão 1:

O DBCC INDEXDEFRAG é utilizado para remover a fragmentação lógica das páginas e é uma operação online. O SQL Server considera uma fragmentação lógica quando o número (Page ID) da próxima página é um valor menor que o da página atual. Por exemplo:

Supondo que a página 400 faça parte do nível folha de um índice cluster (no cabeçalho da página temos a informação de nextpage e prevpage), se nextpage for 401, 450 ou até 1000, não existe fragmentação lógica. Agora, se nextpage for 350, 100 ou 234, então next é menor que current e temos uma fragmentação lógica.

Quando o DBCC INDEXDEFRAG é executado, o SQL Server reorganiza as páginas (sem alocar novas páginas ou apagar existentes) para remover a fragmentação lógica. Exemplifico:

Suponha o seguinte encadeamento usando a estrutura: (prevpage) DadoDaPagina – PageID (nextpage)

                (100) B - 50 (300) | (null) A - 100 (50) | (300) D – 101 (null) | (50) C – 300 (101)

Aqui temos a página A na posição 100, a B na posição 50, a C na posição 300 e a D na posição 100. A fragmentação lógica existe porque B está em uma página com numeração menor que A, idem D para C.

Quando o DBCC INDEXDEFRAG é executado, o SQL Server vai promover uma troca das páginas, resultando:

                (null) A - 50 (100) | (50) B - 100 (101) | (100) C – 101 (300) | (101) D – 300 (null)

Aqui vemos que o SQL Server não alocou novas páginas, para colocar A, B, C e D nas páginas 100, 101, 102 e 103, por exemplo. Então a fragmentação física se manteve...

!!! DBCC DBREINDEX to the rescue !!!

Se você quer o SQL Server faça uma reorganização física dos dados (além da lógica), alocando novas páginas, movendo dados e removendo outras, você utiliza o DBCC DBREINDEX, que vai literalmente apagar e recriar a estrutura física do seu índice (semelhante a DROP/CREATE ou CREATE INDEX WITH DROP_EXISTING). Recomenda-se a utilização do comando CREATE INDEX ao invés do DBCC DBREINDEX, pois este pode ser removido em futuras versões do SQL Server.

No SQL Server 2000 essa abordagem deixa o índice offline enquanto ele está sendo reconstruído, além disso você tem que levar em conta o espaço extra que é utilizado para execução do procedimento, já que tudo acontece dentro de uma transação.

O SQL Server 2005 oferece a possibilidade de recriar os índices online (CREATE INDEX... ONLINE = ON), porém essa abordagem exige espaço adicional no banco de dados em questão ou na tempdb (SORT_IN_TEMPDB).

Para mais informações sobre o assunto, veja as referências:

Microsoft SQL Server 2000 Index Defragmentation Best Practices (https://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx)

SQL Server 2005 Online Index Operations (https://www.microsoft.com/technet/prodtechnol/sql/2005/onlineindex.mspx)

Questão 2:

Assumindo que o WITH FAST que você citou é um parâmetro do DBCC SHOW_CONTIG...

O FAST faz com que o SQL Server não leia as páginas no nível folha do índice. Como no nível anterior (intermediário N) do índice haverá um ponteiro para cada página existente no nível folha, o SQL Server consegue mostrar se existe fragmentação lógica, já que ele possui as informações da seqüência das páginas e os Page ids. Porém informações como Page Density e Avg. Bytes Free per Page não podem ser informados, pois o nível folha que contém essa informação não é analisado.

Então se você estiver quiser apenas verificar como está a fragmentação lógica antes de executar um DBCC INDEXDEFRAG, e não quiser esperar por uma análise completa de todas as páginas do seu índice (já que não vai utilizá-la), você usa um DBCC SHOWCONTIG com a opção WITH FAST.

Questão 3:

Como eu citei acima, o DBCC DBREINDEX pode trazer indisponibilidade da tabela toda (índice cluster) ou do índice não-cluster em questão. No SQL Server 2005 você pode fazer a reindexação online (cuidado com o overhead dessa opção).

Não existe regra de quando você deve usar o INDEXDEFRAG ou o REINDEX, depende do seu caso: em linhas gerais... Se a tabela não sofre muita fragmentação diária, você pode tentar manter diariamente um INDEXDEFRAG (que já ajuda bastante nas operações de read-ahead que são importantes para o SQL Server) e no fim de semana (período de pouco/nenhum uso) você faz um DBREINDEX.

Se a tabela é muito grande, você pode optar por particioná-la, o que pode trazer bons ganhos de performance durante a execução de consultas que somente utilizam algumas partições. Se você fizer isso, veja os cuidados com alinhamento dos índices: Special Guidelines for partitioned indexes (https://msdn2.microsoft.com/en-us/library/ms187526.aspx).

Por fim, sempre vale lembrar que a fragmentação em tabelas pequenas não traz grande impacto de desempenho para o banco de dados. Se preocupe com as tabelas grandes!

Gostou? Não entendeu? Discorda? Fique a vontade para estender essa discussão.

[]s

Luciano Caixeta Moreira

luciano.moreira@microsoft.com

=============================================================

This posting is provided "AS IS" with no warranties, and confers no rights

=============================================================

Comments

• Anonymous
  November 06, 2007
  PingBack from http://msdnrss.thecoderblogs.com/2007/11/06/

• Anonymous
  May 30, 2008
  Adorei o artigo, Foi o primeiro que encontrei que realmente explica a diferença entre DBREINDEX e INDEXDEFRAG. Excelente !!! Parabéns !!!

• Anonymous
  October 28, 2008
  E aí, vamos instalar o Oracle?

• Anonymous
  October 28, 2008
  Realmente... Cheguei nesse post com a dúvida do reindex, mas como tenho um projeto novo, vou testar o Oracle.

• Anonymous
  October 29, 2008
  É claro que vocês podem usar o Oracle, como qualquer outro banco de dados. Todos eles terão um overhead quando falamos de desfragmentação. Meu conselho é que seja evitado ao máximo qualquer tipo de fragmentação, através de uma boa modelagem e existam procedimentos de manutenção corretos. Agora, se não souber direitinho como o produto funciona, pode instalar qualquer um deles, pois vai ter dor de cabeça de qualquer maneira... :-) []s Luti

• Anonymous
  November 16, 2008
  Abstraindo a questão locar ou não a tabela, na prática, eles fazem a mesma coisa?