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
=============================================================
This posting is provided "AS IS" with no warranties, and confers no rights
=============================================================