Como funciona a estrutura interna de Índices no SQL Server?

O artigo visa abordar a estrutura de índices voltado para o banco de dados SQL Server.

Os índices são peças fundamentais que ajudam a melhorar e a otimizar consultas no banco de dados. Quando são criados com a análise e da forma correta, eles trazem um enorme ganho envolvendo I/O Wait (leitura e escrita no discos) e processamento de CPU.

Em uma estrutura de banco de dados, um índice é uma estrutura em disco que é ligada a uma tabela ou view. A sua principal função é otimizar a recuperação de tuplas (linhas) em um banco de dados.

Ao criar um índice, as chaves de uma ou mais colunas são armazenados em forma de arvore onde o SQL Server utiliza para recuperar informações da chaves inseridas de forma mais rápido. Isso reduz drasticamente o esforço de CPU e I/O Wait ao tentar recuperar uma consulta no servidor.

As arvores são estruturadas de forma ordenada afim de facilitar as buscas e possui 3 níveis.

Nível Raiz
Nível intermediário
Nível Folha

Irei explicar resumidamente cada nível.

O nível Raiz e intermediário pegam o primeiro valor de cada página e os ponteiros do nível abaixo.
O nível intermediário contém as árvores de índices existentes.
O Nível folha contém os dados em uma estrutura encadeada que liga as páginas de dados com ponteiros. Os ponteiros ligam uma página anterior para uma próxima página e assim por diante. Simplificadamente, o nível folha é onde fica o próprio dado ordenado.

Segue a imagem de exemplo abaixo:

indice

Diferença entre Índice Clustered x NonClustered

Muitos analistas de banco de dados costumam dizer que a diferença de um índice Clustered e um índice NonClustered é a ordenação, ou seja, índices clustered são ordenados e NonClustered não são ordenados, o que é uma inverdade, por que os índices NonClustered também são ordenados.

Então quais são as diferenças?

1. Indíces Clustered:

Apenas 1 índice Clustered por tabela pode ser criado, pelo simples fato dele possuir todas as informações de colunas.
Geralmente são criado juntamente com a Primary Key.
Quantidade de espaço utilizado para criação do índice clustered é maior.

2. Índices NonClustered:

Podem ser criados até 999 índices nonClustered “se você tiver coragem”
É uma estrutura ordenada em parte, ou seja, conterá apenas as informações para otimizar a consulta da coluna desejada.

Existe apenas uma observação extremamente importante que muitas pessoas não sabem: Quando você cria um índice NonClustered, o próprio SQL Server dá um jeito de inserir no momento da criação, um apontamento para o índice Clustered. Confuso não é? Irei explicar.

Imagine que você faça uma consulta que utiliza um objeto (índice Nonclustered) criado. Dependendo do seu filtro ou da estrutura da consulta, a informação que você deseja retornar não irá utilizar aquele índice NonClustered, então, ao invés do SQL Server começar novamente toda uma varredura na tabela, ele utiliza o ponteiro criado para o índice Clustered, afim de achar a informação o mais rápido possível. Legal não é?

Recomendações

Os índices utilizam bastante espaço em disco, então se não for criado com cuidado, podem prejudicar ao invés de ajudar. Traduzindo: Você que é desenvolvedor, e até mesmo um DBA, não saia criando índice para tudo, analise com calma e crie conscientemente.
Na criação de índice para uma tabela muito grande, utilize a opção de índice (ONLINE = ON). A opção permite que outras atividades continuem sendo executadas no momento em que o índice é criado.
Crie índices utilizados para valores lógicos que utilizam joins, entretanto, evite adicionar tantas colunas que podem ser desnecessárias.
O cenário ideal para criação de índices são colunas que possuem o tipo de dados “INT” (inteiros), colunas exclusivas ou não nulas.
É função do DBA manter uma rotina de rebuild ou reorganize dos índices. O que é isso? A cada inserção em uma tabela que contém índice, toda a “árvore” do índice precisa ser reordenada, certo? Com o tempo isso vai aumentando o tempo de fragmentação do índice, que prejudica na performance e desempenho da utilização dos mesmos. Por isso é necessário fazer um rebuild ou reorganize. Digamos que o rebuild vai reduzir toda essa fragmentação de tempo em tempos.