DBCC INDEXDEFRAG

Esse é mais um artigo da série “Saga da otimização com comandos antigos”

• Parte 1: SET STATISTICS IO
• Parte 2: DBCC DROPCLEANBUFFERS
• Parte 3: DBCC SHOWCONTIG
• Parte 4: DBCC PAGE
• Parte 5: sp_spaceused
• Parte 6: DBCC IND

No último artigo, falei sobre a forma mais rápida de executar um scan. Nesse artigo vou falar sobre o INDEX SCAN.

Seria ele mais rápido que o HEAP SCAN do artigo anterior?

Vamos começar colocando ordem na heap!

Heap é Desorganizada

Heap scan é uma técnica muito rápida porque favorece o uso das operações de read-ahead.

Entretanto, imagine o que acontece quando sabemos que a query retorna apenas um registro:

SELECT * FROM produtos WHERE id = 1234

Isso poderia ser reescrito assim:

SELECT TOP(1) * FROM produtos WHERE id = 1234

A primeira query realiza a leitura da tabela inteira para retornar todos os produtos com ID = 1234 e o comando faz a leitura de todos os registros para se certificar que, de fato, existe apenas um registro. Por outro lado, a segunda query usa TOP(1) para notificar que é necessário apenas um registro. Assim, o comando realiza o scan de tabela e finaliza logo que encontrar o primeiro registro. Estatisticamente, podemos dizer que a segunda query executa na metade do tempo da primeira.

Concluímos que a heap é um conjunto desorganizado de página de dados, ou seja, a única forma de acesso é através do Heap Scan. Uma das formas de tornar o acesso mais rápido é usando o TOP(n) para encerrar o scan o mais rápido possível.

Heap Organizada

Existe uma forma de tornar a Heap mais eficiente: ordenando os registros nas páginas de dados.

Heap: dados desorganizados – será que existe o ID = 1234? onde?

Heap Organizada: é fácil de encontrar o ID = 1234.

Tornar a heap organizada é fácil! Basta transformar a Heap em uma Tabela com Clustered Index.

Clustered Index

Clustered Index. Sim, esse é o nome de uma Heap Organizada.

Infelizmente não gosto desse nome porque muita gente considera o Clustered Index mais como índice ao invés de Tabela. Índice é uma estrutura para ajudar o desempenho do banco de dados e você pode criar vários na mesma tabela (10, 20, 30..). Entretanto, o Clustered Index é especial e você só pode criar um porque ele é a própria tabela! Não há como organizar a Heap de duas formas diferentes ao mesmo tempo.

Clustered index organiza as páginas da tabela. A forma mais fácil de enxergar isso é através do comando DBCC IND. Vamos investigar o index_id = 1, que corresponde ao índice clustered.

A página 1:549 aponta para a próxima página next_id = 1:548 e anterior prev_id = 1:550. Podemos investigar a página anterior 1:550, que aponta para as páginas 1:549 e 1:551. Aqui é possível ver que a primeira página da tabela é a 1:557, pois ela não possui um ponteiro para a página anterior.

Desorganizando o Clustered Index

Sempre que realizamos uma operação de Table Scan em uma tabela com índice clustered, dizemos que:

Table Scan = Clustered Index Scan

Em geral o desempenho do Clustered index scan é comparável com o Heap scan. O motivo é que as páginas estão organizadas e fica fácil de encontrar as informações. Entretanto, existe uma forma de bagunçar o local onde os dados estão fisicamente armazenados: basta fragmentar a tabela e começar a distribuir os dados em diferentes páginas.

Primeiro vamos criar uma tabela e adicionar a ordenação com índice clustered:

Em seguida, adicionamos os produtos sem uma ordem específica.

O resultado do DBCC IND mostra claramente que as páginas anteriores e posteriores estão completamente fora de ordem.

Como as páginas estão desordenadas, Clustered Index Scan não consegue executar transformar as operações de leitura em operações de read-ahead.

Isso torna o acesso ao índice mais lento. Como resolver? Basta desfragmentar o índice. No passado, usava-se bastante o comando DBCC INDEXDEFRAG. Hoje não há motivo para usar o INDEXDEFRAG, pois se tornou obsoleto. Ao invés disso, procure usar a sintaxe do ALTER INDEX REORGANIZE que possui mais flexibilidade e suporte a partições.

ALTER INDEX nome_indice ON produtos REORGANIZE

Pronto! Agora o clustered index scan é praticamente tão rápido quanto heap scan e deixa a tabela organizada.

Conclusão

É recomendado organizar os dados da tabela usando um índice clustered.

Nesse artigo comentei que o desempenho Clustered Index scan é igual ao Heap scan ou inferior (quando o índice se encontra fragmentado). Portanto, criar um índice clustered sem qualquer estudo pode causar degradação de performance.

Criar um clustered index incorreto é pior do que não te-lo.

No próximo artigo vamos explorar um pouco mais a estrutura do índice, também conhecida por BTree+.