O que é um índice

Um índice é um atalho para o dado. Por exemplo, ao invés de fazer uma varredura numa tabela (table scan) em 10.000 registros para saber qual é o mais caro eu posso consultar um índice e em 3 ou 4 passos descobrir a resposta correta.

Os índices melhoram e muito a performance de uma pesquisa mas se forem exagerados os custos de manutenção desses índices podem ser maior que os benefícios que eles trazem.

Dicas de otimização de índice

Conceitos gerais:

Quando uma tabela não possui índices que atendam a consulta o servidor sql server procura em todos os registros da tabela e isso é chamado de table scan e é o mais custoso procedimento possível para o servidor. Literalmente o servidor procura do primeiro ao último registro, um por um, se atende ou não o quesito de pesquisa, por exemplo. Se não há indices na tabela a ordem será dada pela inserção dos registros na tabela, completamente desordenada.

Sabemos que o índice é um atalho entre a pesquisa e os dados. Um ponteiro para o resultado que atende o quesito desejado. Neste documento mostramos como você pode melhorar a velocidade de suas consultas escolhendo os índices adequados, quais tipos de índices o MS SQL suporta e qual é a vantagem e a desvantagem de usar índices em uma situação específica.

Existem índices clusterizados(agrupados) e não clusterizados.

Um índice clusterizado é um tipo especial de índice porque reordena a maneira como os registros na tabela são fisicamente armazenados. Portanto, a tabela pode ter apenas um índice clusterizado. Os nós folha de um índice clusterizdo contêm as páginas de dados.

Um índice não clusterizado é um tipo de índice no qual a ordem lógica do índice não corresponde à ordem física armazenada das linhas no disco. Os nós folha de um índice não clusterizado não consistem nas páginas de dados. Em vez disso, os nós folha contêm linhas de índice.

Ao criar um índice clusterizado ou não clusterizado, você pode definir a opção fill factor(fator de preenchimento) para especificar como o SQL Server completo deve criar cada página de índice. Quando não houver espaço livre para inserir nova linha na página de índice, o SQL Server criará uma nova página de índice e transferirá algumas linhas da página anterior para a nova. Essa operação é chamada de Page Split (divisão de página). Você pode reduzir o número de divisões de página(page split), definindo a opção de fator de preenchimento apropriada para reservar espaço livre em cada página do índice.

O fator de preenchimento (fill factor) é um valor de 1 a 100 que especifica a porcentagem da página de índice a ser deixada em branco. Quanto maior o número maior é o espaço de disco não usado mas no caso de inserts na tabela vai gerar muito menos 'pages split' que com um fill-factor menor.

O valor padrão para o fator de preenchimento é 0. Ele é tratado de forma semelhante a um valor de fator de preenchimento de 100, a diferença é que o SQL Server deixa algum espaço no nível superior da árvore de índice para FILLFACTOR = 0. A porcentagem do fator de preenchimento é usada apenas no momento em que o índice é criado.

Um índice de cobertura (covering index) é um índice, que inclui todas as colunas mencionadas na consulta, ou seja, atende a solicitação. Portanto, o índice contém os dados que você está procurando e o SQL Server não precisa procurar os dados reais na tabela.

Quando você executa muitas operações de modificação de dados (instruções INSERT, UPDATE ou DELETE), ocorre a fragmentação da tabela. Se você deseja determinar o nível de fragmentação, pode executar o comando :
DBCC SHOWCONTIG.

Dicas de otimização:

1 - Todo índice aumenta o tempo necessário para executar INSERTS, UPDATES e DELETES, portanto, o número de índices não deve ser muito. Tente usar no máximo 4-5 índices em uma tabela, não mais. Se você tiver tabela somente leitura, o número de índices poderá ser aumentado.

2 - Mantenha seus índices o mais seletivos possível. Isso reduz o tamanho do índice e reduz o número de leituras necessárias para ler o índice.

3 - Tente criar índices em colunas que tenham valores inteiros em vez de valores de caracteres.

4 - Se você criar um índice composto (várias colunas), a ordem das colunas na chave é muito importante.
Tente ordenar as colunas na chave para aumentar a seletividade, com as colunas mais seletivas à esquerda da chave.

5 - Se você deseja unir várias tabelas, tente criar chaves inteiras substitutas para essa finalidade e crie índices em suas colunas.

6 - Crie chave primária inteira substituta (identidade por exemplo) se sua tabela não possuir muitas operações de inserção.

7 - Os índices clusterizados são mais preferíveis que os não clusterizados, se você precisar selecionar por um intervalo de valores ou classificar os resultados configurados com GROUP BY ou ORDER BY.

8 - Se o seu aplicativo estiver executando a mesma consulta repetidamente na mesma tabela, considere criar um índice de cobertura na tabela.

9 - Você pode usar o Assistente SQL Server Profiler para "Identificar Varreduras de Tabelas Grandes" para determinar quais tabelas no seu banco de dados podem precisar de índices. Esse rastreamento mostrará quais tabelas estão sendo varridas por consultas em vez de usar um índice.

10 - Você pode usar Stored Procedures (procedimento armazenado) não documentado sp_MSforeachtable para recriar todos os índices em seu banco de dados. Tente agendá-lo para execução durante o tempo ocioso da CPU e períodos de produção lentos.

sp_MSforeachtable @ command1 = "print '?' DBCC DBREINDEX ('?') "

---

A maioria dos administradores de banco de dados está familiarizada com os possíveis benefícios de desempenho que podem ganhar através do uso criterioso de índices em tabelas de banco de dados. Os índices permitem acelerar o desempenho da consulta em colunas comumente usadas e melhorar a velocidade geral de processamento do seu banco de dados.

O Microsoft SQL Server oferece suporte a dois tipos de índices:

Os índices agrupados (clusterizados) que definem a classificação física das linhas de uma tabela de banco de dados na mídia de armazenamento. Por esse motivo, cada tabela de banco de dados pode ter apenas um índice em cluster.

Se uma restrição PRIMARY KEY for criada para uma tabela de banco de dados e não existir atualmente nenhum índice clusterizado para essa tabela, o SQL Server criará automaticamente um índice clusterizado na chave primária.

Os índices não agrupados (non Clustered - Heap table ) em cluster são criados fora da tabela do banco de dados e contêm uma lista classificada de referências à própria tabela. O SQL Server 2000 suporta no máximo 249 índices não agrupados por tabela. No entanto, é importante ter em mente que os índices não agrupados desaceleram o processo de modificação e inserção de dados, por isso é importante manter o mínimo possível desse tipo de índice.

Uma das tarefas mais difíceis para os administradores de banco de dados é a seleção de colunas apropriadas para índices não agrupados em cluster. Você deve considerar a criação de índices não agrupados em cluster em quaisquer colunas frequentemente referenciadas nas cláusulas WHERE das instruções SQL.

Outros bons candidatos são colunas referenciadas pelas operações JOIN e GROUP BY.

Você também pode considerar a criação de índices não agrupados em cluster que abranjam todas as colunas usadas por determinadas consultas frequentemente emitidas. Essas consultas são chamadas de consultas cobertas"(covered queries) e apresentam excelentes ganhos de desempenho.

O SQL Server fornece um recurso maravilhoso, conhecido como Assistente de Ajuste de Índice (Index Tuning Wizard), que aprimora muito o processo de seleção de índice. Para usar essa ferramenta, primeiro use o SQL Profiler para capturar um rastreamento da atividade para a qual você deseja otimizar o desempenho.
Você pode executar o rastreamento por um longo período de tempo para capturar uma ampla gama de atividades.
Em seguida, usando o Enterprise Manager, inicie o Assistente para Ajuste de Índice e instrua-o a recomendar índices com base no rastreamento capturado. Ele não apenas sugere colunas adequadas para as consultas, mas também fornece uma estimativa do aumento de desempenho que você experimentará após fazer essas alterações.

Tutoriais do MS SQL Server - Notas sobre o MS SQL Server - Índices no SQL Server

Os índices são usados ​​por bancos de dados relacionais como o SQL Server para localizar dados rapidamente quando uma consulta é processada. Não é necessário modificar o código do aplicativo enquanto manipula índices.

Os índices operam "nos bastidores" em suporte ao mecanismo de banco de dados. No entanto, a criação do índice adequado pode aumentar drasticamente o desempenho de um aplicativo.

O mecanismo do SQL Server usa um índice da mesma maneira que um leitor usa um índice de livros. Por exemplo, uma maneira de encontrar todas as referências a instruções INSERT em um livro SQL seria começar na página um e digitalizar cada página do livro. Poderíamos marcar cada vez que encontrarmos a palavra INSERIR até chegar ao final do livro. Essa abordagem é bastante demorada e trabalhosa. Como alternativa, também podemos usar o índice na parte de trás do livro para encontrar um número de página para cada ocorrência das instruções INSERT. Essa abordagem produz os mesmos resultados acima, mas com uma enorme economia de tempo.

Quando um SQL Server não possui um índice a ser usado na pesquisa, o resultado é semelhante ao leitor que consulta todas as páginas de um livro para encontrar uma palavra: o mecanismo SQL precisa visitar todas as linhas de uma tabela. Na terminologia do banco de dados, chamamos esse comportamento de varredura de tabela ou apenas varredura.
Uma varredura de tabela nem sempre é um problema e às vezes é inevitável. No entanto, à medida que uma tabela cresce para milhares de linhas e depois milhões de linhas e além, as verificações se tornam correspondentemente mais lentas e mais caras.

Criar um índice

O comando exemplificado abaixo especifica o nome do índice (IDX_PrecoUnitario), o nome da tabela (Produtos) e a coluna a indexar (PrecoUnitario).
Vamos criar um índice na tabela Produtos do banco de dados Northwind.

CREATE ÍNDICE [IDX_PrecoUnitario] ON Produtos (PrecoUnitario)

Para verificar se o índice é criado, use o seguinte procedimento armazenado para ver uma lista de todos os índices na tabela Produtos:

EXEC sp_helpindex

Como funciona :

O banco de dados pega as colunas especificadas em um comando CREATE INDEX e classifica os valores em uma estrutura de dados especial conhecida como B-tree.
Uma estrutura em árvore B suporta pesquisas rápidas com uma quantidade mínima de leituras de disco, permitindo que o mecanismo de banco de dados encontre rapidamente os pontos de partida e parada da consulta que estamos usando.
Cada entrada de índice contém a chave de índice (PrecoUnitario).
Cada entrada também inclui uma referência (que aponta) para as linhas da tabela que compartilham esse valor específico e das quais podemos recuperar as informações necessárias.

Assim como o índice na parte de trás de um livro nos ajuda a encontrar palavras-chave rapidamente, o banco de dados pode restringir rapidamente o número de registros que deve examinar ao mínimo usando a lista classificada de valores de UnitPrice armazenados no índice. Evitamos uma varredura de tabela para buscar os resultados da consulta. Dado esse esboço de como os índices funcionam, vamos examinar alguns dos cenários em que os índices oferecem um benefício.

Aproveitando os índices

O mecanismo de banco de dados pode usar índices para aumentar o desempenho em várias consultas diferentes. Às vezes, essas melhorias de desempenho são dramáticas. Um recurso importante do SQL Server é um componente conhecido como otimizador de consulta. O trabalho do otimizador de consultas é encontrar o meio mais rápido e com menos recursos necessários para executar as consultas recebidas. Uma parte importante desse trabalho é selecionar o melhor índice ou índices para executar a tarefa.