Trabalhado com Index SQL SERVER

Trabalhado com Index SQL SERVER
Os índex são mecanismos criados pelos bancos de dados para melhora a recuperação
das informações contidas nas tabelas, sua funcionalidade é muito semelhante ao de um
sumario de um livro, que quando queremos ir a um determinado assunto recorremos ao
sumário para da mais rapidez na busca da mesma.
A sintaxe básica para a criação de um índice é :
CREATE [ TIPO ] INDEX [NOME]
ON [TABELA]
(COLUNAS)
PAD_INDEX = { ON | OFF } | FILLFACTOR = fillfactor
Exemplo:
CREATE NONCLUSTERED INDEX INDEX_PESSOA_NOME ON PESSOA(NOME)
Onde:
Tipo: Os index podendo ser unique, clustered ou no clustered
Nome: Nome que devemos da ao index
Tabela: Nome da entidade que ira criar o index
Colunas: Nome das colunas que iremos usar respeitando as restrições.
No SQL Server, iremos encontrar dois tipos de índex conhecidos como clustered ou
nonclustered, onde teremos em uma tabela só um índex clustered que geralmente é
criado quando criamos a constraint primarykey (chave primaria) permitido assim que
na mesma tabela tenhamos vários índexs nonclustered, onde para podemos criar um
índex temos que obedecer o seguintes critério de 16 colunas e o tamanho do nível de
index de aproximadamente 900 bytes.
Índex Clustered: Como falamos anteriormente o index clustered é um index criado
geralmente quando criamos uma primary key (Chave Primaria). Ele Possui uma
ordenação a nivel de tabela .
IndexNon-Clustered: Possui uma ordenação a nível de pagina possui as mesmas
restrições dos index clustered
As vezes temos que recuperar as informações das tabelas o mais rápido possível porém
as vezes usamos os índex em uma determinada consulta, porém não obtemos as
informações com a rapidez que foram solicitada, para tenta solucionar esse problema foi
inserido no SQL Server a opção de filtro, tal opção só é demonstrada quando usamos os
índex em conjunto da clausula ‘Where’ podemos aumenta a performance.
Tabelas que sofrem muitas alterações (Insert, Update e Delete) refletem essas
modificações nos índices, pois acabam deixando espaços em brancos nas páginas dos
mesmos. Estes espaços não utilizados refletem em maior espaço em disco o que acarreta
um desperdício de tempo ao percorrer a estrutura do índice.
Os acessos aos dados das tabelas e índices podem ser de duas formas, SEEK ou SCAN.
Table SCAN - busca em TODOS os elementos da estrutura (que pode ser uma tabela ou
um índice). É usado quando não possui índices que atendam a instrução de select ou
quando a quantidade de registros que a query retorna (em percentual) é grande.
· SEEK - busca binária nos elementos de um índice. É usado quando existe um índice
que é adequado e a quantidade de registros (em percentual) retornados é pequena.
Sendo assim, é possível executar as seguintes operações para acesso nas tabelas/índices:
· TABLE SCAN - Busca em todos os elementos da tabela, de forma seqüencial;
· INDEX SCAN - Busca em todos os elementos de um índice nonclustered, de forma
seqüencial;
· INDEX SEEK - Busca binária num índice nonclustered;
Alem disto, temos sempre que ter o cuidado quando trabalhamos com index para que
não hajam lentidões apesar dos índices terem sido criados. Para auxiliar o SQL Server
possui duas opções a Fillfactore a Pad_Index, onde a opção FillFactor especificar uma
porcentagem de espaço livre no níveis, onde esse espaço será usado na criação ou
recriação de um índex e quando usamos o Pad_Index estamos aplicado a porcentagem
de espaço livre do nível FillFactor nos níveis intermediário.
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.
dado
dado
dado
dado
Entre um dado e outro armazenado num arquivo de índices pode facilitar na inserção de dados
quando houver um espaçamento entre os registros, pois o SQL não precisa reordenar o
arquivo todas vez que for inserido um novo registro. Porem a consulta pode ficar mais lenta
quanto maior for o espaçamento entre registros.
O ideal é ter um % de fillfactor =0 quando o arquivo só vai ter consultas e um percentual
qualquer quando o arquivo tem inserções.
Este índice pode ser reajustado à medida que for necessária uma performance melhor.
PAD_INDEX = { ON | OFF }
ON = A porcentagem de espaço livre especificada por fillfactor é aplicada às páginas de nível
intermediário do índice.
OFF ou fillfactor não está especificado = As páginas de nível intermediário são preenchidas
até próximo de sua capacidade, deixando espaço suficiente para pelo menos uma linha do
tamanho máximo que o índice pode ter, considerando o conjunto de chaves em páginas
intermediárias.
Colunas que devem ser indexadas.
Crie índices em colunas pesquisadas com freqüências como:
• Chaves primárias (PK).
• Chaves externas (FK) ou colunas usadas frequentemente para unir tabelas.
• Colunas pesquisadas para a localização de faixa de valores de chave. (quando usamos a
clausula IN, NOT IN, BETWEEN).
• Colunas acessadas na ordem de classificação (ORDER BY)
• Colunas usadas durante a agregação. (GROUP BY)
Colunas que NÃO devem ser indexadas.
Não referencie colunas que:
• Raramente são usadas em um consulta.
• Contenham poucos valores únicos. Por exemplo, um índice em uma coluna com dois
valores, Masculino e feminino, retorna uma alta porcentagem de registros.
• Sejam definidas com os tipos de dados text, ntext, varchar e image. Não é possível
indexar colunas com esses tipos de dados.
Criando índices.
Ao criar índice em uma tabela considere os fatos:
• Você deve ser proprietário da tabela.
• Quando se cria uma constraint do tipo PRIMARY KEY e UNIQUE, automaticamente o
SQL Server cria índices clustered para elas.
• Você pode criar índices em VIEWS
• Você não pode criar índice em colunas que já existam os mesmos.
• Você pode criar índice na hora de criação de sua tabela.