Indices

Uma das principais “armas” de um DBA contra a performance ruim são os indices, mas…

O que são esses indices?
Indices em sua descrição são: Estruturas de acesso secundarias, em formato de arvore, que fornecem um acesso mais rapido, direto e direcionado para o dado solicitado.

Qual o proposito de criar um indice?
Melhora de performance.

Quais tipos de indice existem?
Clustered -> Quando este indice é criado, fisicamente sua tabela sera organizada pela coluna do indice cluster (Pode ser mais de uma coluna), limite de apenas um indice cluster por tabela.
Nom-Clustered -> Apenas uma estrutura de acesso secundario, N indices por tabela.
UNIQUE -> Indice cujo campo chave não se repete.

Aonde é criado um indice?
Um indice é criado em uma ou mais colunas de uma tabela.

Qual a sintaxe basica de criação de indice?
CREATE INDEX Index_Name ON Table_Name(Column_Name)

Todas as colunas precisam ter indices?
Não.

Como eu sei qual coluna precisa de um indice?
A grosso modo, as colunas em seu predicado (WHERE de uma query) farão bom uso de indices.

O que é melhor, um indice unico (Apenas uma coluna) ou um indice composto (Mais de uma coluna)?
É necessario testar a performance de ambos e verificar seus planos de execuções.

Qual a desvantagem de se criar um indice?
Instruções DML (Data Modification Language [Insert, Update e Delete]) fazem com que indices sejam atualizados untamente com a instrução, o que pode causar uma queda de performance.

Indices em ambiente OLAP e OLTP:
OLAP -> Ambiente de analise da dados, normalmente não possuem instruções DML portanto possuem normalmente muitos indices.
OLTP -> Ambiente transacional, 90% do tempo utilizado para instruções DML, existem indices, mas em quantidade reduzida.

Existem ações a serem tomadas para a manutenção de um indice?
Sim, voce deve utilizar das opções:
REBUILD -> Reconstroi todo o indice.
REORGANIZE -> Pega um indice ja criado e o reorganiza.
Como uma dica: Algumas pessoas costumam deixar um reorganize diario em seus indices, e um rebuild nos finais de semana por exemplo…..

Agora que ja fiz uma breve explicação sobre o que é e para que servem os indices, irei ao foco desde post, as opções extras ao se criar um indice. Existem 11

opções, e cada uma delas sera descrita abaixo:

1. FILL_FACTOR
Traduzindo do ingles literal, quer dizer, fator de preenchimento, e é isso mesmo, a opção FILL_FACTOR aceita valores percentuais, cujo qual sera

quase uma regra para o quanto da pagina de indice sera preenchido, normalmente os autores dizem que o valor ideal esta entre 70% e 80%, mas é claro que

tudo depende de seu ambiente.
Qual a desvantagem de se criar um FILL_FACTOR baixo: Seu indice ficara muito fragmentado, com muito espaço alocado porem não utilizado.
Qual a desvantagem de se criar um FILL_FACTOR alto: Novas entradas no indice farão com que o mesmo tenha que alocar uma nova pagina, reduzindo a performance da instrução original.
Quando utilizar: Normalmente um percentual de 70%~80% é utilizado para ambientes em geral, a não ser que sua tabela não va realmente sofrer modificações, ai sim o 100% é o ideal.

2. PAD_INDEX
Essa opção só tem sentido de utilização quando junto da opção FILL_FACTOR (Que veremos mais a frente…).
Caso uma linha de indice não caiba no percentual especificado pelo FILL_FACTOR, essa opção (Se estiver como ON), fara com que o DBEngine coloco um novo valor valido em seu FILL_FACTOR para caber ao menos uma linha.

3. SORT_IN_TEMPDB
Todo indice possui uma estrutura de arvore, que por sua vez obviamente é ordenada, portanto, na criação de um indice é obrigatorio dizer que existe uma ordenação dos dados, esse comando faz com que toda essa ordenação seja feita na base de sistemas TEMPDB, que normalmente esta em um RAID 0 ou 1 para melhor performance por exemplo, alem de não concorrer diretamente com os dados em I/O.

4. ONLINE
Opção disponivel apenas em versões Enterprise ou superior, ao se criar um indice sem essa opção, o SQL Server ira fazer com que a tabela fique em LOCKX, o famoso lock exclusivo, ou seja, enquanto ocorre a criação do indice, ninguem mais acessa a tabela, o que pdoe causar um grande fila de transações bloqueadas, com essa opção, o lock é feito por linha, claro que existe um processamento maior, porem não ira bloquear outros usuarios de realizarem seus processos.

5. DROP_EXISTING
Esta faz com que caso ja exista um indice com o mesmo nome na mesma tabela, o mesmo seja substituido pelo novo.

6. MAXDOP
O numero especificado nessa opção é exatamente o numero de processadores que o SQL Server ira utilizar para a criação do indice.

7. IGNORE_DUP_KEY
Em um insert em massa feito em uma tabela/coluna que possua um indice UNIQUE, quando essa opção esta em OFF (default), toda a transação sofrera RollBack, se estiver em ON, apenas o insert especifico ira falhar, e continuara normalmente o insert.

8. STATISTICS_NORECUMPUTE
O default dessa opção é OFF, que faz com que cada para cada operação DML seja feita uma atualização no indice, é bom do ponto de vista de adminitração, que automatizara mais uma tarefa, porem, pode ser ruim pois pode causar perda de performance.

9. ALLOW_ROW_LOCKS
Quando ON (default) diz para o SQL Server que locks de linha em uma tabela é aceitavel quando o indice esta sendo utilizado.

10. ALLOW_PAGE_LOCKS
Mesma descrição do item anterior porem dessa vez para pagina.

11. DATA_COMPRESSION
Aqui as opções NONE, PAGE e ROW são aceitas, no caso, essa opção faz com que a partição e o indice sofram uma compressão de pagina ou linha.
Como opção extra, é possivel se especificar a partição a sofrer a compressão.

Ao se criar indice em colunas com tipos de dados diferentes (Como XML, ou uma coluna computada), existem maneiras e regras e serem seguidas:

Colunas Computadas
XML

Anúncios
Post a comment or leave a trackback: Trackback URL.

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 )

Imagem do Twitter

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

Foto do Facebook

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

Foto do Google+

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

Conectando a %s

%d blogueiros gostam disto: