SQL Server - Índices

O Sql acessa dados de dois modos: Table scan, atravês de índices...

por Thiago Pastorello Gervazoni

O Sql acessa dados de dois modos:

1) Table scan
Varre todas as páginas começando do inicio da tabela, e extraindo o pedido da query

2) Atravês de índices

1. Índices clusterizados

Em um indice clusterizado o leaf-level (última camada de índice) é a página atual, porque os dados são fisicamente ordenados de forma ascendente.,por isto é possível apenas um índice clusterizado por tabela. A ordenação tanto fisicamente dos dados como do índice é a mesma.

Os índices cluster são bastante usados em colunas muito acessadas por range de valores, ou acessadas por ordenações, ao criar um índice clusterizado tenha em mente:

• A chave "Key value" são únicas e por isto ao criar este índice automáticamente é criado um UNIQUE
• O maior valor de um índice clusterizado é 5% da tabela (dependendo da chave do índice)
• Ao criar uma chave primária por default ela vem como clusterizado se não houver já um índice cluster, mas é opcional podendo mudar para não cluster

O índice clusterizado é parecido a uma agenda de telefone onde ficam as iniciais do alfabeto ficam logo em seguida os nomes e telefones das pessoas.

2. Índices Não clusterizados

Quando o índice é não clusterizado a construção é feita no heap (amontoado), o SQL usa as linhas como identificadores de índices. A linha identifica local de informação de armazena de dados.

Chama-se heap porque na sua tradução significa amontoado, isto porque os dados não tem um ordem lógica, são gravados nas páginas que tem espaço disponível.

Nesta arquitetura quem se incumbe de dizer se existe espaço ou não é o IAM (Index Allocation Map Pages), a tabela de sistema sysindexes armazena a primeira página IAM associado ao Heap. Um IAM diz ao SQL que usa um índice não clusterizado que uma query acabou de apagar um registro, e ali existe um espaçõ para a próxima gravação.

Os índices não cluster são muito usados quando necessitamos encontrar uma informação de várias formas diferentes como por exemplo, queremos encontrar um livro pelos campos Nome, Autor, Editora, Tipo, Número de páginas etc, porque ao contrário do cluster o índice não cluster é aconselhado para colunas com alta densidade (também chamado baixa cardinalidade), ou seja, bastante valores repetidos.

A ordem do leaf-level de um índice deste tipo é diferente da ordenação fisica, podendo até ter 249 índices não cluster por tabela.

O índice não clusterizado é parecido com um livro, os dados ficam em um lugar e o índice em outro

3. Indice cluster misturado com índice Não cluster

O SQL server usa a chave clusterizada na página de índice para colocar o ponto do índice, e uma chave (clustering key) armazenando o local da informação. Tabelas que contém índices cluster e índices não cluster é muito comum, o melhor nestas situações é criar o indice cluster primeiro (que irá organizar os dados e o índice em ordem ascendente) e depois criar o índice não clusterizado nas colunas que forem necessárias como FK´s, ou colunas muito acessadas. O SQL acaba por somar os 2 índices e gera um I/O maior porque usa a estrutura b-tree para acessar os dados.

Existem 2 maneiras de desfragmentar um índice:

1) DROP e Recriar o indice

2) Dar REBUILD no indice especificando um fillfactor, usando o DBCC INDEXDEFRAG

Ao realizar um insert, delete, update automaticamente o SQL tem que atualizar o endereço de índice, e com o tempo o mesmo fica fragmentado causando até lentidão na busca da informação, por isto é importante nestes casos checar a fragmentação de alguns índices. Use o DBCC SHOWCONTIG, ou para ver se dados e índices estao cheios. E depois use o DBCC INDEXDEFRAG para desfragmentar os índices.

Vantagens do DBCC INDEXDEFRAG

• Defragmenta o leaf level do indice
• Organiza fisicamente o leaf-level
• Melhora o index-scaning
• Retira páginas em branco (obs : Não diminui espaço para isto necessita o shrink)
• Posso rodar o defrag com o banco on-line

Ex:
DBCC INDEXDEFRAG (pubs, tt2 , PK__tt2__2F10007B)

OBS: O indexdefrag não melhora a performance quando o indice esta fisicamente fragmentado no disco, para desfragmentação fisica tem que recriar o indice (na maioria das vezes é mais rápido) .

FILL FACTOR

O Fill factor varia de 0 a 100%, e sua função é alocar espaços em branco em cada página para reservar espaço para a inserção de novas linhas, para que não haja o page split.

Fill factor aloca especo no leaf-level ( último estágio de índice ), o FILL factor é usado apenas quando o índice é criado ou reconstruido, o SQL não mantêm esta porcentagem dinamica.

Posso trocar o fill factor com a sp_configure.

OBS: Crie fillfactor onde tem indices clusterizados que tem muita insercao, ou o indice cluster onde é muito modificado.

Ex:
CREATE INDEX I_001 ON orders(codigo) WITH pad_index, fillfactor=70

Page SPLIT ou quebra de página, so ocorre em índice clusterizado visto que deve-se manter a ordem lógica dos dados. Em não clusterizado os dados são inseridos onde tiver espaço disponível, se der um update que requer mais espaço que o original em um HEAP, ele leva o linha inteira para outra página e deixa um pointer apontando para onde o registro mudou, isto gera um overhead e lentidão nos comandos de insert e update, por isto chamado page SPLIT, o fill factor impede o page split.

Se usar o DROP_EXISTING na criação do índice voce pode mudar as caracteristicas da chave primária e Unique como:

• Mudar de não cluster para cluster
• Mas nunca de Cluster para não cluster
• Mudar a coluna dos índices
• Trocar a porcentagem do PAD_INDEX e Fillfactor

Ex:
CREATE UNIQUE INDEX I001 ON dbo.tt2(CODIGO) WITH DROP_Existing, FILLFACTOR=65

Algumas Dicas quanto a índices

- Colunas para se indexar

• PK´s
• FK´s
• Colunas que se acessam por ranges (BETWEEN, > < )
• Colunas que se usa para sort order
• Colunas que se usa para grouping ou agregações

- Colunas para não se indexar

• Coluna que você raramente referencia numa query
• Colunas com alta cardinalidade como por exemplo Masculino e Feminino
• Colunas com Ntex , Image, Text

Campeões de performance

1. Colocar indice clusterizado para campo identity melhora muito a performance porque o uso frequente o deixa na memoria, e índice não cluster em demais campos mais utilizados.

2. View Indexada ( já existe uma coluna minha ensinando a criar)

3. Índices que cobrem queries
Índices que cobrem queries são índices que contem todas as informações necessárias, não deixando o SQL fazer nenhuma leitura nos dados fisicos, porque todas informações estão nos índices. Para criar um índice assim basta copia os campos do SELECT campo1,campo2, campo3 e criar o índice nesta mesma ordem e com estes mesmos campos. Pronto o acesso é rápido e sem leitura fisica de dados.

4. Usar o INDEX TUNNING WIZARD
Este aplicativo do SQL analisa todas as queries e indica os melhores índices a serem criados. nas tabelas.

Na próxima coluna veremos como usar o Index Tunnig Wizard, que faz todo este trabalho de criação de índices por nós, mas é muito importante entender o conceito, função e como criar os índices, mesmo que tenha uma ferramenta que faça tudo isto para nós.

Um abraço.