Quando se trata de otimizar o desempenho das suas consultas no SQL Server, o uso de índices apropriados é crucial. Os índices ajudam o otimizador de consultas a escolher a estratégia mais eficiente para recuperar dados, resultando em ganhos significativos de desempenho. Neste artigo, exploraremos diferentes cenários para entender como os índices são usados no SQL Server e determinar qual tipo de índice, clusterizado ou não clusterizado, é mais adequado para cada situação.

Vamos começar criando uma tabela de exemplo que podemos usar para nossos exemplos:

CREATE TABLE MEMBROS (
    numero_membro INT NOT NULL,
    numero_conta INT NOT NULL,
    SSN_membro INT NOT NULL,
    saldo_conta MONEY NOT NULL,
    nome_membro VARCHAR(30) NOT NULL,
    endereco_membro VARCHAR(60) NOT NULL
);

Recuperando uma Única Linha

Ao recuperar uma única linha da tabela “membros”, a escolha do índice depende se há um índice clusterizado ou não clusterizado na coluna “numero_membro”.

Se houver um índice clusterizado, o SQL Server percorrerá as páginas do índice para encontrar a página de dados que contém a linha desejada. Por outro lado, se houver um índice não clusterizado, o SQL Server usará o ID da linha para buscar a linha de dados.

Embora a diferença de desempenho entre os dois índices seja mínima nesse cenário, geralmente é recomendado criar um índice clusterizado ao acessar um intervalo de linhas.

Recuperando um Intervalo de Linhas

Ao recuperar um intervalo de linhas da tabela “membros”, a escolha do índice se torna mais significativa.

Se houver um índice clusterizado na coluna “numero_membro”, o SQL Server percorrerá o índice para encontrar a página de dados que contém o valor de chave mais baixo no intervalo. Em seguida, ele recuperará sequencialmente as linhas até atingir o valor de chave mais alto.

Por outro lado, se houver um índice não clusterizado na coluna “numero_membro”, o SQL Server usará o ID da linha para buscar a página de dados de cada linha no intervalo. Isso pode resultar em uma diferença significativa de desempenho, especialmente quando há um grande número de linhas para recuperar.

Portanto, é aconselhável criar um índice clusterizado ao acessar um intervalo de linhas para otimizar o desempenho.

Consultas Cobertas

Uma consulta coberta é aquela em que os itens na cláusula WHERE e na cláusula SELECT pertencem ao índice. Nesse caso, o SQL Server pode encontrar os resultados no nível folha do índice não clusterizado sem acessar as páginas de dados.

Se houver um índice não clusterizado composto nas colunas “numero_membro” e “saldo_conta”, o SQL Server pode percorrer o índice com base no “numero_membro” e recuperar as informações de “saldo_conta” diretamente do próprio índice. Isso elimina a necessidade de acessar as páginas de dados, resultando em uma eficiência melhorada.

Portanto, em alguns casos, um índice não clusterizado coberto pode ser mais eficiente do que um índice clusterizado equivalente.

Recuperando Linhas com Múltiplos Índices Não Clusterizados

Quando há vários índices não clusterizados em uma tabela, o SQL Server pode usar ambos os índices para recuperar as linhas desejadas. O otimizador de consultas examina ambos os índices e obtém os conjuntos de IDs de linha que correspondem à seleção fornecida. Em seguida, ele obtém a interseção de ambos os conjuntos para determinar o conjunto de resultados final.

Sem um índice clusterizado, o nível folha dos índices contém IDs de linha como ponteiros para as linhas. Usando a varredura do índice e as leituras lógicas, o SQL Server pode recuperar eficientemente as páginas de dados que satisfazem as condições da consulta.

No entanto, a decisão de usar os índices ou realizar uma varredura da tabela depende do número de linhas que satisfazem as condições e do custo geral da operação.

Conclusão

O otimizador de consultas no SQL Server determina a maneira mais eficiente de recuperar os resultados da consulta com base no custo da operação. Ao entender os diferentes cenários e o uso de índices, você pode otimizar o desempenho das suas consultas no SQL Server.

Referências:
Microsoft SQL Server 2000 Performance Optimization and Tuning Handbook por Ken England

Sinta-se à vontade para deixar quaisquer comentários ou perguntas abaixo.