SQL Server: Performance e Índices Mal Desenhados.
Boa performance é um requisito essencial no seu banco de dados. Seja em ambiente local (on-premises) ou na nuvem, bancos de dados são sempre recursos caros e esta é apenas uma razão adicional para se preocupar com performance.
Se o seu banco de dados está lento, seus clientes seguramente vão reclamar e, ao mesmo tempo, você estará jogando dinheiro fora.
Muitas vezes o problema é resolvido criando-se novos índices e garantindo que suas tabelas não tenham índices em excesso (veja mais neste link).
Mas o que fazer quando existem índices e mesmo assim eles parecem não causar nenhum efeito?
SQL Server: Como funcionam Índices Clusterizados
Índices clusterizados são muito úteis, mas é essencial saber como eles trabalham.
Quando se define um índice clusterizado, estamos definindo o modo como os dados serão gravados em disco. Os registros da tabela serão fisicamente gravados nas páginas de dados conforme a ordem especificada pelo(s) campo(s) que define(m) este índice.
Uma tabela só pode ter um único índice clusterizado, pelo simples fato de que ele não é um catálogo novo: ele é a própria tabela gravada na ordem desejada.
Sendo assim, quando se insere um novo registro nessa tabela, o mecanismo do SQL Server vai identificar o(s) valor(es) do(s) campo(s) indexado(s), definir em qual página de dados ele deve ser escrito e então iniciar a gravação.
Como funcionam Índices Não-Clusterizados
Um índice não-clusterizado é um novo catálogo que é criado contendo apenas os valores existentes no(s) campo(s) listado(s) nesse índice. Ele funciona de modo parecido com o índice remissivo de um livro, apontando o valor e a página de dados onde ele é encontrado. Não há problema se o valor existir em múltiplas páginas de dados. Todas serão listadas no índice.
Se a tabela tiver um índice clusterizado (e geralmente é importante que ele exista), todos os índices não-clusterizados serão criados com base nesse índice principal.
Requisitos dos Índices Clusterizados
Se você está acompanhando o raciocínio, já deve ter percebido a importância do índice cluster.
Ele precisa ser bem estudado para que a sequência de inserção dos registros possa ser mantida nas páginas de dados. A inserção de dados seria muito lenta se a cada novo registro fosse necessário ficar pesquisando em qual página de dados ele deve ser gravado.
Outro requisito importante é que o índice seja bastante seletivo (baixa cardinalidade). Lembre-se que todos os índices não-clusterizados que forem criados numa tabela vão se basear nessa característica para agilizar as suas consultas.
Por essa razão, muitas vezes se cria o índice cluster sobre o(s) campo(s) que definem uma chave alternativa da tabela. Muitas vezes se usa campo de identidade (IDENTITY/SEQUENCE), que nada mais é do que um valor numérico sequencial.
Quando os Índices Parecem Inúteis
Tempos atrás eu encontrei uma situação dessas num cliente.
Os índices não-clusterizados pareciam adequados, eram usados pelo otimizador de consultas… e o resultado era péssimo. É verdade que a tabela era muito grande, a principal daquela base de dados. Mas é exatamente para estas situações que criamos índices.
Algumas consultas rodavam em minutos e a aplicação rapidamente travava. A impressão que dava é que esta tabela estava funcionando sem nenhum índice.
E isso me chamou a atenção.
Sabendo que a qualidade e desempenho de um índice não-clusterizado depende da qualidade do índice cluster, verifiquei a definição desse último índice.
Ele havia sido criado sobre um campo com tipo de dados DATE (dd/mm/aaaa). Para uma tabela tão grande quanto aquela, significava dizer que havia centenas de milhares, se não milhões, de registros para cada data.
Em outras palavras, aquele índice clusterizado não servia para nada, não oferecia seletividade nenhuma. E obviamente isso se refletia no desempenho ruim dos índices não-clusterizados, que acabavam mapeando milhares de páginas de dados.
A solução, que exigiu uma janela de manutenção de várias horas, foi recriar a tabela definindo um índice clusterizado mais adequado. Em seguida foram recriados os mesmos índices não-clusterizados que haviam sido definidos inicialmente.
Com isso, as consultas que rodavam em quatro minutos ou mais, passaram a rodar em questão de segundos.
Observe que esta questão de seletividade do índice clusterizado é tão importante que ele é criado por default quando se define a chave primária da tabela. Neste cenário, portanto, cada valor do índice cluster seleciona um único registro.
SQL Server – Comentários Finais
Meu ponto aqui é mostrar que planejamento e atenção na definição das tabelas e índices do seu banco de dados são essenciais.
O segredo da boa operação do seu banco está nos detalhes e grande parte dos problemas do dia a dia podem ser evitados e/ou minimizados na fase de planejamento.
LEIA TAMBÉM
De 0 a 10, o quanto você recomendaria este artigo para um amigo?