Indici per tabelle con ottimizzazione per la memoriaIndexes for Memory-Optimized Tables

QUESTO ARGOMENTO SI APPLICA A:sìSQL Server (a partire dalla versione 2014)sìDatabase SQL di AzurenoAzure SQL Data Warehouse noParallel Data Warehouse THIS TOPIC APPLIES TO:yesSQL Server (starting with 2014)yesAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

Questo articolo descrive i tipi di indice disponibili per una tabella con ottimizzazione per la memoria.This article describes the types of indexes that are 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.
  • Descrive la differenza tra gli indici con ottimizzazione per la memoria e i tradizionali indici basati su disco.Describes how memory-optimized indexes differ from traditional disk-based indexes.
  • Spiega in quali circostanze ciascun tipo di indice con ottimizzazione per la memoria rappresenta la scelta ottimale.Explains the circumstances when each type of memory-optimized index is best.

Gli indicihash sono descritti in dettaglio in un articolo strettamente correlato.Hash indexes are discussed in more detail in a closely related article.

Gli indicicolumnstore sono illustrati in un altro articolo.Columnstore indexes are discussed in another article.

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

Ogni istruzione CREATE TABLE per una tabella con ottimizzazione per la memoria deve includere tra 1 e 8 clausole per dichiarare gli indici.Each CREATE TABLE statement for a memory-optimized table must include between 1 and 8 clauses to declare indexes. Il tipo di indice deve essere uno dei seguenti:The index must be one of the following:

  • Indice hash.Hash index.
  • Indice non cluster (struttura interna predefinita di un albero B).Nonclustered index (meaning the default internal structure of a B-tree).

Per essere dichiarata con DURABILITY = SCHEMA_AND_DATA predefinita, la tabella con ottimizzazione per la memoria deve contenere una chiave primaria.To be declared with the default DURABILITY = SCHEMA_AND_DATA, the memory-optimized table must have a primary key. La clausola PRIMARY KEY NONCLUSTERED nell'istruzione CREATE TABLE seguente soddisfa due requisiti:The PRIMARY KEY NONCLUSTERED clause in the following CREATE TABLE statement satisfies two requirements:

  • Fornisce un indice per soddisfare il requisito minimo di un indice nell'istruzione CREATE TABLE.Provides an index to meet the minimum requirement of one index in the CREATE TABLE statement.
  • Fornisce la chiave primaria è necessaria per la clausola SCHEMA_AND_DATA.Provides the primary key that is required for the SCHEMA_AND_DATA clause.
<span data-ttu-id="3457c-120">CREATE TABLE SupportEvent</span><span class="sxs-lookup"><span data-stu-id="3457c-120">CREATE TABLE SupportEvent</span></span>  
<span data-ttu-id="3457c-121">(</span><span class="sxs-lookup"><span data-stu-id="3457c-121">(</span></span>  
    <span data-ttu-id="3457c-122">SupportEventId   int NOT NULL</span><span class="sxs-lookup"><span data-stu-id="3457c-122">SupportEventId   int NOT NULL</span></span>  
        <span data-ttu-id="3457c-123">PRIMARY KEY NONCLUSTERED,</span><span class="sxs-lookup"><span data-stu-id="3457c-123">PRIMARY KEY NONCLUSTERED,</span></span>  
    <span data-ttu-id="3457c-124">...</span><span class="sxs-lookup"><span data-stu-id="3457c-124">...</span></span>  
<span data-ttu-id="3457c-125">)</span><span class="sxs-lookup"><span data-stu-id="3457c-125">)</span></span>  
    <span data-ttu-id="3457c-126">WITH (</span><span class="sxs-lookup"><span data-stu-id="3457c-126">WITH (</span></span>  
        <span data-ttu-id="3457c-127">MEMORY_OPTIMIZED = ON,</span><span class="sxs-lookup"><span data-stu-id="3457c-127">MEMORY_OPTIMIZED = ON,</span></span>  
        <span data-ttu-id="3457c-128">DURABILITY = SCHEMA_AND_DATA);</span><span class="sxs-lookup"><span data-stu-id="3457c-128">DURABILITY = SCHEMA_AND_DATA);</span></span>  

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

Questa sottosezione contiene un blocco di codice Transact-SQL che mostra la sintassi per creare vari indici in una tabella con ottimizzazione per la memoria.This subsection contains a Transact-SQL code block that demonstrates the syntax to create various indexes on a memory-optimized table. Il codice dimostra quanto segue:The code demonstrates the following:

  1. Creare una tabella con ottimizzazione per la memoria.Create a memory-optimized table.
  2. Usare le istruzioni ALTER TABLE per aggiungere due indici.Use ALTER TABLE statements to add two indexes.
  3. Usare INSERT per inserire alcune righe di dati.INSERT a few rows of data.
<span data-ttu-id="3457c-135">DROP TABLE IF EXISTS SupportEvent;</span><span class="sxs-lookup"><span data-stu-id="3457c-135">DROP TABLE IF EXISTS SupportEvent;</span></span>  
<span data-ttu-id="3457c-136">go</span><span class="sxs-lookup"><span data-stu-id="3457c-136">go</span></span>  

<span data-ttu-id="3457c-137">CREATE TABLE SupportEvent</span><span class="sxs-lookup"><span data-stu-id="3457c-137">CREATE TABLE SupportEvent</span></span>  
<span data-ttu-id="3457c-138">(</span><span class="sxs-lookup"><span data-stu-id="3457c-138">(</span></span>  
  <span data-ttu-id="3457c-139">SupportEventId   int               not null   identity(1,1)</span><span class="sxs-lookup"><span data-stu-id="3457c-139">SupportEventId   int               not null   identity(1,1)</span></span>  
    <span data-ttu-id="3457c-140">PRIMARY KEY NONCLUSTERED,</span><span class="sxs-lookup"><span data-stu-id="3457c-140">PRIMARY KEY NONCLUSTERED,</span></span>  

  <span data-ttu-id="3457c-141">StartDateTime        datetime2     not null,</span><span class="sxs-lookup"><span data-stu-id="3457c-141">StartDateTime        datetime2     not null,</span></span>  
  <span data-ttu-id="3457c-142">CustomerName         nvarchar(16)  not null,</span><span class="sxs-lookup"><span data-stu-id="3457c-142">CustomerName         nvarchar(16)  not null,</span></span>  
  <span data-ttu-id="3457c-143">SupportEngineerName  nvarchar(16)      null,</span><span class="sxs-lookup"><span data-stu-id="3457c-143">SupportEngineerName  nvarchar(16)      null,</span></span>  
  <span data-ttu-id="3457c-144">Priority             int               null,</span><span class="sxs-lookup"><span data-stu-id="3457c-144">Priority             int               null,</span></span>  
  <span data-ttu-id="3457c-145">Description          nvarchar(64)      null</span><span class="sxs-lookup"><span data-stu-id="3457c-145">Description          nvarchar(64)      null</span></span>  
<span data-ttu-id="3457c-146">)</span><span class="sxs-lookup"><span data-stu-id="3457c-146">)</span></span>  
  <span data-ttu-id="3457c-147">WITH (</span><span class="sxs-lookup"><span data-stu-id="3457c-147">WITH (</span></span>  
    <span data-ttu-id="3457c-148">MEMORY_OPTIMIZED = ON,</span><span class="sxs-lookup"><span data-stu-id="3457c-148">MEMORY_OPTIMIZED = ON,</span></span>  
    <span data-ttu-id="3457c-149">DURABILITY = SCHEMA_AND_DATA);</span><span class="sxs-lookup"><span data-stu-id="3457c-149">DURABILITY = SCHEMA_AND_DATA);</span></span>  
<span data-ttu-id="3457c-150">go</span><span class="sxs-lookup"><span data-stu-id="3457c-150">go</span></span>  

    --------------------  

<span data-ttu-id="3457c-151">ALTER TABLE SupportEvent</span><span class="sxs-lookup"><span data-stu-id="3457c-151">ALTER TABLE SupportEvent</span></span>  
  <span data-ttu-id="3457c-152">ADD CONSTRAINT constraintUnique_SDT_CN</span><span class="sxs-lookup"><span data-stu-id="3457c-152">ADD CONSTRAINT constraintUnique_SDT_CN</span></span>  
    <span data-ttu-id="3457c-153">UNIQUE NONCLUSTERED (StartDateTime DESC, CustomerName);</span><span class="sxs-lookup"><span data-stu-id="3457c-153">UNIQUE NONCLUSTERED (StartDateTime DESC, CustomerName);</span></span>  
<span data-ttu-id="3457c-154">go</span><span class="sxs-lookup"><span data-stu-id="3457c-154">go</span></span>  

<span data-ttu-id="3457c-155">ALTER TABLE SupportEvent</span><span class="sxs-lookup"><span data-stu-id="3457c-155">ALTER TABLE SupportEvent</span></span>  
  <span data-ttu-id="3457c-156">ADD INDEX idx_hash_SupportEngineerName</span><span class="sxs-lookup"><span data-stu-id="3457c-156">ADD INDEX idx_hash_SupportEngineerName</span></span>  
    <span data-ttu-id="3457c-157">HASH (SupportEngineerName) WITH (BUCKET_COUNT = 64);  -- Nonunique.</span><span class="sxs-lookup"><span data-stu-id="3457c-157">HASH (SupportEngineerName) WITH (BUCKET_COUNT = 64);  -- Nonunique.</span></span>  
<span data-ttu-id="3457c-158">go</span><span class="sxs-lookup"><span data-stu-id="3457c-158">go</span></span>  

    --------------------  

<span data-ttu-id="3457c-159">INSERT INTO SupportEvent</span><span class="sxs-lookup"><span data-stu-id="3457c-159">INSERT INTO SupportEvent</span></span>  
    <span data-ttu-id="3457c-160">(StartDateTime, CustomerName, SupportEngineerName, Priority, Description)</span><span class="sxs-lookup"><span data-stu-id="3457c-160">(StartDateTime, CustomerName, SupportEngineerName, Priority, Description)</span></span>  
  <span data-ttu-id="3457c-161">VALUES</span><span class="sxs-lookup"><span data-stu-id="3457c-161">VALUES</span></span>  
    <span data-ttu-id="3457c-162">('2016-02-25 13:40:41:123', 'Abby', 'Zeke', 2, 'Display problem.'</span><span class="sxs-lookup"><span data-stu-id="3457c-162">('2016-02-25 13:40:41:123', 'Abby', 'Zeke', 2, 'Display problem.'</span></span>     <span data-ttu-id="3457c-163">),</span><span class="sxs-lookup"><span data-stu-id="3457c-163">),</span></span>  
    <span data-ttu-id="3457c-164">('2016-02-25 13:40:41:323', 'Ben' , null  , 1, 'Cannot find help.'</span><span class="sxs-lookup"><span data-stu-id="3457c-164">('2016-02-25 13:40:41:323', 'Ben' , null  , 1, 'Cannot find help.'</span></span>    <span data-ttu-id="3457c-165">),</span><span class="sxs-lookup"><span data-stu-id="3457c-165">),</span></span>  
    <span data-ttu-id="3457c-166">('2016-02-25 13:40:41:523', 'Carl', 'Liz' , 2, 'Button is gray.'</span><span class="sxs-lookup"><span data-stu-id="3457c-166">('2016-02-25 13:40:41:523', 'Carl', 'Liz' , 2, 'Button is gray.'</span></span>      <span data-ttu-id="3457c-167">),</span><span class="sxs-lookup"><span data-stu-id="3457c-167">),</span></span>  
    <span data-ttu-id="3457c-168">('2016-02-25 13:40:41:723', 'Dave', 'Zeke', 2, 'Cannot unhide column.');</span><span class="sxs-lookup"><span data-stu-id="3457c-168">('2016-02-25 13:40:41:723', 'Dave', 'Zeke', 2, 'Cannot unhide column.');</span></span>  
<span data-ttu-id="3457c-169">go</span><span class="sxs-lookup"><span data-stu-id="3457c-169">go</span></span>  

B.B. Natura degli indici con ottimizzazione per la memoria.Nature of memory-optimized indexes

In una tabella con ottimizzazione per la memoria, ogni indice è anche ottimizzato per la memoria.On a memory-optimized table, every index is also memory-optimized. Le differenze tra un indice in un indice con ottimizzazione per la memoria e un indice tradizionale in una tabella basata su disco sono molte.There are several ways in which an index on a memory-optimized index differs from a traditional index on a disk-base table.

Ogni indice con ottimizzazione per la memoria esiste solo nella memoria attiva.Each memory-optimized index exists only in active memory. L'indice non viene rappresentato sul disco.The index has no representation on the disk.

  • Quando il database torna online, gli indici con ottimizzazione per la memoria vengono ricompilati.Memory-optimized indexes are rebuilt when the database is brought back online.

Quando un'istruzione SQL UPDATE modifica i dati in una tabella con ottimizzazione per la memoria, le modifiche corrispondenti agli indici non vengono scritte nel log.When an SQL UPDATE statement modifies data in a memory-optimized table, corresponding changes to its indexes are not written to the log.

Le voci in un indice con ottimizzazione per la memoria contengono un indirizzo di memoria diretto alla riga nella tabella.The entries in a memory-optimized index contain a direct memory address to the row in the table.

  • Al contrario, le voci in un indice tradizionale ad albero B su disco includono un valore di chiave che il sistema deve usare per trovare l'indirizzo di memoria per la riga della tabella associata.In contrast, entries in a traditional B-tree index on disk contain a key value that the system must first use to find the memory address to the associated table row.

Gli indici con ottimizzazione per la memoria non hanno pagine fisse come gli indici basati su disco.Memory-optimized indexes have no fixed pages as do disk-based indexes.

  • Non presentano il tipo tradizionale di frammentazione all'interno di una pagina e non usano quindi il fattore di riempimento.They do not accrue the traditional type of fragmentation within a page, so they have no fillfactor.

C.C. Valori duplicati delle chiavi di indiceDuplicate index key values

I valori duplicati delle chiavi di indice possono influire sulle prestazioni delle operazioni su tabelle con ottimizzazione per la memoria.Duplicate index key values can impact the performance of operations on memory-optimized tables. Un numero elevato di duplicati (ad esempio, 100+) rende inefficienti le attività di gestione di un indice perché la maggior parte delle operazioni di indice deve attraversare catene di duplicati.Large numbers of duplicates (e.g., 100+) make the job of maintaining an index inefficient because duplicate chains must be traversed for most index operations. L'impatto può essere notato nelle operazioni INSERT, UPDATE e DELETE sulle tabelle con ottimizzazione per la memoria.The impact can be seen in INSERT, UPDATE, and DELETE operations on memory-optimized tables. Questo problema è più evidente nel caso di indici hash, sia per il costo inferiore per ogni operazione per gli indici hash sia per l'interferenza di catene di duplicati di grandi dimensioni con la catena di collisioni hash.This problem is more visible in the case of hash indices, due both to the lower cost per operation for hash indices and the interference of large duplicate chains with the hash collision chain. Per ridurre la duplicazione di un indice, usare un indice non cluster e aggiungere colonne aggiuntive, ad esempio dalla chiave primaria, alla fine della chiave di indice per ridurre il numero di duplicati.To reduce duplication in an index, use a nonclustered index and add additional columns (for example from the primary key) to the end of the index key to reduce the number of duplicates.

Si consideri, ad esempio, una tabella clienti con una chiave primaria impostata su CustomerId e un indice sulla colonna CustomerCategoryID.Consider, as an example, a Customers table with a primary key on CustomerId and an index on column CustomerCategoryID. In genere in una determinata categoria si trovano molti clienti e quindi esistono molti valori duplicati per una determinata chiave di indice sulla colonna CustomerCategoryID.There will typically be many customers in a given category, and thus many duplicate values for a given key in the index on CustomerCategoryID. In questo scenario, è consigliabile usare un indice non cluster (CustomerCategoryID, CustomerId).In this scenario, best practice is to use a nonclustered index on (CustomerCategoryID, CustomerId). L'indice può essere usato per le query che usano un predicato che interessa il valore CustomerCategoryID e non contiene la duplicazione evitando inefficienze nella manutenzione di indici.This index can be used for queries that use a predicate involving CustomerCategoryID, and does not contain duplication, and thus does not cause inefficiency in index maintenance.

La query seguente mostra il numero medio di valori di chiave di indice duplicati per l'indice in CustomerCategoryID nella tabella Sales.Customers, all'interno del database di esempio WideWorldImporters.The following query shows the average number of duplicate index key values for the index on CustomerCategoryID in table Sales.Customers, in the sample database WideWorldImporters.

    SELECT AVG(row_count) FROM
       (SELECT COUNT(*) AS row_count 
        FROM Sales.Customers
        GROUP BY CustomerCategoryID) a

Per valutare il numero medio di duplicati di chiave di indice per la tabella e l'indice in uso, sostituire Sales.Customers con il nome della tabella e CustomerCategoryID con l'elenco delle colonne di chiave di indice.To evaluate the average number of index key duplicates for your own table and index, replace Sales.Customers with your table name, and replace CustomerCategoryID with the list of index key columns.

D.D. Confronto tra le situazioni in cui usare ogni tipo di indiceComparing when to use each index type

La scelta del tipo di indice ottimale dipende dalla natura query.The nature of your particular queries determines which type of index is the best choice.

Quando si implementano tabelle con ottimizzazione per la memoria in un'applicazione esistente, la raccomandazione generale consiste nell'iniziare con gli indici non cluster, poiché le relative funzionalità sono più simili alle funzionalità degli indici non cluster e cluster tradizionali sulle tabelle basate su disco.When implementing memory-optimized tables in an existing application, the general recommendation is to start with nonclustered indexes, as their capabilities more closely resemble the capabilities of traditional clustered and nonclustered indexes on disk-based tables.

D.1 Punti di forza degli indici non clusterD.1 Strengths of nonclustered indexes

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

  • Le query hanno una clausola ORDER BY nella colonna indicizzata.Queries have an ORDER BY clause on the indexed column.
  • Query in cui viene verificata solo la colonna o le colonne iniziali di un indice a più colonne.Queries where only the leading column(s) of a multi-column index is tested.
  • Le query verificano la colonna indicizzata usando una clausola WHERE con:Queries test the indexed column by use of a WHERE clause with:
    • Una disuguaglianza: WHERE StatusCode != 'Done'An inequality: WHERE StatusCode != 'Done'
    • Un intervallo di valori: WHERE Quantity >= 100A value range: WHERE Quantity >= 100

Un indice non cluster è da preferirsi a un indice hash in tutte le istruzioni SELECT seguenti:In all the following SELECTs, a nonclustered index is preferable over a hash index:

SELECT col2 FROM TableA  
    WHERE StartDate > DateAdd(day, -7, GetUtcDate());  

SELECT col3 FROM TableB  
    WHERE ActivityCode != 5;  

SELECT StartDate, LastName  
    FROM TableC  
    ORDER BY StartDate;  

SELECT IndexKeyColumn2  
    FROM TableD  
    WHERE IndexKeyColumn1 = 42;  

D.2 Punti di forza degli indici hashD.2 Strengths 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 le colonne indicizzate usando una clausola WHERE con un'uguaglianza esatta su tutte le colonne delle chiavi di indice, come nell'esempio seguente:Queries test the indexed columns by use of a WHERE clause with an exact equality on all index key columns, as in the following:
<span data-ttu-id="3457c-210">SELECT col9 FROM TableZ</span><span class="sxs-lookup"><span data-stu-id="3457c-210">SELECT col9 FROM TableZ</span></span>  
    <span data-ttu-id="3457c-211">WHERE Z_Id = 2174;</span><span class="sxs-lookup"><span data-stu-id="3457c-211">WHERE Z_Id = 2174;</span></span>  

D.3 Tabella riepilogativa dei punti di forza degli indici a confrontoD.3 Summary table to compare index strengths

Nella tabella seguente sono elencate tutte le operazioni supportate dai vari tipi di indice.The following table lists all operations that are supported by the different index types.

OperazioneOperation Con ottimizzazione per la memoria,Memory-optimized,
hashhash
Con ottimizzazione per la memoria,Memory-optimized,
non clusternonclustered
Basato su disco,Disk-based,
(non) cluster(non)clustered
Index Scan, recupera tutte le righe della tabella.Index Scan, retrieve all table rows. Yes Yes Yes
Index Seek su predicati di uguaglianza (=).Index seek on equality predicates (=). Yes
(chiave completa necessaria)(Full key is required.)
Yes Yes
Index Seek su predicati di disuguaglianza e di intervalloIndex seek on inequality and range predicates
(>, <, <=, >=, BETWEEN).(>, <, <=, >=, BETWEEN).
NoNo
(risultati in un'analisi di indice)(Results in an index scan.)
Yes Yes
Recupero di righe con un ordinamento corrispondente alla definizione dell'indice.Retrieve rows in a sort order that matches the index definition. NoNo Yes Yes
Recupero di righe con un ordinamento inverso rispetto alla definizione dell'indice.Retrieve rows in a sort-order that matches the reverse of the index definition. NoNo NoNo Yes

Nella tabella, Sì significa che l'indice può soddisfare la richiesta in modo appropriato e No significa che non può.In the table, Yes means that the index can efficiently service the request, and No means that the index cannot efficiently satisfy the request.