次の方法で共有

DBCC DBREINDEX

  • [アーティクル]

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

No último artigo, comparamos o INDEX SCAN e HEAP SCAN e mostramos que o Heap Scan tem melhor desempenho.

Impacto do Clustered Index

O exemplo do artigo anterior, criamos uma tabela de produto e adicionamos um índice clustered sobre o campo “nome”.

image

Adicionamos 10.000 registros aleatórios:

image

No final, temos uma tabela com 15MB.

image

O resultado final foram 1828ms e 1.900 leituras em disco.

image

Podemos resolver o problema rodando o comando DBCC DBREINDEX (obsoleto) ou sua nova versão ALTER INDEX REBUILD:

ALTER INDEX nome_indice ON produtos REBUILD

A tabela desfragmentada fica em 10MB e o tempo de execução da query é de 900ms.

Organizando a Tabela

A heap consumia os mesmos 10MB, pois não fragmentava. A query executava em 892ms e realizava 1257 leituras em disco. Então, se o Heap Scan tem melhor desempenho, então por que criar um Clustered Index?

Como vimos no artigo anterior, não vale a pena criar um índice clustered apenas por criar.

É essencial que a escolha da chave para Clustered Index siga as recomendações:

  • Pouca alteração (UPDATE) – mais importante!
  • Campos pequenos (até 8 bytes)
  • Valores sequenciais
  • Valores distintos

No nosso exemplo, há dois problemas em criar um índice clustered usando a chave “nome”: 1) valor não-sequencial, causando a fragmentação natural da tabela. 2) campo longo (800 bytes), que causará futuro problemas com índices non-clustered.

Vou citar 3 motivos para criar um Clustered Index:

1. Evitar problemas da Heap

Embora o heap scan tenha melhor desempenho, a heap esconde alguns problemas:

Por isso, recomendamos criar um clustered index evita esses comportamentos exóticos da Heap.

Campos Identity são boas escolhas porque são campos pequenos (tipo INTEGER), sequenciais e distintos.

2. Período de Dados

Frequentemente encontramos consultas com filtros de data. Quando é necessário ler uma grande quantidade de dados, o índice clustered pode agregar os dados próximos e facilitar as operações de scan.

SELECT * FROM tabela WHERE data_inicio between ‘2016-05-01’ AND ‘2016-05-10’

Campos de data são pequenos e normalmente inseridos sequencialmente. Ao criar um índice clustered, a tabela fica ordenada pela data e facilita as buscas com período de tempo. No exemplo acima, somente os dados do mês de maio serão lidos, sem a necessidade de consultar a tabela inteira e acessar dados dos meses de abril, março, fevereiro, janeiro.

3. Ordenação de Consulta

Quando encontramos consultas usando ORDER BY, podemos sugerir a criação do campo como clustered index.

SELECT * FROM tabela ORDER BY Data

Ao criar um índice clustered, deixamos a tabela pré-ordenada pelo campo chave. Dessa forma, economizamos o recurso de CPU e memória necessário para ordenar os registros em tempo de execução. Entretanto, é importante que a chave tenha pouca (ou nenhuma) alteração.

Exemplos ruins:

SELECT * FROM tabela ORDER BY ultima_modificacao

SELECT * FROM tabela ORDER BY preco DESC

Não adianta criar índices clustered para os campos “ultima_modificacao” e “preco”, pois esses são campos que sofrem UPDATE frequentes.

E as Primary Key?

Geralmente uma Primary Key deve adotar um índice Non-Clustered (NCL).

Entretanto, existem casos para adotar um Clustered Index (CL) em uma Primary Key (PK):

  • Se a Primary Key for um campo IDENTITY, então é uma boa escolha para índice CL.
  • Em banco de dados OLAP, operações de JOIN com grande volume de dados podem se beneficiar de índices CL

Por outro lado, há um incontável número de exemplos onde as Primary Keys são criadas como Clustered Index indevidamente, causando problema de fragmentação e impacto na performance.

Evite fragmentar o Clustered Index

A regra é simples: evite fragmentar o clustered index e tenha o desempenho igual a Heap.

Se essa regra for seguida, eliminamos os três problemas escondidos da heap:

No próximo artigo, vamos explorar um pouco mais sobre os índices. Até o momento, falamos apenas sobre o comparativo do Heap scan e Index scan. Entretanto, a vantagem do índice é usar algo muito mais rápido que o scan.