Indizes für speicheroptimierte Tabellen
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) haben eine Beschränkung von 8 Indizes pro speicheroptimierter Tabelle oder Tabellentyp. Ab SQL Server 2017 (14.x) und in Azure SQL-Datenbank gibt es keine Begrenzung mehr für die spezifische Anzahl von Indizes für speicheroptimierte Tabellen und Tabellentypen.
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.Customers
in 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
- Einer Ungleichheit:
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 | Arbeitsspeicheroptimiert, hash |
Arbeitsspeicheroptimiert, 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 Ungleichheits- und Bereichsprädikaten (>, <, <=, >=, BETWEEN ). |
Ohne (Führt zu einem Indexscan.) |
Ja1 | Ja |
Abrufen von Zeilen in einer Sortierreihenfolge, die der Indexdefinition entspricht. | No | Ja | Ja |
Abrufen von Zeilen in einer Sortierreihenfolge, die der umgekehrten Indexdefinition entspricht. | No | Nein | Ja |
1 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