O ajuste de desempenho é uma das funções mais fundamentais e complexas quando o assunto é banco de dados. Para garantir sua eficiência, a adoção de algumas medidas é importante. Descubra 11 práticas recomendadas de índice do SQL Server para melhorar o ajuste de desempenho:
Práticas recomendadas de índice do SQL Server
1. Compreenda o impacto do design do banco de dados nos índices do SQL Server
Os requisitos de indexação alternam entre bancos de dados de processamento de transações online (OLTP) e de processamento analítico online (OLAP).
Em ambientes OLTP, onde as operações de leitura e gravação são frequentes, com inserção de novos dados e modificações nos existentes, o uso de consultas de manipulação de dados (Insert, Update, Delete) e instruções Select é comum. Nesses cenários, é mais recomendado criar índices na coluna "Selecionado" da tabela. Desta forma, você evita vários índices que podem impactar negativamente o desempenho e sobrecarregar os recursos do sistema. Outra prática recomendada é criar o mínimo de índices necessário para atender aos requisitos de indexação.
Já em ambientes OLAP, onde predominam instruções Select para recuperação de dados analíticos, é viável inserir mais índices, inclusive com múltiplas colunas-chave por índice. A aplicação de índices columnstore também é possível para otimizar a recuperação de dados em consultas de data warehouse.
2. Crie índices para seus requisitos de carga de trabalho
Ao fazer uma nova tabela no banco de dados, evite incluir índices sem embasamento. Desenvolvedores, por vezes, incluem índices clusterizados e não clusterizados sem estudar as necessidades do otimizador de consulta.
Para garantir eficiência, o recomendado é analisar profundamente a carga de trabalho, incluindo procedimentos armazenados, funções e consultas ad hoc, utilizando ferramentas como SQL Profiler, eventos estendidos e visualizações de gerenciamento dinâmico. Com base nessas informações, é possível criar índices estrategicamente para otimizar consultas com uso intensivo de recursos, resultando em um desempenho aprimorado do banco de dados.
3. Monte índices para as consultas mais usadas
Reunir cargas de trabalho para as consultas mais frequentes no sistema é um ponto chave na sua estratégia. Ao montar índices personalizados para essas consultas, é possível reduzir a pressão sobre o sistema.
4. Aplique as práticas recomendadas para colunas de chave de índice do SQL Server
Confira alguns pontos sobre colunas-chave de índice:
Não é possível utilizar colunas com texto, imagem, ntext, varchar(max), nvarchar(max) e varbinary(max) nas colunas-chave do índice.
O recomendado é usar um tipo de dados inteiro na coluna-chave do índice. Isso porque, demanda pouco espaço e funciona de forma eficiente. Sendo assim, a coluna de chave primária deve ser criada, geralmente em um tipo de dados inteiro.
Só é permitido usar o tipo de dados XML em um índice XML.
É preciso considerar a criação de uma chave primária para a coluna com valores exclusivos. Caso uma tabela não tiver colunas de valor exclusivo, é viável definir uma coluna de identidade para um tipo de dados inteiro. Uma chave primária também cria um índice clusterizado para a distribuição de linhas.
Ter uma coluna com os valores Unique e Not NULL como uma candidata útil à chave de índice é possível.
O recomendado é montar o índice baseado nos predicados da cláusula Where.
Uma as tabelas de maneira que diminua o número de linhas para o restante da consulta. Isso ajudará o otimizador de consultas a preparar o plano de execução com recursos mínimos do sistema.
Se utilizar múltiplas colunas para uma chave de índice, também será fundamental considerar a posição delas na chave de índice.
Por último, considere o uso de colunas incluídas em seus índices.
5. Analise a distribuição de dados das colunas de índice do SQL Server
A análise da distribuição de dados nas colunas-chave de índice do SQL Server deve fazer parte do processo. Colunas com valores não exclusivos têm o podem atrasar a recuperação de dados, o que acaba resultando em transações prolongadas. Utilizar histograma presente nas estatísticas do banco de dados é uma forma de analisar a distribuição de dados.
6. Utilize a ordem de classificação dos dados
Outro ponto para levar em consideração são os requisitos de classificação de dados em suas consultas e índices. A forma padrão da classificação do SQL Server é por meio dos dados em ordem crescente em um índice.
7. Use chaves estrangeiras para seu índice do SQL Server
Monte um índice nas colunas de chaves estrangeiras. A criação de um índice clusterizado na chave estrangeira é indicada para melhorar o desempenho da consulta.
8. Dê atenção às considerações de armazenamento de índice do SQL Server
Leve em consideração o armazenamento de índices no SQL Server para alocar um grupo de arquivos separado para índices em um disco distinto para otimizar I/O. Além de poder usar o particionamento de tabelas, distribuindo dados em diferentes discos, associado a índices particionados, aprimora o acesso simultâneo aos dados.
Ao criar ou reconstruir um índice, a configuração do FILLFACTOR é crucial, definindo espaço livre para futuras inserções de dados. Para conjuntos de dados estáticos, um alto valor de FILLFACTOR é apropriado, enquanto para dados dinâmicos. O recomendado é deixar espaço adequado para novas inserções. Essas práticas são fundamentais para otimizar o desempenho e a eficiência de armazenamento no SQL Server.
9. Localize índices ausentes
Em alguns casos, você obtém informações sobre um índice ausente do SQL Server no plano de execução da consulta. Também é possível executar as visualizações de gerenciamento dinâmico para encontrar esses índices ausentes. O que não se pode fazer é criar esses índices sem embasamento. É somente uma indicação do otimizador de consulta, mas não leva em consideração o índice existente ou os requisitos da sua carga de trabalho. Também pode adicionar múltiplas colunas na definição do índice, sendo assim, revise estas sugestões antes de implementá-lo.
10. Tenha o hábito de montar um índice clusterizado antes de um índice não clusterizado
Uma dica padrão é: construa um índice clusterizado antes de construir índices não clusterizados. Caso uma tabela não tiver um índice, um índice não clusterizado consiste em identificadores de linha. Após montar um índice clusterizado, o SQL Server necessita construir esses índices não clusterizados para que possam direcionar para a chave do índice clusterizado em vez dos identificadores de linha.
11. Acompanhe a manutenção do índice e atualize as estatísticas
Confira algumas das áreas de manutenção a serem monitoradas quando se trata de índices do SQL Server.
Retire a fragmentação do índice: o recomendado é reconstruir o índice se ele tiver uma fragmentação maior que 30% e reorganizá-lo se tiver menos de 30% de fragmentação;
Retire índices não utilizados: lembre-se que um índice não utilizado também consome armazenamento e aumenta a sobrecarga de manutenção;
Atualize estatísticas: O otimizador de consulta poderá preparar um plano de execução incorreto se as estatísticas do índice não forem atualizadas. Portanto, lembre-se sempre de atualizá-las.