Solução de problemas de índices de hash para tabelas com otimização de memória

Aplica-se a:SQL ServerBanco de Dados SQL do AzureInstância Gerenciada de SQL do Azure

Pré-requisito

Informações de contexto importantes para entender este artigo estão disponíveis em:

• Índices para tabelas com otimização de memória
• Índices de hash para tabelas com otimização de memória

Números práticos

Ao criar um índice de hash para uma tabela com otimização de memória, o número de buckets deve ser especificado durante a criação. Na maioria dos casos, o ideal é que a contagem de buckets deve estar entre 1 e 2 vezes o número de valores distintos na chave de índice.

Entretanto, mesmo que o BUCKET_COUNT esteja moderadamente abaixo ou acima do intervalo preferencial, o desempenho do índice de hash provavelmente será tolerável ou aceitável. No mínimo, considere dar ao índice de hash um BUCKET_COUNT aproximadamente igual ao número total de linhas previsto para a tabela com otimização de memória.
Suponha que a tabela em crescimento tenha dois milhões de linhas, com previsão de aumentar dez vezes, chegando a 20 milhões de linhas. Comece com um número de buckets que seja dez vezes o número de linhas na tabela. Assim haverá espaço para uma quantidade maior de linhas.

• O ideal é aumentar a contagem de buckets quando a quantidade de linhas atingir a contagem inicial de buckets.
• Mesmo que a quantidade de linhas cresça e chegue a ser cinco vezes maior que a contagem de buckets, o desempenho ainda será bom na maioria das situações.

Suponha que um índice de hash tenha dez milhões de valores de chave distintos.

• Um número de buckets igual a dois milhões seria o mínimo aceitável. O grau de degradação de desempenho seria tolerável.

Há muitos valores duplicados no índice?

Se os valores de hash indexados tiverem uma alta taxa de duplicados, os buckets de hash terão cadeias mais longas.

Considere a mesma tabela SupportEvent do bloco de código da sintaxe T-SQL anterior. O código T-SQL a seguir demonstra como você pode encontrar e exibir a proporção entre todos os valores e os valores únicos:

-- Calculate ratio of:  Rows / Unique_Values.  
DECLARE @allValues float(8) = 0.0, @uniqueVals float(8) = 0.0;  
  
SELECT @allValues = Count(*) FROM SupportEvent;  
  
SELECT @uniqueVals = Count(*) FROM  
  (SELECT DISTINCT SupportEngineerName  
      FROM SupportEvent) as d;  
  
    -- If (All / Unique) >= 10.0, use a nonclustered index, not a hash.   
SELECT Cast((@allValues / @uniqueVals) as float) as [All_divby_Unique];  
go  

• Uma proporção de 10,0 ou superior significa que o hash seria um tipo ruim de índice. Em vez disso, considere o uso de um índice não clusterizado.

Solução de problemas relacionados ao número de compartimentos do índice de hash

Esta seção aborda como solucionar problemas relacionados ao número de buckets do seu índice de hash.

Monitore estatísticas de cadeias e compartimentos vazios

Você pode monitorar a integridade estatística dos índices de hash, executando o T-SQL SELECT a seguir. O SELECT usa a DMV (exibição de gerenciamento de dados) denominada sys.dm_db_xtp_hash_index_stats.

SELECT  
  QUOTENAME(SCHEMA_NAME(t.schema_id)) + N'.' + QUOTENAME(OBJECT_NAME(h.object_id)) as [table],   
  i.name                   as [index],   
  h.total_bucket_count,  
  h.empty_bucket_count,  
    
  FLOOR((  
    CAST(h.empty_bucket_count as float) /  
      h.total_bucket_count) * 100)  
                            as [empty_bucket_percent],  
  h.avg_chain_length,   
  h.max_chain_length  
FROM  
        sys.dm_db_xtp_hash_index_stats  as h   
  JOIN sys.indexes                     as i  
          ON h.object_id = i.object_id  
          AND h.index_id  = i.index_id  
JOIN sys.memory_optimized_tables_internal_attributes ia ON h.xtp_object_id=ia.xtp_object_id
JOIN sys.tables t on h.object_id=t.object_id
WHERE ia.type=1
ORDER BY [table], [index];  

Compare os resultados do SELECT com as seguintes recomendações estatísticas:

• Buckets vazios:
  • 33% é um bom valor de destino, mas geralmente um percentual maior é satisfatório (até 90%).
  • Quando o número de buckets é igual ao número de valores de chave distintos, aproximadamente 33% dos buckets estão vazios.
  • Um valor inferior a 10% é muito baixo.
• Cadeias dentro de compartimentos:
  • Um tamanho de cadeia médio de 1 será ideal caso não haja valores de chave de índice duplicados. Em geral, tamanhos de cadeia de até 10 são aceitáveis.
  • Quando o comprimento médio da cadeia é maior que dez e o percentual de buckets vazios é maior que 10%, os dados têm tantos duplicados que o índice de hash pode não ser o tipo mais apropriado.

Demonstração de cadeias e baldes vazios

O seguinte bloco de código T-SQL fornece uma maneira fácil de testar um SELECT * FROM sys.dm_db_xtp_hash_index_stats;. O bloco de código é concluído em 1 minuto. Estas estão as fases do seguinte bloco de código:

1. Cria uma tabela com otimização de memória com alguns índices de hash.
2. Preenche a tabela com milhares de linhas.
   a. Um operador de módulo é usado para configurar a taxa de valores duplicados na coluna StatusCode.
   b. O loop insere 262.144 linhas em aproximadamente 1 minuto.
3. Exibe uma mensagem solicitando que você execute a instrução SELECT anterior em sys.dm_db_xtp_hash_index_stats.

DROP TABLE IF EXISTS SalesOrder_Mem;  
go  
  
  
CREATE TABLE SalesOrder_Mem  
(  
  SalesOrderId   uniqueidentifier  NOT NULL  DEFAULT newid(),  
  OrderSequence  int               NOT NULL,  
  OrderDate      datetime2(3)      NOT NULL,  
  StatusCode     tinyint           NOT NULL,  
  
  PRIMARY KEY NONCLUSTERED  
      HASH (SalesOrderId)  WITH (BUCKET_COUNT = 262144),  
  
  INDEX ix_OrderSequence  
      HASH (OrderSequence) WITH (BUCKET_COUNT = 20000),  
  
  INDEX ix_StatusCode  
      HASH (StatusCode)    WITH (BUCKET_COUNT = 8),  
  
  INDEX ix_OrderDate       NONCLUSTERED (OrderDate DESC)  
)  
  WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA)  
go  
  
--------------------  
  
SET NOCOUNT ON;  
  
-- Same as PK bucket_count.  68 seconds to complete.  
DECLARE @i int = 262144;  
  
BEGIN TRANSACTION;  
  
WHILE @i > 0  
BEGIN  
  
  INSERT SalesOrder_Mem  
      (OrderSequence, OrderDate, StatusCode)  
    Values  
      (@i, GetUtcDate(), @i % 8);  -- Modulo technique.  
  
  SET @i -= 1;  
END  
COMMIT TRANSACTION;  
  
PRINT 'Next, you should query:  sys.dm_db_xtp_hash_index_stats .';  
go  

O loop INSERT anterior faz o seguinte:

• Insere valores exclusivos para o índice de chave primária e para ix_OrderSequence.
• Insere algumas centenas de milhares de linhas que representam apenas oito valores distintos de StatusCode. Portanto, há uma alta taxa de duplicação de valor no índice ix_StatusCode.

Para solucionar problemas quando o número de buckets não é ideal, examine a seguinte saída do SELECT de sys.dm_db_xtp_hash_index_stats. Para esses resultados, adicionamos WHERE Object_Name(h.object_id) = 'SalesOrder_Mem' à SELECT copiada da seção D.1.

Nossos SELECT resultados são exibidos após o código, divididos artificialmente em duas tabelas de resultados mais estreitas para melhor visualização.

• Estes são os resultados da contagem de buckets.

• Veja a seguir os resultados do tamanho da cadeia.

Vamos interpretar as tabelas de resultados anteriores para os três índices hash:

ix_StatusCode:

• 50% dos buckets estão vazios, o que é bom.
• Porém, o comprimento médio da cadeia é muito alto (65536).
  • Isso indica uma alta taxa de valores duplicados.
  • Portanto, usar um índice de hash não é apropriado nesse caso. Deve-se utilizar índice não clusterizado.

ix_OrderSequence:

• 0% dos compartimentos estão vazios, o que é um valor muito baixo.
• O comprimento médio da cadeia é 8, embora todos os valores nesse índice sejam exclusivos.
  • Portanto, o número de buckets deve ser aumentado para reduzir o comprimento médio da cadeia, aproximando-o de 2 ou 3.
• Como a chave de índice tem 262144 valores exclusivos, o número de buckets deve ser pelo menos 262144.
  • Caso seja esperado um futuro crescimento, o número de buckets deverá ser mais alto.

Índice de chave primária (PK_SalesOrd_...):

• 36% dos buckets estão vazios, o que é bom.
• O comprimento médio da cadeia é 1, o que também é bom. Nenhuma alteração é necessária.

Equilibrando os prós e contras

As cargas de trabalho OLTP concentram-se em linhas individuais. Geralmente, as verificações completas de tabelas não estão no caminho crítico de desempenho para cargas de trabalho OLTP. Portanto, a compensação que deve ser balanceada é entre quantidade de utilização de memória versus desempenho de testes de equidade e operações de inserção.

Se a utilização de memória for a maior preocupação:

• Escolha uma contagem de buckets próxima ao número de valores exclusivos de chave de índice.
• A contagem de buckets não deve ser significativamente menor que o número de valores únicos de chave de índice, pois isso afeta a maioria das operações DML assim como o tempo necessário para recuperar o banco de dados após a reinicialização do servidor.

Se o desempenho de testes de igualdade for a maior preocupação:

• Será apropriado um número de buckets maior, com duas ou três vezes o número de valores de índice exclusivos. Uma contagem maior significa:
  • Recuperações mais rápidas ao procurar um valor específico.
  • Um aumento de utilização de memória.
  • Um aumento do tempo necessário para uma verificação completa do índice de hash.

Leituras adicionais

Índices de hash para tabelas com otimização de memória
Índices não clusterizados para tabelas com otimização de memória