Ajustar e manter índices

• 6 minutos

O método mais comum (e mais eficaz) para ajustar consultas T-SQL é avaliar e ajustar sua estratégia de indexação. Os bancos de dados indexados corretamente executam menos E/Ss para retornar resultados da consulta e, com menos E/Ss, há uma redução da pressão nos sistemas de armazenamento e de E/S. Reduzir a E/S ainda permite uma melhor utilização de memória. Lembre-se da proporção de leitura/gravação de suas consultas.

Uma carga de trabalho de gravação pesada pode indicar que o custo de gravar linhas em índices adicionais não traz muitos benefícios. Uma exceção seria se a carga de trabalho executasse principalmente atualizações que também precisam fazer operações de pesquisa. As operações de atualização que fazem pesquisas podem se beneficiar de índices adicionais ou colunas adicionadas a um índice existente. Seu objetivo deve ser sempre obter o máximo proveito do menor número de índices em suas tabelas.

Uma abordagem de ajuste de desempenho comum é a seguinte:

• Avalie o uso do índice existente usando sys.dm_db_index_operational_stats e sys.dm_db_index_usage_stats.

• Considere a eliminação de índices duplicados e não utilizados, mas isso deve ser feito com cuidado. Alguns índices só podem ser usados durante operações mensais/trimestrais/anuais e podem ser importantes para esses processos. Você também pode considerar a criação de índices para dar suporte a essas operações um pouco antes do agendamento delas, para reduzir a sobrecarga de ter índices não utilizados em uma tabela.

• Examine e avalie consultas caras do Repositório de Consultas ou da captura de Eventos Estendidos e trabalhe em índices criados manualmente para atender melhor a essas consultas.

• Crie os índices em um ambiente que não seja de produção, teste a execução e o desempenho da consulta e observe as alterações de desempenho. É importante observar quaisquer diferenças de hardware entre os ambientes de produção e de não produção, pois a quantidade de memória e o número de CPUs podem afetar o plano de execução.

• Após testar cuidadosamente, implemente as alterações em seu sistema de produção.

Verifique a ordem das colunas de seus índices – a coluna principal impulsiona as estatísticas de coluna e geralmente determina se o otimizador escolherá o índice. Idealmente, a coluna principal será seletiva e usada na cláusula WHERE de muitas de suas consultas. Considere o uso de um processo de controle de alterações para monitorar as alterações que podem afetar o desempenho do aplicativo. Antes de descartar um índice, salve o código no controle do código-fonte, para que o índice possa ser recriado rapidamente se uma consulta executada com pouca frequência exigir que o índice tenha um bom desempenho.

Por fim, as colunas usadas para comparações de igualdade devem preceder colunas usadas para comparações de desigualdade e as colunas com maior seletividade devem preceder colunas com menos valores distintos.

Índice retomável

O índice retomável permite que as operações de manutenção de índice sejam pausadas ou ocorram em determinada janela de tempo e sejam retomadas mais tarde. Um bom exemplo de onde usar operações de índice retomáveis é reduzir o impacto da manutenção de índice em um ambiente de produção ocupado. Você pode executar operações de recompilação durante uma janela de manutenção específica, fornecendo mais controle sobre o processo.

Além disso, a criação de um índice para uma tabela grande pode afetar negativamente o desempenho de todo o sistema de banco de dados. A única maneira de corrigir esse problema em versões anteriores ao SQL Server 2019 será eliminar o processo de criação do índice. Você precisará iniciar o processo desde o início se o sistema reverter a sessão.

Com o índice retomável, você pode pausar o build e reiniciá-lo mais tarde no ponto em que ele foi pausado.

O exemplo a seguir mostra como criar um índice retomável:

-- Creates a nonclustered index for the Customer table

CREATE INDEX IX_Customer_PersonID_ModifiedDate 
    ON Sales.Customer (PersonID, StoreID, TerritoryID, AccountNumber, ModifiedDate)
WITH (RESUMABLE=ON, ONLINE=ON)
GO

Em uma janela de consulta, pause a operação de índice:

ALTER INDEX IX_Customer_PersonID_ModifiedDate ON Sales.Customer PAUSE
GO

A instrução acima usa a cláusula PAUSE para interromper temporariamente a criação do índice online retomável.

Você pode verificar o status de execução atual de um índice online retomável consultando a exibição do sistema sys.index_resumable_operations.