Indizes für speicheroptimierte Tabellen

Artikel
04/26/2023

Gilt für:SQL Server Azure SQL-Datenbank Azure SQL Managed Instance

Alle speicheroptimierten Tabellen müssen mindestens einen Index enthalten, da die Zeilen durch die Indizes miteinander verbunden werden. Für eine speicheroptimierte Tabelle wird jeder Index auch speicheroptimiert. Ein Index für eine speicheroptimierte Tabelle unterscheidet sich auf vielfältige Weise von einem herkömmlichen Index für eine datenträgerbasierte Tabelle:

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. Für einen der verfügbaren Indextypen gibt es Indexseiten. Diese Indextypen werden jedoch anders gespeichert als Indizes für datenträgerbasierte Tabellen. Sie lassen nicht den herkömmlichen Fragmentierungstyp innerhalb einer Seite anwachsen, sodass sie keinen Füllfaktor haben.
Änderungen, die bei der Datenbearbeitung an Indizes in speicheroptimierten Tabellen vorgenommen werden, werden niemals auf den Datenträger geschrieben. Nur die Datenzeilen und Änderungen an den Daten werden in das Transaktionsprotokoll geschrieben.
Speicheroptimierte Indizes werden neu erstellt, wenn die Datenbank wieder online geschaltet wird.

Alle Indizes in speicheroptimierten Tabellen werden basierend auf den Indexdefinitionen bei der Datenbankwiederherstellung erstellt.

Bei dem Index muss es sich um einen der folgenden handeln:

Hashindex
Nicht gruppierter speicheroptimierter Index (d.h. die interne Standardstruktur einer B-Struktur)

Hashindizes werden unter Hashindizes für speicheroptimierte Tabellen ausführlicher erläutert.
Nicht gruppierte Indizes werden unter Nicht gruppierte Indizes für speicheroptimierte Tabellen ausführlicher behandelt.
Columnstore -Indizes werden in einem anderen Artikelbehandelt.

Syntax für speicheroptimierte Indizes

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.

Für eine Deklaration mit dem Standard DURABILITY = SCHEMA_AND_DATA muss die speicheroptimierte Tabelle einen Primärschlüssel haben. Die PRIMARY KEY NONCLUSTERED-Klausel in der folgenden CREATE TABLE-Anweisung erfüllt zwei Anforderungen:

Sie stellt einen Index bereit, um die Mindestanforderung von einem Index in der CREATE TABLE-Anweisung zu erfüllen.

Sie stellt den Primärschlüssel bereit, der für die SCHEMA_AND_DATA-Klausel erforderlich ist.

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

Hinweis

SQL Server 2014 (12.x) und SQL Server 2016 (13.x) weisen eine Beschränkung von 8 Indizes pro speicheroptimierter Tabelle oder Tabellentyp auf. Ab SQL Server 2017 (14.x) und in der Azure SQL-Datenbank gibt es keine Beschränkung mehr für die Anzahl der Indizes, die für speicheroptimierte Tabellen und Tabellentypen spezifisch sind.

Codebeispiel für die Syntax

Dieser Unterabschnitt enthält einen Transact-SQL-Codeblock, der die Syntax zum Erstellen von verschiedenen Indizes für eine speicheroptimierte Tabelle darstellt. Der Code veranschaulicht Folgendes:

Erstellen Sie eine speicheroptimierte Tabelle.
Verwenden Sie ALTER TABLE-Anweisungen, um zwei Indizes hinzuzufügen.

Fügen Sie einige Zeilen Daten ein (INSERT).

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üsselwerte

Durch doppelte Werte für einen Indexschlüssel kann die Leistung speicheroptimierter Tabellen reduziert werden. Duplikate für das System durchlaufen Eingangsketten für die meisten Lese- und Schreibvorgänge für Indizes. Wenn eine Kette mehr als 100 doppelte Einträge umfasst, kann die Leistungsminderung messbar werden.

Doppelte Hashwerte

Dieses Problem ist bei Hashindizes stärker sichtbar. Hashindizes sind aufgrund der folgenden Aspekte stärker beeinträchtigt:

Die niedrigeren Kosten pro Vorgang für Hashindizes
Die Interferenz großer doppelter Ketten mit der Hashkollisionskette

Um die Duplizierung in einem Index zu reduzieren, können Sie folgende Anpassungen vornehmen:

Verwenden Sie einen nicht gruppierten Index.
Fügen Sie am Ende des Indexschlüssels zusätzliche Spalten hinzu, um die Anzahl von Duplikaten zu verringern.
- Sie können beispielsweise Spalten hinzufügen, die auch im Primärschlüssel enthalten sind.

Weitere Informationen zu Hashkollisionen finden Sie unter Hashindizes für speicheroptimierte Tabellen.

Verbesserungsbeispiel

Es folgt ein Beispiel dafür, wie Sie ineffiziente Leistung in Ihrem Index vermeiden können.

Angenommen, Sie haben eine Tabelle Customers mit einem Primärschlüssel für CustomerId und einem Index in der Spalte CustomerCategoryID. In der Regel sind in einer bestimmten Kategorie viele Kunden enthalten. Daher gibt es viele doppelte Werte für „CustomerCategoryID“ in einem bestimmten Schlüssel des Indexes.

In diesem Szenario hat sich die Verwendung eines nicht gruppierten Index für (CustomerCategoryID, CustomerId) bewährt. 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. Daher wird weder durch die doppelten „CustomerCategoryID“-Werte noch durch die zusätzliche Spalte im Index eine ineffiziente Indexwartung verursacht.

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

Vergleichen des Verwendungszeitpunkts für jeden Indextyp

Die Art Ihrer spezifischen Abfragen bestimmt, welche Art von Index die beste Wahl ist.

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.

Empfehlungen für die Verwendung nicht gruppierter Indizes

Ein nicht gruppierter Index ist gegenüber einem Hashindex zu bevorzugen, wenn:

Abfragen eine ORDER BY-Klausel für die indizierte Spalte enthalten.
Bei Abfragen nur die führenden Spalten eines mehrspaltigen Indexes getestet werden.
Abfragen testen die indizierte Spalte mithilfe einer WHERE Klausel mit:
- Einer Ungleichheit: WHERE StatusCode != 'Done'
- Einem Wertebereichsscan: WHERE Quantity >= 100

In allen folgenden SELECT-Anweisungen wird ein nicht gruppierter Index gegenüber einem Hashindex bevorzugt:

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 Hashindizes

Hashindizes werden in erster Linie für gezielte Suchvorgänge und nicht für Bereichsscans verwendet.

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:

SELECT CustomerName 
FROM SupportEvent  
WHERE SupportEngineerName = 'Liz';

Mehrspaltiger Index

Ein mehrspaltiger Index könnte ein nicht gruppierter Index oder ein Hashindex sein. Nehmen wir an, die Indexspalten sind „col1“ und „col2“. Gemäß der folgenden SELECT-Anweisung wäre nur der nicht gruppierte Index für den Abfrageoptimierer nützlich:

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. Ansonsten ist der Hashindex für den Abfrageoptimierer nicht sinnvoll.

Genauso wenig ist der Indextyp nützlich, wenn die WHERE-Klausel nur die zweite Spalte im Indexschlüssel angibt.

Zusammenfassungstabelle für den Vergleich der Indexverwendungsszenarien

Die folgende Tabelle enthält alle Vorgänge, die von den verschiedenen Indextypen unterstützt werden. Ja bedeutet, dass der Index die Anforderung effizient verarbeiten kann, und Nein bedeutet, dass der Index die Anforderung nicht effizient erfüllen kann.

Vorgang	Speicheroptimierte, Hash	Speicheroptimierte, Nicht gruppiert	Datenträgerbasiert, (nicht) gruppiert
Indexscan, alle Tabellenzeilen abrufen.	Ja	Ja	Ja
Indexsuche nach Gleichheitsprädikaten (=).	Ja. (Vollständiger Schlüssel ist erforderlich.)	Ja	Ja
Indexsuche nach Ungleichheiten und Bereichs-Prädikaten (>, <, <=, >=, `BETWEEN`).	Ohne (Führt zu einem Indexscan.)	Ja¹	Ja
Abrufen von Zeilen in einer Sortierreihenfolge, die der Indexdefinition entspricht.	Nein	Ja	Ja
Abrufen von Zeilen in einer Sortierreihenfolge, die der umgekehrten Indexdefinition entspricht.	Nein	Nein	Ja

¹ Für einen nicht gruppierten speicheroptimierten Index ist der vollständige Schlüssel nicht erforderlich, um eine Indexsuche auszuführen.

Automatische Verwaltung von Index und Statistiken

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

Weitere Informationen

Handbuch zum SQL Server-Indexentwurf
Hashindizes für speicheroptimierte Tabellen
Nicht gruppierter Indizes für speicheroptimierte Tabellen
Adaptive Indexdefragmentierung