Risoluzione dei problemi per gli indici hash per tabelle ottimizzate per la memoriaTroubleshooting Hash Indexes for Memory-Optimized Tables

QUESTO ARGOMENTO SI APPLICA A: sìSQL ServersìDatabase SQL di AzurenoAzure SQL Data Warehouse noParallel Data Warehouse THIS TOPIC APPLIES TO: yesSQL ServeryesAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

PrerequisitiPrerequisite

Informazioni sul contesto importanti per la comprensione di questo articolo sono disponibili nell'articolo:Important context information for understanding this article is available at:

Numeri praticiPractical numbers

Quando si crea un indice hash per una tabella ottimizzata per la memoria, il numero di bucket deve essere specificato al momento della creazione.When creating a hash index for a memory-optimized table, the number of buckets needs to be specified at create time. Nella maggior parte dei casi, il numero di bucket dovrebbe essere in teoria impostato su un valore compreso tra una e due volte il numero di valori distinct della chiave di indice.In most cases the bucket count would ideally be between 1 and 2 times the number of distinct values in the index key.

Tuttavia, anche se il valore BUCKET_COUNT è moderatamente inferiore o superiore all'intervallo preferito, è probabile che le prestazioni dell'indice hash siano tollerabili o accettabili.However, even if the BUCKET_COUNT is moderately below or above the preferred range, the performance of your hash index is likely to be tolerable or acceptable. Come minimo, valutare la possibilità di assegnare all'indice hash un valore BUCKET_COUNT quasi uguale al numero di righe che si prevede di raggiungere nella tabella ottimizzata per la memoria.At minimum, consider giving your hash index a BUCKET_COUNT roughly equal to the number of rows you predict your memory-optimized table will grow to have.
Ad esempio, se la tabella espandibile contiene 2.000.000 righe, ma si prevede che crescerà di 10 volte, fino a 20.000.000 righe,Suppose your growing table has 2,000,000 rows, but the prediction is it will grow 10 times to 20,000,000 rows. iniziare con un numero di bucket 10 volte superiore al numero di righe nella tabella.Start with a bucket count that is 10 times the number of rows in the table. In questo modo si avrà spazio sufficiente per un numero maggiore di righe.This gives you room for an increased quantity of rows.

  • In teoria, è consigliabile aumentare il numero di bucket quando la quantità di righe raggiunge il numero di bucket iniziale.Ideally you would increase the bucket count when the quantity of rows reaches the initial bucket count.
  • Anche se il numero di righe diventasse 5 volte superiore rispetto al numero di bucket, le prestazioni sarebbero ancora soddisfacenti in molte situazioni.Even if the quantity of rows grows to 5 times larger than the bucket count, the performance is still good in most situations.

Si supponga che un indice hash includa 10.000.000 valori di chiave distinct.Suppose a hash index has 10,000,000 distinct key values.

  • Un numero di bucket pari a 2.000.000 sarebbe il valore minimo accettabile.A bucket count of 2,000,000 would be about as low as you could accept. La riduzione del livello delle prestazioni potrebbe essere tollerabile.The degree of performance degradation could be tolerable.

L'indice contiene troppi valori duplicati?Too many duplicate values in the index?

Se i valori indicizzati di hash presentano un tasso elevato di duplicati, i bucket di hash avranno catene più lunghe.If the hash indexed values have a high rate of duplicates, the hash buckets suffer longer chains.

Si supponga di avere la stessa tabella SupportEvent del blocco di codice con sintassi T-SQL riportato sopra.Assume you have the same SupportEvent table from the earlier T-SQL syntax code block. Il codice T-SQL seguente dimostra come trovare e visualizzare il rapporto tra tutti i valori e i valori univoci :The following T-SQL code demonstrates how you can find and display the ratio of all values to unique values:

-- 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  
  • Un rapporto di 10:0 o superiore indica che un hash sarebbe un tipo di indice inadeguato.A ratio of 10.0 or higher means a hash would be a poor type of index. Considerare la possibilità di utilizzare in alternativa un indice non cluster.Consider using a nonclustered index instead,

Risoluzione dei problemi relativi al numero di bucket dell'indice hashTroubleshooting hash index bucket count

Questa sezione illustra come risolvere i problemi relativi al numero di bucket per l'indice hash.This section discusses how to troubleshoot the bucket count for your hash index.

Monitorare le statistiche per le catene e i bucket vuotiMonitor statistics for chains and empty buckets

È possibile monitorare l'integrità statistica degli indici hash eseguendo l'istruzione T-SQL SELECT seguente.You can monitor the statistical health of your hash indexes by running the following T-SQL SELECT. L'istruzione SELECT usa la vista di gestione dati (DMV) denominata sys.dm_db_xtp_hash_index_stats.The SELECT uses the data management view (DMV) named 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];  

Confrontare i risultati dell'istruzione SELECT con le linee guida statistiche seguenti:Compare the SELECT results to the following statistical guidelines:

  • Bucket vuoti:Empty buckets:
    • 33% è un valore di destinazione valido, ma una percentuale più grande (anche 90%) è in genere accettabile.33% is a good target value, but a larger percentage (even 90%) is usually fine.
    • Quando il numero di bucket è pari al numero di valori di chiave distinct, circa il 33% dei bucket è vuoto.When the bucket count equals the number of distinct key values, approximately 33% of the buckets are empty.
    • Un valore inferiore al 10% è troppo basso.A value below 10% is too low.
  • Catene all'interno dei bucket:Chains within buckets:
    • Una lunghezza media della catena pari a 1 è ideale nel caso in cui non sono presenti valori duplicati delle chiavi di indice.An average chain length of 1 is ideal in case there are no duplicate index key values. Le lunghezze della catena fino a 10 sono in genere accettabili.Chain lengths up to 10 are usually acceptable.
    • Se la lunghezza media delle catene è maggiore di 10 e la percentuale di bucket vuoti è superiore al 10%, il numero di duplicati dei dati è tale per cui un indice hash potrebbe non essere il tipo più appropriato.If the average chain length is greater than 10, and the empty bucket percent is greater than 10%, the data has so many duplicates that a hash index might not be the most appropriate type.

Dimostrazione delle catene e dei bucket vuotiDemonstration of chains and empty buckets

Il blocco di codice T-SQL seguente offre un modo semplice per testare un SELECT * FROM sys.dm_db_xtp_hash_index_stats;.The following T-SQL code block gives you an easy way to test a SELECT * FROM sys.dm_db_xtp_hash_index_stats;. Il blocco di codice viene completato in 1 minuto.The code block completes in 1 minute. Di seguito sono riportate le fasi del blocco di codice seguente:Here are the phases of the following code block:

  1. Crea una tabella ottimizzata per la memoria con alcuni indici hash.Creates a memory-optimized table that has a few hash indexes.
  2. Popola la tabella con migliaia di righe.Populates the table with thousands of rows.
    A.a. Viene usato un operatore modulo per configurare il tasso di valori duplicati nella colonna StatusCode.A modulo operator is used to configure the rate of duplicate values in the StatusCode column.
    B.b. Il ciclo inserisce 262.144 righe in circa 1 minuto.The loop inserts 262,144 rows in approximately 1 minute.
  3. Con PRINT viene stampato un messaggio che chiede di eseguire l'istruzione SELECT precedente da sys.dm_db_xtp_hash_index_stats.PRINTs a message asking you to run the earlier SELECT from 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  

Il ciclo INSERT precedente esegue queste operazioni:The preceding INSERT loop does the following:

  • Inserisce valori univoci per l'indice di chiave primaria e per ix_OrderSequence.Inserts unique values for the primary key index, and for ix_OrderSequence.
  • Inserisce un paio di centinaia di migliaia di righe che rappresentano solo 8 valori distinct per StatusCode.Inserts a couple hundred thousands rows which represent only 8 distinct values for StatusCode. È quindi presente un tasso elevato di duplicazione di valori nell'indice ix_StatusCode.Therefore there is a high rate of value duplication in index ix_StatusCode.

Per la risoluzione dei problemi quando il numero di bucket non è ottimale, esaminare il seguente output di SELECT da sys.dm_db_xtp_hash_index_stats.For troubleshooting when the bucket count is not optimal, examine the following output of the SELECT from sys.dm_db_xtp_hash_index_stats. Per questi risultati abbiamo aggiunto WHERE Object_Name(h.object_id) = 'SalesOrder_Mem' all'istruzione SELECT copiata dalla sezione D.1.For these results we added WHERE Object_Name(h.object_id) = 'SalesOrder_Mem' to the SELECT copied from section D.1.

I risultati SELECT vengono visualizzati dopo il codice, artificialmente suddivisi in due tabelle di risultati più ristretti per una migliore visualizzazione.Our SELECT results are displayed after the code, artificially split into two narrower results tables for better display.

  • Di seguito sono riportati i risultati per bucket_count.Here are the results for bucket count.
IndexNameIndexName total_bucket_counttotal_bucket_count empty_bucket_countempty_bucket_count EmptyBucketPercentEmptyBucketPercent
ix_OrderSequenceix_OrderSequence 3276832768 1313 00
ix_StatusCodeix_StatusCode 88 44 5050
PK_SalesOrd_B14003...PK_SalesOrd_B14003... 262144262144 9652596525 3636
  • Successivamente vengono riportati i risultati per chain_length.Next are the results for chain length.
IndexNameIndexName avg_chain_lengthavg_chain_length max_chain_lengthmax_chain_length
ix_OrderSequenceix_OrderSequence 88 2626
ix_StatusCodeix_StatusCode 6553665536 6553665536
PK_SalesOrd_B14003...PK_SalesOrd_B14003... 11 88

Interpretiamo la tabella dei risultati precedenti per i tre indici hash:Let us interpret the preceding results tables for the three hash indexes:

ix_StatusCode:ix_StatusCode:

  • Il 50% dei bucket è vuoto, una condizione positiva.50% of the buckets are empty, which is good.
  • La lunghezza media della catena, però, è molto elevata (65536).However, the average chain length is very high at 65536.
    • Questo è indicativo di un tasso elevato di valori duplicati.This indicates a high rate of duplicate values.
    • Quindi, l'uso di un indice hash non è appropriato in questo caso.Therefore, using a hash index is not appropriate in this case. È più opportuno utilizzare in alternativa un indice non cluster.A nonclustered index should be used instead.

ix_OrderSequence:ix_OrderSequence:

  • Lo 0% dei bucket è vuoto, un valore troppo basso.0% of the buckets are empty, which is too low.
  • La lunghezza media della catena è 8, anche se tutti i valori dell'indice sono univoci.The average chain length is 8, even though all values in this index are unique.
    • È quindi consigliabile aumentare il numero di bucket per avvicinare la lunghezza media della catena a 2 o 3.Therefore the bucket count should be increased, to reduce the average chain length closer to 2 or 3.
  • Dato che la chiave di indice ha 262144 valori univoci, il numero di bucket deve essere pari ad almeno 262144.Because the index key has 262144 unique values, the bucket count should be at least 262144.
    • Se si prevede una crescita futura, il numero di bucket deve essere maggiore.If future growth is expected, the bucket count should be higher.

Indice di chiave primaria (PK_SalesOrd_...):Primary key index (PK_SalesOrd_...):

  • Il 36% dei bucket è vuoto, una condizione positiva.36% of the buckets are empty, which is good.
  • La lunghezza media della catena è pari a 1, un'altra condizione positiva.The average chain length is 1, which is also good. Non è necessario apportare alcuna modifica.No change is needed.

Soluzione di compromessoBalancing the trade-off

I carichi di lavoro OLTP si focalizzano su singole righe.OLTP workloads focus on individual rows. Le scansioni complete delle tabelle non fanno in genere parte del percorso critico per le prestazioni per i carichi di lavoro OLTP.Full table scans are not usually in the performance critical path for OLTP workloads. È pertanto necessario trovare un compromesso tra quantità dell'utilizzo della memoria e prestazioni dei test di uguaglianza e delle operazioni di inserimento.Therefore, the trade-off you must balance is between quantity of memory utilization versus performance of equality tests and insert operations.

Se l'utilizzo della memoria è l'aspetto più rilevante:If memory utilization is the bigger concern:

  • Scegliere un numero di bucket simile al numero di record di chiave di indice.Choose a bucket count close to the number of index key records.
  • Il numero di bucket non deve essere notevolmente inferiore al numero di valori di chiave di indice, in quanto ciò influisce sulla maggior parte delle operazioni DML nonché sul tempo necessario per il recupero del database dopo il riavvio del server.The bucket count should not be significantly lower than the number of index key values, as this impacts most DML operations as well the time it takes to recover the database after server restart.

Se le prestazioni dei test di uguaglianza sono l'aspetto più rilevante:If performance of equality tests is the bigger concern:

  • Un numero di bucket superiore, due o tre volte maggiore rispetto al numero di valori di indice univoci, è appropriato.A higher bucket count, of two or three times the number of unique index values, is appropriate. Un numero maggiore comporta:A higher count means:
    • Maggiore rapidità di recupero quando si cerca uno specifico valore.Faster retrievals when looking for one specific value.
    • Un utilizzo maggiore della memoria.An increased memory utilization.
    • Prolungamento del tempo necessario per l'analisi completa dell'indice hash.An increase in the time required for a full scan of the hash index.

Ulteriori informazioniAdditional reading

Indici hash per tabelle con ottimizzazione per la memoria Hash Indexes for Memory-Optimized Tables
Indice non cluster per tabelle ottimizzate per la memoriaNonclustered Indexes for Memory-Optimized Tables