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/S para retornar resultados de consultas e, com menos E/S, há uma pressão reduzida sobre os sistemas de E/S e de armazenamento. A redução da E/S permite até uma melhor utilização da memória. Tenha em mente a proporção de leitura/gravação de suas consultas.

Uma carga de trabalho de gravação pesada pode indicar que o custo de escrever linhas em índices extras não é muito benéfico. Uma exceção seria se a carga de trabalho executar 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 ou colunas extras adicionados a um índice existente. Seu objetivo deve ser sempre obter o máximo benefício do menor número de índices em suas tabelas.

Uma abordagem comum de ajuste de desempenho é a seguinte:

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

  • Considere eliminar índices não utilizados e duplicados, 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 imediatamente antes de as operações serem agendadas, para reduzir a sobrecarga de ter índices não utilizados em uma tabela.

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

  • Crie o(s) índice(s) 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 seus ambientes de produção e não produção, pois a quantidade de memória e o número de CPUs podem afetar seu plano de execução.

  • Depois de testar cuidadosamente, implemente as alterações no seu sistema de produção.

Verifique a ordem das colunas dos seus índices — a coluna principal orienta as estatísticas da coluna e geralmente determina se o otimizador escolherá o índice. Idealmente, a coluna principal será seletiva e usada na WHERE cláusula de muitas de suas consultas. Considere o uso de um processo de controle de alterações para controlar alterações que possam 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.

Finalmente, as colunas usadas para comparações de igualdade devem preceder as 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 do índice sejam pausadas ou ocorram em uma janela de tempo e sejam retomadas posteriormente. Um bom exemplo de onde usar operações de índice retomáveis é reduzir o impacto da manutenção do índice em um ambiente de produção ocupado. Em seguida, você pode executar operações de reconstrução durante uma janela de manutenção específica, proporcionando 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 é matar o processo de criação de índice. Em seguida, você tem que iniciar o processo de novo desde o início se o sistema reverter a sessão.

Com o índice retomável, você pode pausar a compilação e, em seguida, reiniciá-la mais tarde no ponto em que foi pausada.

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 declaração acima usa a PAUSE cláusula para interromper temporariamente a criação do índice on-line retomável.

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

Nota

O índice retomável só é suportado com operações online.