Freigeben über

Ermitteln der richtigen Bucketanzahl für Hashindizes

Sie müssen einen Wert für den BUCKET_COUNT Parameter angeben, wenn Sie die speicheroptimierte Tabelle erstellen. In diesem Thema werden Empfehlungen zum Ermitteln des geeigneten Werts für den BUCKET_COUNT Parameter erstellt. Wenn Sie die richtige Bucketanzahl nicht ermitteln können, verwenden Sie stattdessen einen nicht gruppierten Index. Ein falscher BUCKET_COUNT Wert, insbesondere einer, der zu niedrig ist, kann die Arbeitsauslastungsleistung sowie die Wiederherstellungszeit der Datenbank erheblich beeinträchtigen. Es ist besser, die Eimeranzahl zu überschätzen.

Doppelte Indexschlüssel können die Leistung mit einem Hashindex verringern, da die Schlüssel auf dieselbe Hashtable gehasht werden, was dazu führt, dass die Kette dieser Hashtable zunimmt.

Weitere Informationen zu nicht gruppierten Hashindizes finden Sie unter Hashindizes und Richtlinien für die Verwendung von Indizes für Memory-Optimized Tabellen.

Für jeden Hashindex einer speicheroptimierten Tabelle wird eine Hashtabelle zugewiesen. Die Größe der für einen Index zugewiesenen Hashtabelle wird durch den BUCKET_COUNT Parameter in CREATE TABLE (Transact-SQL) oder CREATE TYPE (Transact-SQL) angegeben. Die Bucketanzahl wird intern auf die nächste Zweierpotenz aufgerundet. Beispielsweise führt die Angabe einer Bucketanzahl von 300.000 zu einer tatsächlichen Bucketanzahl von 524.288.

Links zu einem Artikel und Video zur Bucketanzahl finden Sie unter Ermitteln der richtigen Bucketanzahl für Hashindizes (In-Memory OLTP).

Empfehlungen

In den meisten Fällen sollte die Anzahl der Buckets das 1- bis 2-fache der Anzahl unterschiedlicher Werte im Indexschlüssel betragen. Wenn der Indexschlüssel viele doppelte Werte enthält, gibt es im Durchschnitt mehr als 10 Zeilen für jeden Indexschlüsselwert, verwenden Sie stattdessen einen nicht gruppierten Index.

Möglicherweise können Sie nicht immer vorhersagen, wie viele Werte ein bestimmter Indexschlüssel haben oder haben wird. Die Leistung sollte akzeptabel sein, wenn der BUCKET_COUNT Wert innerhalb von 5 Mal der tatsächlichen Anzahl der Schlüsselwerte liegt.

Verwenden Sie Abfragen ähnlich wie in den folgenden Beispielen, um die Anzahl eindeutiger Indexschlüssel in vorhandenen Daten zu ermitteln:

Primärschlüssel und eindeutige Indizes

Da der Primärschlüsselindex eindeutig ist, entspricht die Anzahl der unterschiedlichen Werte im Schlüssel der Anzahl der Zeilen in der Tabelle. Verwenden Sie beispielsweise einen Primärschlüssel von (SalesOrderID, SalesOrderDetailID) in der Tabelle "Sales.SalesOrderDetail" in der AdventureWorks-Datenbank und führen Sie die folgende Abfrage aus, um die Anzahl der eindeutigen Primärschlüsselwerte zu berechnen. Diese entspricht der Anzahl der Zeilen in der Tabelle.

SELECT COUNT(*) AS [row count]   
FROM Sales.SalesOrderDetail

Diese Abfrage zeigt eine Zeilenanzahl von 121.317 an. Verwenden Sie eine Anzahl von Buckets von 240.000, wenn sich die Zeilenanzahl nicht erheblich ändert. Verwenden Sie eine Bucketanzahl von 480.000, wenn die Anzahl der Verkaufsaufträge in der Tabelle voraussichtlich auf das Vierfache anwachsen wird.

Nicht eindeutige Indizes

Geben Sie für andere Indizes, z. B. einen mehrspaltigen Index für (SpecialOfferID, ProductID), die folgende Abfrage aus, um die Anzahl eindeutiger Indexschlüsselwerte zu ermitteln:

SELECT COUNT(*) AS [SpecialOfferID_ProductID index key count]  
FROM   
   (SELECT DISTINCT SpecialOfferID, ProductID   
    FROM Sales.SalesOrderDetail) t

Diese Abfrage gibt eine Indexschlüsselanzahl für (SpecialOfferID, ProductID) von 484 zurück, die angibt, dass ein nicht gruppierter Index anstelle eines nicht gruppierten Hashindex verwendet werden soll.

Bestimmen der Anzahl der Duplikate

Um die durchschnittliche Anzahl doppelter Werte für einen Indexschlüsselwert zu ermitteln, dividieren Sie die Gesamtanzahl der Zeilen durch die Anzahl eindeutiger Indexschlüssel.

Für den Beispielindex für (SpecialOfferID, ProductID) führt dies zu 121317 / 484 = 251. Dies bedeutet, dass Indexschlüsselwerte einen Mittelwert von 251 haben und daher ein nicht gruppierter Index sein sollte.

Fehlerbehebung bei der Anzahl der Buckets

Verwenden Sie sys.dm_db_xtp_hash_index_stats (Transact-SQL) zum Beheben von Problemen mit der Bucketanzahl in speicheroptimierten Tabellen, um Statistiken zu den leeren Buckets und der Länge von Zeilenketten zu erhalten. Die folgende Abfrage kann verwendet werden, um Statistiken zu allen Hashindizes in der aktuellen Datenbank abzurufen. Die Abfrage kann mehrere Minuten dauern, wenn große Tabellen in der Datenbank vorhanden sind.

SELECT   
   object_name(hs.object_id) AS 'object name',   
   i.name as 'index name',   
   hs.total_bucket_count,  
   hs.empty_bucket_count,  
   floor((cast(empty_bucket_count as float)/total_bucket_count) * 100) AS 'empty_bucket_percent',  
   hs.avg_chain_length,   
   hs.max_chain_length  
FROM sys.dm_db_xtp_hash_index_stats AS hs   
   JOIN sys.indexes AS i   
   ON hs.object_id=i.object_id AND hs.index_id=i.index_id

Die beiden wichtigsten Indikatoren für die Gesundheit des Hash-Indexes sind:

empty_bucket_percent
empty_bucket_percent gibt die Anzahl der leeren Buckets im Hashindex an.

Wenn der empty_bucket_percent unter 10 Prozent liegt, ist die Anzahl der Buckets wahrscheinlich zu niedrig. Im Idealfall sollte die empty_bucket_percent 33 Prozent oder höher sein. Wenn die Bucketanzahl mit der Anzahl der Indexschlüsselwerte übereinstimmt, ist etwa 1/3 der Buckets aufgrund der Hashverteilung leer.

durchschnittliche_Kettenlänge
avg_chain_length gibt die durchschnittliche Länge der Zeilenketten in den Hash-Buckets an.

Wenn avg_chain_length größer als 10 ist und empty_bucket_percent größer als 10 Prozent ist, gibt es wahrscheinlich viele doppelte Indexschlüsselwerte, und ein nicht gruppierter Index wäre besser geeignet. Eine durchschnittliche Kettenlänge von 1 ist ideal.

Es gibt zwei Faktoren, die sich auf die Länge der Kette auswirken:

  1. Duplikate; Alle doppelten Zeilen sind Teil derselben Kette im Hashindex.

  2. Mehrere Schlüsselwerte werden demselben Bucket zugeordnet. Je niedriger die Bucketanzahl ist, desto mehr Buckets, denen mehrere Werte zugeordnet sind.

Betrachten Sie beispielsweise die folgende Tabelle und das folgende Skript, um Beispielzeilen in die Tabelle einzufügen:

CREATE TABLE [Sales].[SalesOrderHeader_test]  
(  
   [SalesOrderID] [uniqueidentifier] NOT NULL DEFAULT (newid()),  
   [OrderSequence] int NOT NULL,  
   [OrderDate] [datetime2](7) NOT NULL,  
   [Status] [tinyint] NOT NULL,  
  
PRIMARY KEY NONCLUSTERED HASH ([SalesOrderID]) WITH ( BUCKET_COUNT = 262144 ),  
INDEX IX_OrderSequence HASH (OrderSequence) WITH ( BUCKET_COUNT = 20000),  
INDEX IX_Status HASH ([Status]) WITH ( BUCKET_COUNT = 8),  
INDEX IX_OrderDate NONCLUSTERED ([OrderDate] ASC),  
)WITH ( MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_AND_DATA )  
GO  
  
DECLARE @i int = 0  
BEGIN TRAN  
WHILE @i < 262144  
BEGIN  
   INSERT Sales.SalesOrderHeader_test (OrderSequence, OrderDate, [Status]) VALUES (@i, sysdatetime(), @i % 8)  
   SET @i += 1  
END  
COMMIT  
GO

Das Skript fügt 262.144 Zeilen in die Tabelle ein. Er fügt eindeutige Werte in den Primärschlüsselindex und in IX_OrderSequence ein. Es fügt viele doppelte Werte in den Index IX_Status ein: Das Skript generiert nur 8 unterschiedliche Werte.

Die Ausgabe der BUCKET_COUNT Problembehandlungsabfrage lautet wie folgt:

Indexname total_bucket_count Leer-Eimer-Anzahl Leeranteilprozentsatz avg_chain_length maximale_Kettenlänge
IX_Statusanzeige 8 4 50 65536 65536
IX_Bestellfolge 32768 13 0 8 26
PK_SalesOrd_B14003C3F8FB3364 262144 96319 36 1 8

Berücksichtigen Sie die drei Hashindizes in dieser Tabelle:

  • IX_Status: 50 Prozent der Buckets sind leer, was gut ist. Die durchschnittliche Kettenlänge ist jedoch sehr hoch (65.536). Dies gibt eine große Anzahl doppelter Werte an. Daher ist die Verwendung eines nicht gruppierten Hashindex in diesem Fall nicht geeignet. Stattdessen sollte ein nicht gruppierter Index verwendet werden.

  • IX_OrderSequence: 0 Prozent der Buckets sind leer, was zu wenig ist. Darüber hinaus beträgt die durchschnittliche Kettenlänge 8. Da die Werte in diesem Index eindeutig sind, bedeutet dies, dass durchschnittlich 8 Werte jedem Bucket zugeordnet sind. Die Anzahl der Eimer sollte erhöht werden. Da der Indexschlüssel 262.144 eindeutige Werte aufweist, sollte die Bucketanzahl mindestens 262.144 sein. Wenn ein zukünftiges Wachstum erwartet wird, sollte die Zahl höher sein.

  • Primärschlüsselindex (PK__SalesOrder...): 36 Prozent der Container sind leer, was gut ist. Darüber hinaus beträgt die durchschnittliche Kettenlänge 1, was auch gut ist. Keine Änderung erforderlich.

Weitere Informationen zur Problembehandlung bei Speicheroptimierten Hashindizes finden Sie unter Problembehandlung allgemeiner Leistungsprobleme mit Memory-Optimized Hashindizes.

Detaillierte Überlegungen zur weiteren Optimierung

In diesem Abschnitt werden weitere Überlegungen zur Optimierung der Anzahl der Buckets beschrieben.

Um die beste Leistung für Hashindizes zu erzielen, ausgleichen Sie die Speichermenge, die der Hashtabelle zugeordnet ist, und die Anzahl der unterschiedlichen Werte im Indexschlüssel. Es gibt auch ein Gleichgewicht zwischen der Leistung von Punktabfragen und Tabellenscans.

  • Je höher der Bucketanzahlswert ist, desto mehr leere Buckets befinden sich im Index. Dies wirkt sich auf die Speicherauslastung (8 Bytes pro Bucket) und die Leistung von Tabellenscans aus, da jeder Bucket als Teil einer Tabellenüberprüfung gescannt wird.

  • Je niedriger die Bucketanzahl ist, desto mehr Werte werden einem einzelnen Bucket zugewiesen. Dadurch wird die Leistung für Punktsuche und Einfügungen verringert, da SQL Server möglicherweise mehrere Werte in einem einzigen Bucket durchlaufen muss, um den durch das Suchdedikat angegebenen Wert zu finden.

Wenn die Bucketanzahl deutlich niedriger ist als die Anzahl eindeutiger Indexschlüssel, werden den einzelnen Buckets viele Werte zugeordnet. Dadurch wird die Leistung der meisten DML-Vorgänge beeinträchtigt, insbesondere Punktsuchvorgänge (Nachschlagevorgänge einzelner Indexschlüssel) und Einfügevorgänge. So können Sie beispielsweise eine schlechte Performance bei SELECT-Abfragen sowie UPDATE- und DELETE-Vorgängen mit Gleichheitsprädikaten beobachten, die den Indexschlüsselspalten in der WHERE-Klausel entsprechen. Eine niedrige Bucketanzahl wirkt sich auch auf die Wiederherstellungszeit der Datenbank aus, da die Indizes beim Datenbankstart neu erstellt werden.

Doppelte Indexschlüsselwerte

Doppelte Werte können die Auswirkungen auf die Leistung von Hashkonflikten erhöhen. Dies ist in der Regel kein Problem, wenn jeder Indexschlüssel über eine geringe Anzahl von Duplikaten verfügt. Dies kann jedoch ein Problem sein, wenn die Diskrepanz zwischen der Anzahl der eindeutigen Indexschlüssel und der Anzahl der Zeilen in den Tabellen sehr groß wird.

Alle Zeilen mit demselben Indexschlüssel werden in dieselbe doppelte Kette eingefügt. Wenn sich mehrere Indexschlüssel aufgrund eines Hashkonflikts im selben Bucket befinden, müssen Indexscanner immer die vollständige Duplikatkette für den ersten Wert durchsuchen, bevor sie die erste Zeile finden können, die dem zweiten Wert entspricht. Doppelte Schlüssel erschweren auch die Garbage Collection, um die Zeile zu finden. Wenn beispielsweise 1.000 Duplikate für jeden Schlüssel vorhanden sind und eine der Zeilen gelöscht wird, muss der Garbage Collector die Kette von 1.000 Duplikaten durchsuchen, um die Verknüpfung der Zeile vom Index aufzuheben. Dies gilt auch dann, wenn die Abfrage, die den Löschvorgang gefunden hat, einen effizienteren Index (ein Primärschlüsselindex) zum Suchen der Zeile verwendet hat, da der Garbage Collector die Verknüpfung von jedem Index aufheben muss.

Bei Hashindizes gibt es zwei Möglichkeiten, die durch doppelte Indexschlüsselwerte verursachte Arbeit zu reduzieren:

  • Verwenden Sie stattdessen einen nicht gruppierten Index. Sie können die Duplikate verringern, indem Sie dem Indexschlüssel Spalten hinzufügen, ohne dass Änderungen an der Anwendung erforderlich sind.

  • Geben Sie eine extrem hohe Anzahl an Buckets für den Index an. Beispielsweise 20 bis 100 Mal die Anzahl eindeutiger Indexschlüssel. Dadurch werden Hashkonflikte reduziert.

Kleine Tabellen

Bei kleineren Tabellen ist die Speicherauslastung in der Regel kein Problem, da die Größe des Indexes im Vergleich zur Gesamtgröße der Datenbank gering ist.

Sie müssen jetzt eine Auswahl treffen, basierend auf der Art der leistung, die Sie benötigen:

  • Wenn die leistungskritischen Vorgänge im Index überwiegend Punktabfragen und/oder Einfügeoperationen sind, wäre eine höhere Bucketanzahl geeignet, um die Wahrscheinlichkeit von Hashkonflikten zu verringern. Es wäre am besten, die Anzahl der Zeilen mindestens zu verdreifachen oder sogar noch mehr.

  • Wenn vollständige Indexüberprüfungen die vorherrschenden leistungskritischen Vorgänge sind, verwenden Sie eine Bucketanzahl, die nahe der tatsächlichen Anzahl der Indexschlüsselwerte liegt.

Große Tische

Bei großen Tabellen könnte die Speicherauslastung zu einem Problem werden. Bei einer 250 Millionen Zeilentabelle mit 4 Hashindizes, die jeweils eine Bucketanzahl von einer Milliarde aufweisen, beträgt der Aufwand für die Hashtabellen 4 Indizes * 1 Milliarden Buckets * 8 Bytes = 32 Gigabyte Arbeitsspeicherauslastung. Bei der Auswahl einer Bucketanzahl von 250 Millionen für jeden Index beträgt der Gesamtaufwand für die Hashtabellen 8 Gigabyte. Beachten Sie, dass dies zusätzlich zu den 8 Bytes Speicherverbrauch kommt, die jeder Index zu jeder Zeile hinzufügt, was in diesem Szenario 8 Gigabyte ergibt (4 Indizes * 8 Bytes * 250 Millionen Zeilen).

Vollständige Tabellenscans befinden sich in der Regel nicht im leistungskritischen Pfad für OLTP-Workloads. Daher besteht die Wahl zwischen der Speicherauslastung und der Leistung von Punktabfrage- und Einfügevorgängen.

  • Wenn die Speicherauslastung ein Problem darstellt, wählen Sie eine Bucketanzahl in der Nähe der Anzahl der Indexschlüsselwerte aus. Die Bucketanzahl sollte nicht wesentlich niedriger sein als die Anzahl der Indexschlüsselwerte, da sich dies auf die meisten DML-Vorgänge auswirkt, sowie die Zeit, die zum Wiederherstellen der Datenbank nach dem Serverneustart benötigt wird.

  • Bei der Optimierung der Leistung für Punktsuchvorgänge wäre eine erhöhte Anzahl von Buckets auf das Zwei- oder sogar Dreifache der Anzahl der eindeutigen Indexwerte angemessen. Eine höhere Bucketanzahl würde eine erhöhte Speicherauslastung und eine Erhöhung der Zeit bedeuten, die für einen vollständigen Indexscan erforderlich ist.

Siehe auch

Indizes für Memory-Optimized Tabellen

  • Last updated on