Indizes für speicheroptimierte TabellenIndexes on Memory-Optimized Tables

Anwendungsbereich:Applies to: JaSQL ServerSQL Server (alle unterstützten Versionen) yesSQL ServerSQL Server (all supported versions) JaAzure SQL-DatenbankAzure SQL DatabaseYesAzure SQL-DatenbankAzure SQL DatabaseAnwendungsbereich:Applies to: JaSQL ServerSQL Server (alle unterstützten Versionen) yesSQL ServerSQL Server (all supported versions) JaAzure SQL-DatenbankAzure SQL DatabaseYesAzure SQL-DatenbankAzure SQL Database

Alle speicheroptimierten Tabellen müssen mindestens einen Index enthalten, da die Zeilen durch die Indizes miteinander verbunden werden.All memory-optimized tables must have at least one index, because it is the indexes that connect the rows together. Für eine speicheroptimierte Tabelle wird jeder Index auch speicheroptimiert.On a memory-optimized table, every index is also memory-optimized. Ein Index für eine speicheroptimierte Tabelle unterscheidet sich auf vielfältige Weise von einem herkömmlichen Index für eine datenträgerbasierte Tabelle:There are several ways in which an index on a memory-optimized table differs from a traditional index on a disk-base table:

  • Datenzeilen werden nicht in Seiten gespeichert, sodass es keine Sammlung von Seiten bzw. Erweiterungen, Partitionen oder Zuordnungseinheiten gibt, auf die zum Abrufen aller Seiten einer Tabelle verwiesen werden kann.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. Für einen der verfügbaren Indextypen gibt es Indexseiten. Diese Indextypen werden jedoch anders gespeichert als Indizes für datenträgerbasierte Tabellen.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. Sie lassen nicht den herkömmlichen Fragmentierungstyp innerhalb einer Seite anwachsen, sodass sie keinen Füllfaktor haben.They do not accrue the traditional type of fragmentation within a page, so they have no fillfactor.
  • Änderungen, die bei der Datenbearbeitung an Indizes in speicheroptimierten Tabellen vorgenommen werden, werden niemals auf den Datenträger geschrieben.Changes made to indexes on memory-optimized tables during data manipulation are never written to disk. Nur die Datenzeilen und Änderungen an den Daten werden in das Transaktionsprotokoll geschrieben.Only the data rows, and changes to the data, are written to the transaction log.
  • Speicheroptimierte Indizes werden neu erstellt, wenn die Datenbank wieder online geschaltet wird.Memory-optimized indexes are rebuilt when the database is brought back online.

Alle Indizes in speicheroptimierten Tabellen werden basierend auf den Indexdefinitionen bei der Datenbankwiederherstellung erstellt.All indexes on memory-optimized tables are created based on the index definitions during database recovery.

Bei dem Index muss es sich um einen der folgenden handeln:The index must be one of the following:

  • HashindexHash index
  • Nicht gruppierter speicheroptimierter Index (d.h. die interne Standardstruktur einer B-Struktur)Memory-optimized Nonclustered index (meaning the default internal structure of a B-tree)

Hashindizes werden unter Hashindizes für speicheroptimierte Tabellen ausführlicher erläutert.Hash indexes are discussed in more detail in Hash Indexes for Memory-Optimized Tables.
Nicht gruppierte Indizes werden unter Nicht gruppierte Indizes für speicheroptimierte Tabellen ausführlicher behandelt.Nonclustered indexes are discussed in more detail in Nonclustered Index for Memory-Optimized Tables.
Columnstore -Indizes werden in einem anderen Artikelbehandelt.Columnstore indexes are discussed in another article.

Syntax für speicheroptimierte IndizesSyntax for memory-optimized indexes

Jede CREATE TABLE-Anweisung für eine speicheroptimierte Tabelle muss einen Index enthalten, entweder explizit über einen INDEX oder implizit über eine PRIMAY KEY- oder UNIQUE-Einschränkung.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.

Für eine Deklaration mit dem Standard DURABILITY = SCHEMA_AND_DATA muss die speicheroptimierte Tabelle einen Primärschlüssel enthalten.To be declared with the default DURABILITY = SCHEMA_AND_DATA, the memory-optimized table must have a primary key. Die PRIMARY KEY NONCLUSTERED-Klausel in der folgenden CREATE TABLE-Anweisung erfüllt zwei Anforderungen:The PRIMARY KEY NONCLUSTERED clause in the following CREATE TABLE statement satisfies two requirements:

  • Sie stellt einen Index bereit, um die Mindestanforderung von einem Index in der CREATE TABLE-Anweisung zu erfüllen.Provides an index to meet the minimum requirement of one index in the CREATE TABLE statement.

  • Sie stellt den Primärschlüssel bereit, der für die SCHEMA_AND_DATA-Klausel erforderlich ist.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);  
    

Hinweis

Für SQL Server 2014 (12.x)SQL Server 2014 (12.x) und SQL Server 2016 (13.x)SQL Server 2016 (13.x) besteht ein Limit von 8 Indizes pro speicheroptimierte Tabelle oder Tabellentyp.SQL Server 2014 (12.x)SQL Server 2014 (12.x) and SQL Server 2016 (13.x)SQL Server 2016 (13.x) have a limit of 8 indexes per memory-optimized table or table type. Ab SQL Server 2017 (14.x)SQL Server 2017 (14.x) und in Azure SQL-DatenbankAzure SQL Database gibt es keine Begrenzung mehr für die spezifische Anzahl von Indizes für speicheroptimierte Tabellen und Tabellentypen.Starting with SQL Server 2017 (14.x)SQL Server 2017 (14.x) and in Azure SQL-DatenbankAzure SQL Database, there is no longer a limit on the number of indexes specific to memory-optimized tables and table types.

Codebeispiel für die SyntaxCode sample for syntax

Dieser Unterabschnitt enthält einen Transact-SQL-Codeblock, der die Syntax zum Erstellen von verschiedenen Indizes für eine speicheroptimierte Tabelle darstellt.This subsection contains a Transact-SQL code block that demonstrates the syntax to create various indexes on a memory-optimized table. Der Code veranschaulicht Folgendes:The code demonstrates the following:

  1. Erstellen Sie eine speicheroptimierte Tabelle.Create a memory-optimized table.

  2. Verwenden Sie ALTER TABLE-Anweisungen, um zwei Indizes hinzuzufügen.Use ALTER TABLE statements to add two indexes.

  3. Fügen Sie einige Zeilen Daten ein (INSERT).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 
    

Doppelte IndexschlüsselwerteDuplicate index key values

Durch doppelte Werte für einen Indexschlüssel kann die Leistung speicheroptimierter Tabellen reduziert werden.Duplicate values for an index key might reduce the performance of memory-optimized tables. Duplikate für das System durchlaufen Eingangsketten für die meisten Lese- und Schreibvorgänge für Indizes.Duplicates for the system to traverse entry chains for most index read and write operations. Wenn eine Kette mehr als 100 doppelte Einträge umfasst, kann die Leistungsminderung messbar werden.When a chain of duplicate entries exceeds 100 entries, the performance degradation can become measurable.

Doppelte HashwerteDuplicate hash values

Dieses Problem ist bei Hashindizes stärker sichtbar.This problem is more visible in the case of hash indexes. Hashindizes sind aufgrund der folgenden Aspekte stärker beeinträchtigt:Hash indexes suffer more due to the following considerations:

  • Die niedrigeren Kosten pro Vorgang für HashindizesThe lower cost per operation for hash indexes.
  • Die Interferenz großer doppelter Ketten mit der HashkollisionsketteThe interference of large duplicate chains with the hash collision chain.

Um die Duplizierung in einem Index zu reduzieren, können Sie folgende Anpassungen vornehmen:To reduce duplication in an index, try the following adjustments:

  • Verwenden Sie einen nicht gruppierten Index.Use a nonclustered index.
  • Fügen Sie am Ende des Indexschlüssels zusätzliche Spalten hinzu, um die Anzahl von Duplikaten zu verringern.Add additional columns to the end of the index key, to reduce the number of duplicates.
    • Sie können beispielsweise Spalten hinzufügen, die auch im Primärschlüssel enthalten sind.For example, you could add columns that are also in the primary key.

Weitere Informationen zu Hashkollisionen finden Sie unter Hashindizes für speicheroptimierte Tabellen.For more information about hash collisions, see Hash Indexes for Memory-Optimized Tables.

VerbesserungsbeispielExample improvement

Es folgt ein Beispiel dafür, wie Sie ineffiziente Leistung in Ihrem Index vermeiden können.Here is an example of how to avoid any performance inefficiency in your index.

Angenommen, Sie haben eine Tabelle Customers mit einem Primärschlüssel für CustomerId und einem Index in der Spalte CustomerCategoryID.Consider a Customers table that has a primary key on CustomerId, and has an index on column CustomerCategoryID. In der Regel sind in einer bestimmten Kategorie viele Kunden enthalten.Typically there will be many customers in a given category. Daher gibt es viele doppelte Werte für „CustomerCategoryID“ in einem bestimmten Schlüssel des Indexes.Thus there will be many duplicate values for CustomerCategoryID inside a given key of the index.

In diesem Szenario hat sich die Verwendung eines nicht gruppierten Index für (CustomerCategoryID, CustomerId) bewährt.In this scenario, the best practice is to use a nonclustered index on (CustomerCategoryID, CustomerId). Dieser Index kann für Abfragen verwendet werden, die ein Prädikat unter Einbeziehung von CustomerCategoryID verwenden, doch enthält der Indexschlüssel keine Duplikate.This index can be used for queries that use a predicate involving CustomerCategoryID, yet the index key does not contain duplication. Daher wird weder durch die doppelten „CustomerCategoryID“-Werte noch durch die zusätzliche Spalte im Index eine ineffiziente Indexwartung verursacht.Therefore, no inefficiencies in index maintenance are cause by either the duplicate CustomerCategoryID values, or by the extra column in the index.

Die folgende Abfrage zeigt die durchschnittliche Anzahl doppelter Indexschlüsselwerte für CustomerCategoryID in der Tabelle Sales.Customersin der Beispieldatenbank 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

Um die durchschnittliche Anzahl der Indexschlüsselduplikate für Ihre eigene Tabelle und den Index zu evaluieren, ersetzen Sie Sales.Customers durch Ihren Tabellennamen und CustomerCategoryID durch die Liste der Indexschlüsselspalten.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.

Vergleichen des Verwendungszeitpunkts für jeden IndextypComparing when to use each index type

Die Art Ihrer spezifischen Abfragen bestimmt, welche Art von Index die beste Wahl ist.The nature of your particular queries determines which type of index is the best choice.

Beim Implementieren speicheroptimierter Tabellen in einer vorhandenen Anwendung gilt die allgemeine Empfehlung, mit nicht gruppierten Indizes zu beginnen, da ihre Funktionen mehr den Funktionen herkömmlicher gruppierter und nicht gruppierter Indizes für datenträgerbasierte Tabellen ähneln.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.

Empfehlungen für die Verwendung nicht gruppierter IndizesRecommendations for nonclustered index use

Ein nicht gruppierter Index ist gegenüber einem Hashindex zu bevorzugen, wenn:A nonclustered index is preferable over a hash index when:

  • Abfragen eine ORDER BY-Klausel für die indizierte Spalte enthalten.Queries have an ORDER BY clause on the indexed column.
  • Bei Abfragen nur die führenden Spalten eines mehrspaltigen Indexes getestet werden.Queries where only the leading column(s) of a multi-column index is tested.
  • Abfragen die indizierte Spalte testen mithilfe einer WHERE-Klausel mit:Queries test the indexed column by use of a WHERE clause with:
    • Einer Ungleichheit: WHERE StatusCode != 'Done'An inequality: WHERE StatusCode != 'Done'
    • Einem Wertebereichsscan: WHERE Quantity >= 100A value range scan: WHERE Quantity >= 100

In allen folgenden SELECT-Anweisungen wird ein nicht gruppierter Index gegenüber einem Hashindex bevorzugt: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 StartDateTime, CustomerName  
FROM SupportEvent  
ORDER BY StartDateTime DESC; -- ASC would cause a scan.

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

Empfehlungen für die Verwendung von HashindizesRecommendations for hash index use

Hashindizes werden in erster Linie für gezielte Suchvorgänge und nicht für Bereichsscans verwendet.Hash indexes are primarily used for point lookups and not for range scans.

Ein Hashindex ist gegenüber einem nicht gruppierten Index vorzuziehen, wenn Abfragen Gleichheitsprädikate, verwenden, und die WHERE-Klausel wird allen Indexschlüsselspalten zugeordnet, wie im folgenden Beispiel gezeigt wird: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';

Mehrspaltiger IndexMulti-column index

Ein mehrspaltiger Index könnte ein nicht gruppierter Index oder ein Hashindex sein.A multi-column index could be a nonclustered index or a hash index. Nehmen wir an, die Indexspalten sind „col1“ und „col2“.Suppose the index columns are col1 and col2. Gemäß der folgenden SELECT-Anweisung wäre nur der nicht gruppierte Index für den Abfrageoptimierer nützlich: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';  

Der Hashindex benötigt die WHERE-Klausel, um einen Gleichheitstest für die einzelnen Spalten im Schlüssel anzugeben.The hash index needs the WHERE clause to specify an equality test for each of the columns in its key. Ansonsten ist der Hashindex für den Abfrageoptimierer nicht sinnvoll.Else the hash index is not useful to the query optimizer.

Genauso wenig ist der Indextyp nützlich, wenn die WHERE-Klausel nur die zweite Spalte im Indexschlüssel angibt.Neither index type is useful if the WHERE clause specifies only the second column in the index key.

Zusammenfassungstabelle für den Vergleich der IndexverwendungsszenarienSummary table to compare index use scenarios

Die folgende Tabelle enthält alle Vorgänge, die von den verschiedenen Indextypen unterstützt werden.The following table lists all operations that are supported by the different index types. Ja bedeutet, dass der Index die Anforderung effizient verarbeiten kann, und Nein bedeutet, dass der Index die Anforderung nicht effizient erfüllen kann.Yes means that the index can efficiently service the request, and No means that the index cannot efficiently satisfy the request.

VorgangOperation Speicheroptimiert,Memory-optimized,
hashhash
Speicheroptimiert,Memory-optimized,
Nicht gruppiertnonclustered
Datenträgerbasiert,Disk-based,
(nicht) gruppiert(non)clustered
Indexscan, alle Tabellenzeilen abrufen.Index Scan, retrieve all table rows. JaYes JaYes JaYes
Indexsuche nach Gleichheitsprädikaten (=).Index seek on equality predicates (=). JaYes
(Vollständiger Schlüssel ist erforderlich.)(Full key is required.)
JaYes JaYes
Indexsuche nach Ungleichheits- und BereichsprädikatenIndex seek on inequality and range predicates
(>, <, <=, >=, BETWEEN).(>, <, <=, >=, BETWEEN).
NeinNo
(Führt zu einem Indexscan.)(Results in an index scan.)
Ja1Yes 1 JaYes
Abrufen von Zeilen in einer Sortierreihenfolge, die der Indexdefinition entspricht.Retrieve rows in a sort order that matches the index definition. NeinNo JaYes JaYes
Abrufen von Zeilen in einer Sortierreihenfolge, die der umgekehrten Indexdefinition entspricht.Retrieve rows in a sort-order that matches the reverse of the index definition. NeinNo NeinNo JaYes
       

1 Für einen nicht gruppierten speicheroptimierten Index ist der vollständige Schlüssel nicht erforderlich, um eine Indexsuche auszuführen.1 For a memory-optimized Nonclustered index, the full key is not required to perform an index seek.

Automatische Verwaltung von Index und StatistikenAutomatic index and statistics management

Nutzen Sie Lösungen wie Adaptive Index Defrag, um die Indexdefragmentierung und das Aktualisieren der Statistiken für eine oder mehrere Datenbanken automatisch zu verwalten.Leverage solutions such as Adaptive Index Defrag to automatically manage index defragmentation and statistics updates for one or more databases. Dieser Vorgang entscheidet unter anderem anhand des Fragmentierungsgrads automatisch, ob ein Index neu organisiert oder neu erstellt wird und aktualisiert Statistiken mit einem linearen Schwellenwert.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.

Weitere RessourcenSee Also

Handbuch zum SQL Server Indexentwurf SQL Server Index Design Guide
Hashindizes für speicheroptimierte Tabellen Hash Indexes for Memory-Optimized Tables
Nicht gruppierte Indizes für speicheroptimierte Tabellen Nonclustered Indexes for Memory-Optimized Tables
Adaptive IndexdefragmentierungAdaptive Index Defrag