Banco de Dados

20 set, 2016

Índices e o excesso de coisa boa – Parte 02: objetos sem uso

Publicidade

A dose certa diferencia um veneno de um remédio – Paracelso

Índices devem ser escolhidos conforme a expectativa de utilização da tabela ou, melhor ainda, conforme as estatísticas reais das operações da tabela. Projetar índices antes de a base entrar em operação é, para quaisquer fins práticos, um jogo de adivinhação.

Por isso é essencial que a estratégia inicial de indexação seja reavaliada logo depois que o banco subir para produção. A ideia é analisar as estatísticas de acesso aos índices existentes para identificar aqueles que são pouco utilizados, de modo que sejam substituídos por índices mais eficientes. Esse é um processo que deve fazer parte da rotina de manutenção de todo banco de dados.

A seguir, apresento algumas considerações sobre essa questão, além de um script para identificação dos índices que são pouco utilizados.

Estatísticas

O otimizador de consultas é uma ferramenta essencial no seu banco de dados. Ele define qual é a melhor forma de executar uma consulta, escolhendo qual o processo mais adequado para busca dos dados e qual índice deve ser usado.

Porém, o otimizador funciona baseado nas estatísticas do banco. Sem estatísticas atualizadas, os resultados do otimizador não serão confiáveis.

Por isso é muito importante que seu banco de dados esteja configurado para coletar e atualizar automaticamente as estatísticas de operação. Isso garante o funcionamento adequado do otimizador de consultas e, por consequência, garante também que seus índices serão usados de forma apropriada.

Essa é a razão pela qual não é eficiente planejar a criação de índices sem usar dados de produção: as estatísticas de acesso nas bases de desenvolvimento ou de teste não refletem a realidade da operação.

Quem são os índices ineficientes?

Para falarmos de índices ineficientes, precisamos adotar critérios, mas sempre vai existir certo grau de subjetividade nessa escolha.

O caso mais evidente de ineficiência são os índices raramente utilizados. Ou, mais especificamente, aqueles que são proporcionalmente pouco usados em relação à sobrecarga de trabalho que eles geram. Lembre-se de que o catálogo de cada índice precisa ser atualizado a cada INSERT, UPDATE ou DELETE que acontece na tabela correspondente e, portanto, um índice sempre causa uma sobrecarga.

Mas o que significa “pouco usado”? Esse critério precisa ser definido caso a caso, considerando o nível de atividade de cada índice. Um índice que sofreu 10 mil atualizações e teve 500 acessos no mesmo período (o que é um número significativo), apresentou uma razão entre acessos e sobrecarga de 5% (500/10.000). Outro índice que teve 1.000 acessos, mas teve um milhão de atualizações, apresentou uma razão muito menor de 0,1%. Ou seja, apesar de o índice ser bastante usado (1.000 acessos), é preciso avaliar se vale a pena mantê-lo, já que ele exige uma sobrecarga de trabalho muito alta (um milhão de atualizações).

Identificando índices pouco usados

No SQL Server, temos todas essas informações sobre acesso e atualização de índices na DMV sys.dm_db_index_usage_stats.

Essa DMV informa estatísticas de acessos e atualizações de cada índice, incluindo as seguintes colunas:

  • user_updates: quantas vezes o índice precisou ser atualizado devido a operações de INSERT, DELETE ou UPDATE;
  • user_seeks: quantas vezes ele foi usado em operações do tipo INDEX SEEK, o tipo mais eficiente de acesso aos índices;
  • user_scans: quantas vezes o índice foi usado em operações INDEX SCAN;
  • user_lookups: quantas vezes foi usado em operações KEY LOOKUP, o tipo de acesso menos eficiente de todos.

No script a seguir, eu apresento essas informações e acrescento o total de acessos (IxTotal), que é a soma das três estatísticas de acesso, e também a razão entre acessos e atualizações (coluna “IxRatio”). Como dificilmente temos estatísticas de uso em bases demo, precisei usar um caso real para ilustrar o resultado obtido. Naturalmente, precisei omitir a fonte desses números, mas o resultado apresentado serve apenas para fins didáticos.

tabela

Analisando os resultados da tabela acima, eu recomendaria a exclusão de todos os índices, com exceção do “Indice2”, que comentarei a seguir.

Todos os outros índices são muito pouco utilizados, chegando ao caso crítico do “Indice1”, que foi criado sobre uma tabela de 60 mil registros, atualizado mais de 27 mil vezes e nunca utilizado! Lamentavelmente, isso não é raro em bases produtivas, e é por isso que é necessário que esse procedimento faça parte do processo de manutenção do banco.

Sobre o “Indice2”, eu recomendaria mantê-lo, porque apesar de ele ter um nível baixo de utilização (razão de apenas 4%), ele sempre é usado em operações de INDEX SEEK, que é o caso ideal de acesso a qualquer índice.

Com isso, encerramos mais este artigo. No terceiro e último artigo desta série, comentarei sobre outro caso de índices ineficientes: índices que ocupam muito espaço em disco.

Confira a primeira parte aqui.

Leituras Sugeridas

  1. SQL Server 2016 – Statistics por Microsoft.
  2. MSDN: sys.dm_db_index_usage_stats  por Microsoft.