Ao contrário do índice não cluster, o índice cluster dispensa a utilização de ponteiros e atua diretamente nas páginas de dados de uma tabela. Tabelas que não possuem índice cluster são conhecidas como heap tables e nelas as linhas são gravadas nas páginas de dados sem que exista uma ordenação específica (são gravadas de forma desordenada, aleatória), bem diferente das tabelas que possuem índice cluster, onde as linhas são gravadas seguindo a classificação da chave do índice.
O processo de desfragmentação
Os processos de desfragmentação de índices existentes no SQL Server 2000 não funcionam para desfragmentar tabelas sem índices, eles foram criados para desfragmentar apenas índices. Como as páginas de dados de uma heap table não são regidas por um índice, não é possível desfragmentar uma tabela sem índice cluster utilizando os comandos DBCC DBREINDEX e DBCC INDEXDEFRAG.
Para tentar entender melhor, vamos imaginar um cenário onde temos uma tabela com índice cluster e uma outra tabela sem índice cluster. Após várias operações de INSERT, UPDATE e DELETE teremos algo como apresentado na figura abaixo:
Podemos observar que nas páginas de dados da tabela com índice cluster as linhas são gravadas seguindo a classificação do índice. Já na tabela sem índice cluster, as linhas são gravadas de forma aleatória.
Após a desfragmentação das tabelas, o resultado seria semelhante ao seguinte:
Na tabela com índice cluster, a desfragmentação deixou a tabela mais otimizada, liberando as páginas 3 e 4 e condensando os dados nas páginas 1 e 2. Na tabela sem índice cluster, não ocorreram alterações, a tabela continuou utilizando 4 páginas e os dados continuaram fragmentados dentro das páginas.
Com isso, podemos concluir que as páginas de dados de tabelas que possuem índice cluster serão automaticamente desfragmentadas ao executar o processo de desfragmentação disponível no SQL Server 2000. Já as páginas de dados de tabelas sem índice cluster (heap), não serão desfragmentadas.
Como fazer então para desfragmentar uma heap table? O processo de desfragmentação de uma heap table pode ser executado basicamente de duas formas:
1. Movendo os dados da heap table para uma tabela temporária, depois excluir a heap table, recriá-la e re-inserir os dados na nova tabela.
2. Localizar na heap table uma coluna que seja única, criar um índice cluster na coluna e depois excluir o índice cluster.
Um outro ponto onde devemos ficar atentos sobre as heaps é que estas são devoradoras de espaço em disco, principalmente se os dados sofrerem muitas atualizações ou exclusões. Espaços subutilizados em páginas de dados são sinônimos de baixo desempenho, pois força o subsistema de disco a efetuar um número maior de I/Os. A solução mais prática e recomendada é identificar as heap tables e efetuar a criação de índices cluster adequados.
Para identificar as tabelas sem índice cluster em um database, execute a query abaixo no Query Analyzer:
SELECT object_name(i.id) from sysindexes i
INNER JOIN sysobjects o ON i.id=o.id
WHERE indid=0 AND xtype=’U’
O exemplo prático
Para demonstrar na prática a ineficiência da desfragmentação das páginas de dados em tabelas sem índice cluster, vamos criar uma tabela (TBIDXNCLUSTER) com 20.000 registros e apenas com um índice não cluster. O script de criação da tebela pode ser visto abaixo.
USE Pubs
GO
CREATE TABLE TBIDXNCLUSTER (col1 int IDENTITY(1,1), col2 char(100),col3 char(100))
GO
declare @count int
SET @count = 1
WHILE @count <20000
BEGIN
INSERT INTO TBIDXNCLUSTER
VALUES (‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’,’BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB’)
SET @count=@count+1
END
GO
— Cria índice Não Cluster
CREATE NONCLUSTERED INDEX IDXNCluster ON TBIDXNCLUSTER(col2)
Logo após a criação da tabela e seu respectivo índice, notamos que tanto a fragmentação da tabela (index ID: 0) quanto do índice (index ID: 2) são praticamente nula. A tabela ocupa 2042 páginas e o índice 155, com um fator de densidade médio das páginas (Avg. Page Density) superior a 96%.
Após a exclusão de um grande volume de registros (para forçar a fragmentação), é possível notar uma fragmentação de quase 50% nas páginas de dados da tabela e também nas páginas do índice. Para facilitar a exclusão dos registros, utilizei o script abaixo para exclui todos os registros onde col1 é par.
DELETE FROM TBIDXNCLUSTER WHERE col1%2=0
Para desfragmentar as páginas da tabela, executamos o processo de reindex (DBCC DBREINDEX), porém, observe que apenas o índice não cluster foi desfragmentado, voltando a atingir um fator de densidade média superior a 99% e tendo o número de páginas reduzido para 77.
Como as páginas de dados da tabela não estão associadas a um índice cluster, o processo de reindexação não teve efeito sobre elas, mantendo a fragmentação de 48%. Observe também que o número de Pages Scanned continua exatamente o mesmo de quando a tabela foi criada, 2042.
Para demonstrar a diferença quando se utiliza índice cluster, transformamos a tabela em uma tabela clusterizada. Para isso, removemos o índice não cluster e criamos um índice cluster.
DROP INDEX TBIDXNCLUSTER.IDXNCluster
GO
CREATE CLUSTERED INDEX IDXCluster ON TBIDXNCLUSTER(col2)
Com a criação do índice cluster, a densidade média das páginas subiu de 48.35% para 98.78%, graças à redução no número de páginas que caiu de 2042 para 1000.
Conclusão
Como vimos, tabelas sem índice cluster são péssimas para performance porque as páginas de dados da tabela nunca serão desfragmentadas, como conseqüência, o subsistema de disco também será sobrecarregado pois terá que efetuar mais leituras que o necessário para obter as informações desejadas.
A solução é escolher uma coluna adequada e criar um índice cluster na tabela. Caso não exista uma coluna adequada, a criação de uma coluna do tipo identity pode resolver o problema, mas evitem ao máximo deixar tabelas sem índice cluster, principalmente se forem tabelas com grandes volumes de dados ou utilizadas em JOINS.
Um abraço
Nilton Pinheiro