Solução de problemas comuns de desempenho com índices de hash Memory-Optimized

Este tópico se concentrará em solucionar problemas e contornar questões comuns com índices de hash.

A pesquisa requer um subconjunto das colunas da chave do índice hash.

Questão: Os índices de hash exigem valores para todas as colunas de chave de índice para calcular o valor de hash e localizar as linhas correspondentes na tabela de hash. Portanto, se uma consulta incluir predicados de igualdade apenas para um subconjunto das chaves de índice na cláusula WHERE, o SQL Server não poderá usar uma busca de índice para localizar as linhas correspondentes aos predicados na cláusula WHERE.

Por outro lado, índices ordenados como os índices não clusterizados baseados em disco e os índices não clusterizados com otimização de memória dão suporte à busca de índice em um subconjunto das colunas de chave de índice, desde que sejam as colunas principais do índice.

Sintoma: Isso resulta em uma degradação de desempenho, pois o SQL Server precisa executar verificações de tabela completas em vez de uma busca de índice, que normalmente é uma operação mais rápida.

Como solucionar problemas: Além da degradação do desempenho, a inspeção dos planos de consulta mostrará uma varredura em vez de uma busca de índice. Se a consulta for bastante simples, a inspeção do texto da consulta e da definição de índice também mostrará se a pesquisa requer um subconjunto das colunas de chave de índice.

Considere a seguinte tabela e consulta:

CREATE TABLE [dbo].[od]  
(  
     o_id INT NOT NULL,  
     od_id INT NOT NULL,  
     p_id INT NOT NULL,  
     CONSTRAINT PK_od PRIMARY KEY NONCLUSTERED HASH (o_id, od_id) WITH (BUCKET_COUNT = 10000)  
)  
WITH (MEMORY_OPTIMIZED = ON)  
  
 SELECT p_id  
 FROM dbo.od  
 WHERE o_id=1  

A tabela tem um índice de hash nas duas colunas (o_id, od_id), enquanto a consulta tem um predicado de igualdade na coluna (o_id). Como a consulta tem predicados de igualdade em apenas um subconjunto das colunas de chave de índice, o SQL Server não pode executar uma operação de busca de índice usando PK_od; Em vez disso, o SQL Server precisa reverter para uma verificação de índice completa.

Soluções alternativas: Há várias soluções alternativas possíveis. Por exemplo:

• Recrie o índice como tipo não clusterizado em vez de hash não clusterizado. O índice não clusterizado com otimização de memória é ordenado e, portanto, o SQL Server pode executar uma busca de índice nas colunas de chave de índice líderes. A definição de chave primária resultante para o exemplo seria constraint PK_od primary key nonclustered.

• Altere a chave de índice atual para corresponder às colunas na cláusula WHERE.

• Adicione um novo índice de hash que corresponda às colunas na cláusula WHERE da consulta. No exemplo, a definição de tabela resultante seria a seguinte:

  CREATE TABLE dbo.od  
   ( o_id INT NOT NULL,  
   od_id INT NOT NULL,  
   p_id INT NOT NULL,  
  
   CONSTRAINT PK_od PRIMARY KEY   
   NONCLUSTERED HASH (o_id,od_id) WITH (BUCKET_COUNT=10000),  
  
   INDEX ix_o_id NONCLUSTERED HASH (o_id) WITH (BUCKET_COUNT=10000)  
  
   ) WITH (MEMORY_OPTIMIZED=ON)  
  

Observe que um índice de hash com otimização de memória não terá um desempenho ideal se houver muitas linhas duplicadas para um determinado valor de chave de índice: no exemplo, se o número de valores exclusivos para a coluna o_id for muito menor do que o número de linhas na tabela, não seria ideal adicionar um índice (o_id); em vez disso, alterar o tipo do índice PK_od de hash para não clusterizado seria a melhor solução. Para obter mais informações, consulte Determining the Correct Bucket Count for Hash Indexes.

Consulte Também

Índices nas tabelas Memory-Optimized