Indici in tabelle con ottimizzazione per la memoriaIndexes on 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

Tutte le tabelle ottimizzate per la memoria devono contenere almeno un indice poiché sono gli indici che consentono l'interconnessione delle righe.All memory-optimized tables must have at least one index, because it is the indexes that connect the rows together. 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 ottimizzato 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:

  • Le righe di dati non vengono archiviate in pagine. Non è pertanto possibile fare riferimento a una raccolta di pagine o extent, né a partizioni o unità di allocazione per ottenere tutte le pagine di una tabella.Data rows are not stored on pages, so there is no collection of pages or extents, no partitions or allocation units that can be referenced to get all the pages for a table. Il concetto di pagine di indice per uno dei tipi di indici disponibili è presente, ma sono archiviati in modo diverso rispetto agli indici per le tabelle basate su disco.There is the concept of index pages for one of the available types of indexes, but they are stored differently than indexes for disk-based tables. 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.
  • Le modifiche apportate agli indici nelle tabelle ottimizzate per la memoria durante la manipolazione dei dati non vengono mai scritte su disco.Changes made to indexes on memory-optimized tables during data manipulation are never written to disk. Solo le righe di dati e le modifiche apportate ai dati vengono scritte nel log delle transazioni.Only the data rows, and changes to the data, are written to the transaction log.
  • 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.

Tutti gli indici nelle tabelle ottimizzate per la memoria vengono creati in base alle definizioni degli indici durante il recupero del database.All indexes on memory-optimized tables are created based on the index definitions during database recovery.

Il tipo di indice deve essere uno dei seguenti:The index must be one of the following:

  • Indice hashHash index
  • Indice non cluster ottimizzato per la memoria (struttura interna predefinita di un albero B)Memory-optimized Nonclustered index (meaning the default internal structure of a B-tree)

Gli indici hash sono illustrati in dettaglio in Indici hash per tabelle ottimizzate per la memoria.Hash indexes are discussed in more detail in Hash Indexes for Memory-Optimized Tables. Gli indici non cluster sono illustrati in dettaglio in Indice non cluster per tabelle ottimizzate per la memoria.Nonclustered indexes are discussed in more detail in Nonclustered Index for Memory-Optimized Tables.
Gli indicicolumnstore sono illustrati in un altro articolo.Columnstore indexes are discussed in another article.

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

Ogni istruzione CREATE TABLE per una tabella ottimizzata per la memoria deve includere un indice, in modo esplicito tramite un INDEX o in modo implicito tramite un vincolo PRIMAY KEY o UNIQUE.Each CREATE TABLE statement for a memory-optimized table must include an index, either explicitly through an INDEX or implicitly through a PRIMAY KEY or UNIQUE constraint.

Per essere dichiarata con la clausola DURABILITY = SCHEMA_AND_DATA predefinita, la tabella ottimizzata 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.

    CREATE TABLE SupportEvent  
    (  
        SupportEventId   int NOT NULL  
            PRIMARY KEY NONCLUSTERED,  
        ...  
    )  
        WITH (  
            MEMORY_OPTIMIZED = ON,  
            DURABILITY = SCHEMA\_AND_DATA);  
    

    Nota

    SQL Server 2014SQL Server 2014 e SQL Server 2016SQL Server 2016 hanno un limite di 8 indici per ogni tipo di tabella o tabella ottimizzata per la memoria. and SQL Server 2016SQL Server 2016 have a limit of 8 indexes per memory-optimized table or table type. A partire da SQL Server 2017SQL Server 2017 e in Database SQL di AzureAzure SQL Database non è più previsto un limite al numero di indici specifici di tabelle ottimizzate per la memoria e tipi di tabella.Starting with SQL Server 2017SQL Server 2017 and in Database SQL di AzureAzure SQL Database, there is no longer a limit on the number of indexes specific to memory-optimized tables and table types.

Esempio di codice per la sintassiCode sample for syntax

Questa sottosezione contiene un blocco di codice Transact-SQL che mostra la sintassi per creare vari indici in una tabella ottimizzata 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 ottimizzata 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.

    DROP TABLE IF EXISTS SupportEvent;  
    go  
    
    CREATE TABLE SupportEvent  
    (  
        SupportEventId   int               not null   identity(1,1)  
        PRIMARY KEY NONCLUSTERED,  
    
        StartDateTime        datetime2     not null,  
        CustomerName         nvarchar(16)  not null,  
        SupportEngineerName  nvarchar(16)      null,  
        Priority             int               null,  
        Description          nvarchar(64)      null  
    )  
        WITH (  
        MEMORY_OPTIMIZED = ON,  
        DURABILITY = SCHEMA\_AND_DATA);  
    go  
    
        --------------------  
    
    ALTER TABLE SupportEvent  
        ADD CONSTRAINT constraintUnique_SDT_CN  
        UNIQUE NONCLUSTERED (StartDateTime DESC, CustomerName);  
    go  
    
    ALTER TABLE SupportEvent  
        ADD INDEX idx_hash_SupportEngineerName  
        HASH (SupportEngineerName) WITH (BUCKET_COUNT = 64);  -- Nonunique.  
    go  
    
        --------------------  
    
    INSERT INTO SupportEvent  
        (StartDateTime, CustomerName, SupportEngineerName, Priority, Description)  
        VALUES  
        ('2016-02-23 13:40:41:123', 'Abby', 'Zeke', 2, 'Display problem.'     ),  
        ('2016-02-24 13:40:41:323', 'Ben' , null  , 1, 'Cannot find help.'    ),  
        ('2016-02-25 13:40:41:523', 'Carl', 'Liz' , 2, 'Button is gray.'      ),  
        ('2016-02-26 13:40:41:723', 'Dave', 'Zeke', 2, 'Cannot unhide column.');  
    go 
    

Valori duplicati delle chiavi di indiceDuplicate index key values

I valori duplicati delle chiavi di indice possono influire sulle prestazioni delle operazioni su tabelle ottimizzate 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 è evidente nelle operazioni INSERT, UPDATE e DELETE su tabelle ottimizzate 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 indexes, due both to the lower cost per operation for hash indexes 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. Per altre informazioni sulle collisioni hash, vedere Indici hash per tabelle ottimizzate per la memoria.For more information about hash collisions, see Hash Indexes for Memory-Optimized Tables.

Si consideri ad esempio una tabella Customers con una chiave primaria in CustomerId e un indice nella colonna CustomerCategoryID.For example, consider 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 in (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 CustomerCategoryIDe non contiene la duplicazione evitando inefficienze nella manutenzione dell'indice.This index can be used for queries that use a predicate involving CustomerCategoryID, and does not contain duplication, and therefore 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.

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

Indicazioni per l'uso di indici non clusterRecommendations for nonclustered index use

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'analisi dell'intervallo di valori: WHERE Quantity >= 100A value range scan: 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 CustomerName, Priority, Description 
FROM SupportEvent  
WHERE StartDateTime > DateAdd(day, -7, GetUtcDate());  

SELECT CustomerName, Priority, Description 
FROM SupportEvent  
WHERE CustomerName != 'Ben';  

SELECT StartDateTime, CustomerName  
FROM SupportEvent  
ORDER BY StartDateTime;  

SELECT CustomerName  
FROM SupportEvent  
WHERE StartDateTime = '2016-02-26';  

Indicazioni per l'uso di indici hashRecommendations for hash index use

Gli indici hash vengono usati principalmente per le ricerche di punti e non per le analisi di intervalli.Hash indexes are primarily used for point lookups and not for range scans.

Un indice hash è da preferirsi a un indice non cluster quando le query usano predicati di uguaglianza e la clausola WHERE esegue il mapping a tutte le colonne chiave dell'indice, come nell'esempio seguente:A hash index is preferable over a nonclustered index when queries use equality predicates, and the WHERE clause maps to all index key columns, as in the following example:

SELECT CustomerName 
FROM SupportEvent  
WHERE SupportEngineerName = 'Liz';

Indice a più colonneMulti-column index

Un indice a più colonne può essere un indice non cluster o un indice hash.A multi-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. Con l'istruzione SELECT seguente, solo l'indice non cluster risulterebbe utile per Query Optimizer:Given the following 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 richiede che la clausola WHERE specifichi un test di uguaglianza per ognuna delle colonne nella propria 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 query optimizer.

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

Tabella di riepilogo per il confronto degli scenari d'uso degli indiciSummary table to compare index use scenarios

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. significa che l'indice è in grado di soddisfare la richiesta in modo appropriato e No significa che non lo è.Yes means that the index can efficiently service the request, and No means that the index cannot efficiently satisfy the request.

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.)
1Yes 1 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

1 Per un indice non cluster ottimizzato per la memoria, non è necessaria la chiave completa per eseguire una ricerca nell'indice.1 For a memory-optimized Nonclustered index, the full key is not required to perform an index seek.

Gestione automatica dell'indice e delle statisticheAutomatic index and statistics management

Sfruttare le soluzioni, ad esempio la deframmentazione dell'indice adattativo, per gestire automaticamente la deframmentazione dell'indice e gli aggiornamenti delle statistiche per uno o più database.Leverage solutions such as Adaptive Index Defrag to automatically manage index defragmentation and statistics updates for one or more databases. Questa procedura sceglie automaticamente se ricompilare o riorganizzare un indice in base al relativo livello di frammentazione, tra gli altri parametri, e aggiornare le statistiche con una soglia lineare.This procedure automatically chooses whether to rebuild or reorganize an index according to its fragmentation level, amongst other parameters, and update statistics with a linear threshold.

Vedere ancheSee Also

Guida per la progettazione di indici di SQL Server SQL Server Index Design Guide
Indici hash per tabelle ottimizzate per la memoria Hash Indexes for Memory-Optimized Tables
Indice non cluster per tabelle ottimizzate per la memoria Nonclustered Indexes for Memory-Optimized Tables
Adaptive Index Defrag (Deframmentazione dell'indice adattativo)Adaptive Index Defrag