Por que é importante usar o índice Clustered?

Para um melhor aproveitamento da leitura deste artigo é importante que você possua conhecimentos sobre a aplicação de índices no SQL Server.

Por esses dias me deparei com uma situação em um projeto o qual o cliente possuia uma tabela enorme com cerca de 1 bilhão de registros  e  cerca de 200GB de tamanho,  esta tabela possuia diversas colunas e a maioria delas estavam indexadas, porém não havia um índice clustered.

Como sabia dos benefícios do índice clustered, fiz alguns testes e após inserir um índice clustered nesta tabela o tamanho da tabela foi reduzido em 30% e as consultas a esta tabela ficaram bem mais rápidas.

Como diz aquele ditado: “Não basta matar a cobra,tem que que matar a cobra e mostrar o pau”, resolvi criar aqui um exemplo ilustrando esta experiência.

Em teoria como o índice clustered fica no nivel folha da página de dados ele  além de eliminar lookups no plano de execução, reduz a quantidade de páginas a serem lidas pelo SQL Server, vamos comprovar esta teoria?

1. Primeiramente  vamos criar uma tabela com 7 colunas,  esta tabela é meramente ilustrativa, portanto não há nenhuma preocupação em relação aos tipos de dados e padrões de nomes de colunas.

2. Agora vamos popular esta tabela com dados ficticios, neste caso inserimos 1 milhão de linhas.

 

3. Agora vamos extrair informações sobre o nosso heap de dados, para isso nós usamos a Dynamic Management Function  sys.dm_db_index_physical_stats e a procedure de sistema sp_spaceused.

Vejam que temos um heap de dados com pouco mais de 22 mil páginas e a soma dos dados com o tamanho do heap é pouco maior que 173MB

4. Só para efeito de comparação  vamos ativar a coleta de estatisticas (SET STATISTICS TIME – para coleta da duração da query e SET STATISTICS IO – para coleta de dados de informações  de I/O)  e realizar uma query simples na tabela.

Vejam que ele percorreu o heap 3 vezes e leu as 22mil páginas na memória.

5. Agora vamos inserir um índice não clustered, refazer os passos até aqui e rever os resultados.

Vejam que agora nós temos 72MB para o índice que possuí 3 níveis sendo 9.175 páginas para o primeiro , 92 e 1 respectivamente para os próximos níveis.  E agora o SQL Server realiza a leitura de somente 9.635 leituras na memória e o tempo  de execução foi 2.4 vezes mais rápido.

6. Agora é a hora da verdade, vamos inserir um índice cluster, refazer os passos até aqui e rever os resultados.

Vejam que agora com a introdução do índice clustered o tamanho dos registros diminuem e com isso o tamanho do índice não cluestered também é menor, além disso o tempo de execução foi quase duas vezes mais rápido (1.7  vezes).

A tabela a seguir resume as informaçoes obtidas nos testes acima

Conclusão:

O uso de índice clustered pode ajudar a melhorar a performance de acesso a dados no banco de dados.

É uma boa prática utilizar índices clustered em campos de chave (Primária/Estrangeira) e/ou campos de data.

É isso aí galera, espero que este post possa ajudá-los, fiquem a vontade para comentar.

Abraços,

Giuliano B. Cardoso

Referências Bibiliográficas:

 

Anúncios

1 Response to “Por que é importante usar o índice Clustered?”


  1. 1 Gustavo Lira 05/12/2011 às 22:40

    Show de bola. Estava me matando aqui. Sou novo na área, joguei no google te achei cara, te add na barra de favoritos. Valeu!. Gustavo


Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair /  Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair /  Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair /  Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair /  Alterar )

w

Conectando a %s




Giuliano B. Cardoso,
Administrador de Empresas com Habilitação em Informática, atua em Tecnologia da Informação há mais de 10 anos, especialista em banco de dados SQL Server, atualmente atua como consultor em Business Intelligence e instrutor de cursos oficiais Microsoft.
Possuí as seguintes certificações: ITIL-F, MCP,MCDBA SQL 2000, MCTS SQL 2005/2008, MCITP SQL2005/2008 e MCT

Mais acessados

Anúncios

%d blogueiros gostam disto: