Hash Indexes for Memory-Optimized Tables (Indici hash per tabelle con ottimizzazione per la memoria)Hash Indexes for Memory-Optimized Tables

In questo argomento si applica a: SìSQL ServerSìDatabase SQL di AzurenonAzure SQL Data Warehouse non Parallel Data WarehouseTHIS TOPIC APPLIES TO: yesSQL ServeryesAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

Questo articolo descrive il tipo di indice hash disponibile per una tabella ottimizzata per la memoria.This article describes the hash type of index that is available for a memory-optimized table. Questo articolo:The article:

  • Fornisce brevi esempi di codice per illustrare la sintassi Transact-SQL.Provides short code examples to demonstrate the Transact-SQL syntax.
  • Illustra le nozioni fondamentali sugli indici hash.Describes the fundamentals of hash indexes.
  • Descrive come stimare un numero di bucket appropriato.Describes how to estimate an appropriate bucket count.
  • Descrive come progettare e gestire gli indici hash.Describes how to design and manage your hash indexes.

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:

A.A. Sintassi per gli indici ottimizzati per la memoriaSyntax for memory-optimized indexes

A.1 Esempio di codice per la sintassiA.1 Code sample for syntax

Questa sottosezione contiene un blocco di codice Transact-SQL che mostra le sintassi disponibili per creare un indice hash in una tabella ottimizzata per la memoria.This subsection contains a Transact-SQL code block that demonstrates the available syntaxes to create a hash index on a memory-optimized table:

  • Nell'esempio viene illustrato che l'indice hash viene dichiarato all'interno dell'istruzione CREATE TABLE.The sample shows the hash index is declared inside the CREATE TABLE statement.
<span data-ttu-id="98aac-118">DROP TABLE IF EXISTS SupportEventHash;</span><span class="sxs-lookup"><span data-stu-id="98aac-118">DROP TABLE IF EXISTS SupportEventHash;</span></span>  
<span data-ttu-id="98aac-119">go</span><span class="sxs-lookup"><span data-stu-id="98aac-119">go</span></span>  

<span data-ttu-id="98aac-120">CREATE TABLE SupportIncidentRating_Hash</span><span class="sxs-lookup"><span data-stu-id="98aac-120">CREATE TABLE SupportIncidentRating_Hash</span></span>  
<span data-ttu-id="98aac-121">(</span><span class="sxs-lookup"><span data-stu-id="98aac-121">(</span></span>  
  <span data-ttu-id="98aac-122">SupportIncidentRatingId   int      not null   identity(1,1)</span><span class="sxs-lookup"><span data-stu-id="98aac-122">SupportIncidentRatingId   int      not null   identity(1,1)</span></span>  
    <span data-ttu-id="98aac-123">PRIMARY KEY NONCLUSTERED,</span><span class="sxs-lookup"><span data-stu-id="98aac-123">PRIMARY KEY NONCLUSTERED,</span></span>  

  <span data-ttu-id="98aac-124">RatingLevel          int           not null,</span><span class="sxs-lookup"><span data-stu-id="98aac-124">RatingLevel          int           not null,</span></span>  

  <span data-ttu-id="98aac-125">SupportEngineerName  nvarchar(16)  not null,</span><span class="sxs-lookup"><span data-stu-id="98aac-125">SupportEngineerName  nvarchar(16)  not null,</span></span>  
  <span data-ttu-id="98aac-126">Description          nvarchar(64)      null,</span><span class="sxs-lookup"><span data-stu-id="98aac-126">Description          nvarchar(64)      null,</span></span>  

  <span data-ttu-id="98aac-127">INDEX ix_hash_SupportEngineerName</span><span class="sxs-lookup"><span data-stu-id="98aac-127">INDEX ix_hash_SupportEngineerName</span></span>  
    <span data-ttu-id="98aac-128">HASH (SupportEngineerName) WITH (BUCKET_COUNT = 100000)</span><span class="sxs-lookup"><span data-stu-id="98aac-128">HASH (SupportEngineerName) WITH (BUCKET_COUNT = 100000)</span></span>  
<span data-ttu-id="98aac-129">)</span><span class="sxs-lookup"><span data-stu-id="98aac-129">)</span></span>  
  <span data-ttu-id="98aac-130">WITH (</span><span class="sxs-lookup"><span data-stu-id="98aac-130">WITH (</span></span>  
    <span data-ttu-id="98aac-131">MEMORY_OPTIMIZED = ON,</span><span class="sxs-lookup"><span data-stu-id="98aac-131">MEMORY_OPTIMIZED = ON,</span></span>  
    <span data-ttu-id="98aac-132">DURABILITY = SCHEMA_ONLY);</span><span class="sxs-lookup"><span data-stu-id="98aac-132">DURABILITY = SCHEMA_ONLY);</span></span>  
<span data-ttu-id="98aac-133">go</span><span class="sxs-lookup"><span data-stu-id="98aac-133">go</span></span>  

Per determinare il corretto BUCKET_COUNT per i propri dati, vedere Configurazione del numero di bucket dell'indice hash.To determine the right BUCKET_COUNT for your data, see Configuring the hash index bucket count.

B.B. Indici hashHash indexes

B.1 Nozioni fondamentali sulle prestazioniB.1 Performance basics

Le prestazioni di un indice hash sono:The performance of a hash index is:

  • Eccellenti quando la clausola WHERE specifica un valore esatto per ogni colonna della chiave di indice hash.Excellent when the WHERE clause specifies an exact value for each column in the hash index key.
  • Scarse quando la clausola WHERE cerca un intervallo di valori nella chiave di indice.Poor when the WHERE clause looks for a range of values in the index key.
  • Scarse quando la clausola WHERE specifica un determinato valore per la prima colonna di una chiave di indice hash a due colonne, ma non specifica un valore per la seconda colonna della chiave.Poor when the WHERE clause specifies one specific value for the first column of a two column hash index key, but does not specify a value for the second column of the key.

B.2 Limitazioni di dichiarazioneB.2 Declaration limitations

Un indice hash può essere presente solo in una tabella ottimizzata per la memoria.A hash index can exist only on a memory-optimized table. Non può esistere in una tabella basata su disco.It cannot exist on a disk-based table.

Un indice hash può essere dichiarato:A hash index can be declared as:

  • UNIQUE, oppure per impostazione predefinita Nonunique.UNIQUE, or can default to Nonunique.
  • NONCLUSTERED, che è l'impostazione predefinita.NONCLUSTERED, which is the default.

Di seguito è riportato un esempio della sintassi per creare un indice hash al di fuori dell'istruzione CREATE TABLE:Here is an example of the syntax to create a hash index outside of the CREATE TABLE statement:

ALTER TABLE MyTable_memop  
  ADD INDEX ix_hash_Column2 UNIQUE  
    HASH (Column2) WITH (BUCKET_COUNT = 64);  

B.3 Bucket e funzione hashB.3 Buckets and hash function

Un indice hash ancora i propri valori di chiave in una matrice di bucket :A hash index anchors its key values in what we call a bucket array:

  • Ogni bucket è costituito da 8 byte, che vengono usati per archiviare l'indirizzo di memoria di un elenco di collegamenti delle voci della chiave.Each bucket is 8 bytes, which are used to store the memory address of a link list of key entries.
  • Ogni voce rappresenta un valore per una chiave di indice, oltre all'indirizzo della riga corrispondente nella tabella ottimizzata per la memoria sottostante.Each entry is a value for an index key, plus the address of its corresponding row in the underlying memory-optimized table.
    • Ogni voce punta alla voce successiva in un elenco di collegamenti di voci, tutte concatenate per il bucket corrente.Each entry points to the next entry in a link list of entries, all chained to the current bucket.

L'algoritmo di hash tenta di distribuire tutti i valori di chiave univoci o distinct in modo uniforme tra i bucket, ma è difficile raggiungere l'uniformità totale.The hashing algorithm tries to spread all the unique or distinct key values evenly among its buckets, but total evenness is an unreached ideal. Allo stesso bucket vengono concatenate tutte le istanze di un qualsiasi valore di chiave specificato.All instances of any given key value are chained to the same bucket. Il bucket può anche contenere una combinazione di tutte le istanze di un valore chiave diverso.The bucket might also have mixed in all instances of a different key value.

  • Una combinazione di questo tipo è detta collisione hash.This mixture is called a hash collision. Le collisioni sono comuni ma non ottimali.Collisions are common but are not ideal.
  • Un obiettivo realistico è che il 30% dei bucket contengano due valori di chiave diversi.A realistic goal is for 30% of the buckets contain two different key values.

È necessario dichiarare quanti bucket dovrà avere l'indice hash.You declare how many buckets a hash index shall have.

  • Minore è il rapporto tra bucket e righe di tabella o valori distinct, maggiore sarà la lunghezza dell'elenco di collegamenti bucket medio.The lower the ratio of buckets to table rows or to distinct values, the longer the average bucket link list will be.
  • Gli elenchi di collegamenti brevi risultano più veloci rispetto agli elenchi di collegamenti lunghi.Short link lists perform faster than long link lists.

In SQL Server è disponibile una funzione hash usata per tutti gli indici hash.SQL Server has one hash function it uses for all hash indexes:

  • La funzione hash è deterministica: dato lo stesso valore di chiave di input, restituisce lo stesso slot di bucket.The hash function is deterministic: given the same input key value, it consistently outputs the same bucket slot.
  • Con chiamate ripetute, gli output della funzione hash tendono a formare una distribuzione di Poisson o a campana, non una distribuzione lineare piana.With repeated calls, the outputs of the hash function tend to form a Poisson or bell curve distribution, not a flat linear distribution.

Nell'immagine seguente è riepilogata l'interazione tra l'indice hash e i bucket.The interplay of the hash index and the buckets is summarized in the following image.

hekaton_tables_23dhekaton_tables_23d

B.4 Versioni delle righe e Garbage CollectionB.4 Row versions and garbage collection

In una tabella ottimizzata per la memoria, quando una riga è interessata da un'istruzione SQL UPDATE, la tabella crea una versione aggiornata della riga.In a memory-optimized table, when a row is affected by an SQL UPDATE, the table creates an updated version of the row. Durante la transazione di aggiornamento, altre sessioni potrebbero riuscire a leggere la versione precedente della riga e quindi evitare il rallentamento delle prestazioni associato a un blocco di riga.During the update transaction, other sessions might be able to read the older version of the row and thereby avoid the performance slowdown associated with a row lock.

L'indice hash potrebbe anche avere versioni diverse delle relative voci per includere l'aggiornamento.The hash index might also have different versions of its entries to accommodate the update.

In seguito, quando le versioni precedenti non sono più necessarie, un thread di Garbage Collection (GC) attraversa i bucket e i relativi elenchi di collegamenti per eliminare le voci obsolete.Later when the older versions are no longer needed, a garbage collection (GC) thread traverses the buckets and their link lists to clean away old entries. Il thread GC offre prestazioni migliori se le catene degli elenchi di collegamenti sono brevi.The GC thread performs better if the link list chain lengths are short.

C.C. Configurazione del numero di bucket dell'indice hashConfiguring the hash index bucket count

Il numero di bucket dell'indice hash viene specificato al momento della creazione dell'indice e può essere modificato utilizzando la sintassi ALTER TABLE...ALTER INDEX REBUILD.The hash index bucket count is specified at index create time, and can be changed using the ALTER TABLE...ALTER INDEX REBUILD syntax.

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.
Non è sempre possibile stimare quanti valori ha o potrebbe avere una particolare chiave di indice.You may not always be able to predict how many values a particular index key may have or will have. Le prestazioni sono di norma ancora soddisfacenti se il valore BUCKET_COUNT è al massimo 10 volte superiore al numero effettivo di valori chiave e un valore superiore è in genere migliore di un valore inferiore.Performance is usually still good if the BUCKET_COUNT value is within 10 times of the actual number of key values, and overestimating is generally better than underestimating.

Un numero di bucket troppo ridotto presenta i seguenti svantaggi:Too few buckets has the following drawbacks:

  • Più collisioni hash di valori di chiave distinct.More hash collisions of distinct key values.
    • Ogni valore distinct è costretto a condividere lo stesso bucket con un valore distinct diverso.Each distinct value is forced to share the same bucket with a different distinct value.
    • La lunghezza media di catena per bucket aumenta.The average chain length per bucket grows.
    • Più è lunga la catena di bucket, più lente saranno le ricerche di uguaglianza nell'indice.The longer the bucket chain, the slower the speed of equality lookups in the index and .

Un numero di bucket troppo elevato presenta i seguenti svantaggi:Too many buckets has the following drawbacks:

  • Un numero di bucket troppo elevato può comportare la presenza di più bucket vuoti.Too high a bucket count might result in more empty buckets.
    • I bucket vuoti influiscono sulle prestazioni delle analisi complete degli indici.Empty buckets impact the performance of full index scans. Se le analisi vengono eseguite regolarmente, è consigliabile scegliere un numero di bucket simile al numero dei valori di chiave di indice distinct.If those are performed regularly, consider picking a bucket count close to the number of distinct index key values.
    • I bucket vuoti utilizzano memoria, anche se ogni bucket utilizza solo 8 byte.Empty buckets use memory, though each bucket uses only 8 bytes.

Nota

L'aggiunta di ulteriori bucket non determina in alcun modo la riduzione del concatenamento di voci che condividono un valore duplicato.Adding more buckets does nothing to reduce the chaining together of entries that share a duplicate value. La frequenza di duplicazione dei valori viene usata per stabilire se un hash è del tipo di indice appropriato, non per calcolare il numero di bucket.The rate of value duplication is used to decide whether a hash is the appropriate index type, not to calculate the bucket count.

C.1 Numeri praticiC.1 Practical numbers

Anche se il valore BUCKET_COUNT è moderatamente inferiore o superiore all'intervallo preferito, è probabile che le prestazioni dell'indice hash siano tollerabili o accettabili.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. Non si verificano problemi.No crisis is created.

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.Give your hash index a BUCKET_COUNT roughly equal to the number of rows you predict your memory-optimized table will grow to have.

Se ad esempio 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 you predict the quantity 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.

C.2 L'indice contiene troppi valori duplicati?C.2 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,

D.D. 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.

D.1 Monitorare le statistiche per le catene e i bucket vuotiD.1 Monitor 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.

D.2 Dimostrazione delle catene e dei bucket vuotiD.2 Demonstration 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 INSERT inserisce 262144 righe in circa 1 minuto.The loop INSERTs 262144 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.

D.3 Soluzione di compromessoD.3 Balancing 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. Occorre quindi trovare un compromesso tra:Therefore, the trade-off you must balance is between:

  • Quantità di memoria utilizzata eQuantity of memory utilization; versus
  • Prestazioni dei test di uguaglianza e delle operazioni di inserimento.Performance of equality tests, and of 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.

E.E. Punti di forza degli indici hashStrengths of hash indexes

Un indice hash è da preferirsi a un indice non cluster quando:A hash index is preferable over a nonclustered index when:

  • Le query verificano la colonna indicizzata usando una clausola WHERE con un'uguaglianza, come nell'esempio seguente:Queries test the indexed column by use of a WHERE clause with an equality, as in the following:
<span data-ttu-id="98aac-309">SELECT col9 FROM TableZ</span><span class="sxs-lookup"><span data-stu-id="98aac-309">SELECT col9 FROM TableZ</span></span>  
    <span data-ttu-id="98aac-310">WHERE Z_Id = 2174;</span><span class="sxs-lookup"><span data-stu-id="98aac-310">WHERE Z_Id = 2174;</span></span>  

E.1 Chiavi di indice hash a più colonneE.1 Multi-column hash index keys

L'indice a due colonne può essere un indice non cluster o un indice hash.Your two column index could be a nonclustered index or a hash index. Si supponga che le colonne di indice siano col1 e col2.Suppose the index columns are col1 and col2. Data la seguente istruzione SQL SELECT, solo l'indice non cluster potrebbe essere utile per Query Optimizer:Given the following SQL SELECT statement, only the nonclustered index would be useful to the query optimizer:

SELECT col1, col3  
    FROM MyTable_memop  
    WHERE col1 = 'dn';  

L'indice hash necessita della clausola WHERE per specificare un test di uguaglianza per ogni colonna della chiave.The hash index needs the WHERE clause to specify an equality test for each of the columns in its key. In caso contrario l'indice hash non è utile per Query Optimizer.Else the hash index is not useful to the optimizer.

Nessuno dei due tipi di indice è utile se la clausola WHERE specifica solo la seconda colonna della chiave di indice.Neither index type is useful if the WHERE clause specifies only the second column in the index key.