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 é: http://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 (http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx)


SQL Server 2005 Online Index Operations (http://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 (http://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 (6)

  1. Denise Nunes says:

    Adorei o artigo,

    Foi o primeiro que encontrei que realmente explica a diferença entre DBREINDEX e INDEXDEFRAG.

    Excelente !!! Parabéns !!!

  2. Pancho says:

    E aí, vamos instalar o Oracle?

  3. Leandro says:

    Realmente… Cheguei nesse post com a dúvida do reindex, mas como tenho um projeto novo, vou testar o Oracle.

  4. Luti says:

    É 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

  5. Eduardo says:

    Abstraindo a questão locar ou não a tabela, na prática, eles fazem a mesma coisa?

Skip to main content