Solução de problemas de índices de hash para tabelas Memory-Optimized

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

Pré-requisito

Informações contextuais importantes para a compreensão deste artigo estão disponíveis em:

• Índices para Tabelas Memory-Optimized
• índices de hash para tabelas Memory-Optimized

Números práticos

Ao criar um índice de hash para uma tabela com otimização de memória, o número de buckets precisa ser especificado no momento da criação. Na maioria dos casos, o número de compartimentos seria, idealmente, entre 1 e 2 vezes o número de valores distintos na chave de índice.

No entanto, mesmo que o BUCKET_COUNT esteja moderadamente abaixo ou acima do intervalo preferido, o desempenho do seu índice de hash provavelmente será tolerável ou aceitável. No mínimo, considere dar ao seu índice de hash uma BUCKET_COUNT aproximadamente igual ao número de linhas que prevê que a sua tabela otimizada para memória poderá crescer.
Suponha que sua tabela crescente tenha 2.000.000 linhas, mas a previsão é que ela cresça 10 vezes para 20.000.000 linhas. Comece com uma contagem de buckets 10 vezes o número de linhas na tabela. Isso dá espaço para uma quantidade maior de linhas.

• Idealmente, você deveria aumentar o número de compartimentos quando a quantidade de linhas atingir o número inicial de compartimentos.
• Mesmo que a quantidade de linhas cresça para cinco vezes maior do que a contagem de baldes, o desempenho ainda é bom na maioria das situações.

Suponha que um índice de hash tenha 10.000.000 de valores de chave distintos.

• Uma contagem de buckets de 2.000.000 seria o mínimo aceitável. O grau de degradação do desempenho pode ser tolerável.

Demasiados valores duplicados no índice?

Se os valores indexados por hash tiverem uma alta taxa de duplicatas, os hash buckets sofrerão cadeias mais longas.

Suponha que você tenha a mesma tabela SupportEvent do bloco de código de sintaxe T-SQL anterior. A seguir, o código T-SQL demonstra como é possível encontrar e exibir a proporção de todos os valores de em relação aos valores exclusivos de.

-- 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 um hash seria um tipo pobre de índice. Em vez disso, considere o uso de um índice não clusterizado,

Resolução de problemas na contagem de compartimentos do índice de hash

Esta seção discute como solucionar problemas na contagem de buckets para seu índice de hash.

Monitore estatísticas para cadeias e baldes vazios

Você pode monitorar a integridade estatística de seus índices de hash executando o seguinte T-SQL SELECT. O SELECT usa a visualização de gerenciamento de dados (DMV) chamada 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 orientações estatísticas:

• Baldes vazios:
  • 33% é um bom valor-alvo, mas uma percentagem maior (mesmo 90%) é geralmente boa.
  • Quando a contagem de buckets é igual ao número de valores de chave distintos, aproximadamente 33% dos buckets estão vazios.
  • Um valor inferior a 10% é demasiado baixo.
• Correntes dentro de baldes:
  • Um comprimento médio de cadeia de 1 é ideal no caso de não haver valores de chave de índice duplicados. Comprimentos de corrente até 10 são geralmente aceitáveis.
  • Se o comprimento médio da cadeia for maior que 10 e a porcentagem do bucket vazio for maior que 10%, os dados terão tantas duplicatas que um índice de hash pode não ser o tipo mais apropriado.

Demonstração de correntes e baldes vazios

O bloco de código T-SQL a seguir oferece 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. Aqui estão as fases do seguinte bloco de código:

1. Cria uma tabela com otimização de memória que tem 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. imprime uma mensagem solicitando que executes o SELECT anterior de 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 de 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 para StatusCode. Portanto, há uma alta taxa de duplicação de valor no índice ix_StatusCode.

Para solucionar problemas quando a contagem de buckets não seja ideal, examine a seguinte saída do SELECT de sys.dm_db_xtp_hash_index_stats. Para estes resultados, adicionámos WHERE Object_Name(h.object_id) = 'SalesOrder_Mem' ao SELECT copiado da secção D.1.

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

• Aqui estão os resultados para contagem de bucket.

• Seguem-se os resultados para comprimento da cadeia.

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

ix_StatusCode:

• 50 dos baldes% estão vazios, o que é bom.
• No entanto, o comprimento médio da cadeia é muito alto em 65536.
  • Isso indica uma alta taxa de valores duplicados.
  • Portanto, usar um índice de hash não é apropriado neste caso. Em vez disso, deve ser utilizado um índice não agrupado.

ix_OrderSequence:

• 0% dos baldes encontram-se vazios, o que é um valor demasiado baixo.
• O comprimento médio da cadeia é 8, embora todos os valores neste índice sejam únicos.
  • Portanto, a contagem de baldes deve ser aumentada, para reduzir o comprimento médio da cadeia para ficar mais próximo de 2 ou 3.
• Como a chave de índice possui 262144 valores únicos, a contagem de buckets deve ser, no mínimo, 262144.
  • Se houver crescimento futuro esperado, a contagem de baldes deve ser maior.

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

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

Equilibrando o compromisso

As cargas de trabalho OLTP concentram-se em linhas individuais. As verificações de tabela completa geralmente não estão no caminho crítico de desempenho para cargas de trabalho OLTP. Portanto, a compensação que você deve equilibrar é entre quantidade de utilização de memória versus desempenho de testes de igualdade e operações de inserção.

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

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

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

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

Leitura adicional

índices de hash para tabelas Memory-Optimized
Índices Não Agrupados para Tabelas Memory-Optimized