Troubleshooting Hash Indexes for Memory-Optimized Tables (Behandlung von Problemen bei Hashindizes für speicheroptimierte Tabellen)

Artikel
04/26/2023

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

Voraussetzung

Wichtige Kontextinformationen für das Verständnis dieser Artikel sind verfügbar unter:

Praktische Zahlen

Wenn Sie einen Hashindex für eine speicheroptimierte Tabelle erstellen, muss die Anzahl der Buckets beim Erstellen angegeben werden. In den meisten Fällen sollte die Bucketanzahl das Ein- bis Zweifache der Anzahl eindeutiger Werte im Indexschlüssel betragen.

Auch wenn BUCKET_COUNT geringfügig unter oder über dem bevorzugten Bereich liegt, ist die Leistung des Hashindexes wahrscheinlich tolerierbar oder akzeptabel. Legen Sie BUCKET_COUNT für Ihren Hashindex auf eine Anzahl fest, die in etwa der Anzahl der Zeilen entspricht, die die speicheroptimierte Tabelle nach Ihrer Einschätzung haben wird.
Angenommen, Ihre wachsende Tabelle hat 2.000.000 Zeilen, aber die Vorhersage ist, dass sie 10 Mal auf 20.000.000 Zeilen vergrößert wird. Beginnen Sie mit einer Bucketanzahl, die dem 10-Fachen der Zeilenanzahl in der Tabelle entspricht. Dieses bietet Ihnen Raum für eine größere Anzahl von Zeilen.

Im Idealfall würden Sie die Bucketanzahl heraufsetzen, wenn die Zeilenanzahl die anfängliche Bucketanzahl erreicht.
Auch wenn die Anzahl der Zeilen zu fünfmal größer als die Bucketanzahl wird, ist die Leistung in den meisten Situationen immer noch gut.

Angenommen, ein Hashindex verfügt über 10.000.000 eindeutige Schlüsselwerte.

Die Bucketanzahl von 2.000.000 wäre der niedrigste akzeptable Wert. Das Ausmaß der Leistungseinbußen könnte tolerierbar sein.

Zu viele duplizierte Werte im Index?

Wenn die Hash-indizierten Werte einen hohen Anteil von Duplikaten aufweisen, werden die Hashbuckets durch längere Ketten beeinträchtigt.

Angenommen, Sie nutzen die gleiche SupportEvent-Tabelle aus dem früheren T-SQL-Syntax-Codeblock. Der folgende T-SQL-Code veranschaulicht, wie Sie das Verhältnis aller Werte zu eindeutigen Werten suchen und anzeigen können:

-- Calculate ratio of:  Rows / Unique_Values.  
DECLARE @allValues float(8) = 0.0, @uniqueVals float(8) = 0.0;  
  
SELECT @allValues = Count(*) FROM SupportEvent;  
  
SELECT @uniqueVals = Count(*) FROM  
  (SELECT DISTINCT SupportEngineerName  
      FROM SupportEvent) as d;  
  
    -- If (All / Unique) >= 10.0, use a nonclustered index, not a hash.   
SELECT Cast((@allValues / @uniqueVals) as float) as [All_divby_Unique];  
go

Ein Verhältnis von 10,0 oder höher bedeutet, dass ein Hash ein schlechter Indextyp sein würde. Verwenden Sie stattdessen einen nicht gruppierten Index.

Problembehandlung bei Hashindex-Bucketanzahl

Dieser Abschnitt beschreibt die Problembehandlung hinsichtlich der Bucketanzahl für Ihren Hashindex.

Überwachen der Statistiken für Ketten und leere Buckets

Sie können die statistische Integrität Ihrer Hashindizes überwachen, indem Sie die folgende T-SQL SELECT-Anweisung ausführen. Die SELECT-Anweisung verwendet die Datenverwaltungsansicht (DMV, Data Management View) mit dem Namen sys.dm_db_xtp_hash_index_stats.

SELECT  
  QUOTENAME(SCHEMA_NAME(t.schema_id)) + N'.' + QUOTENAME(OBJECT_NAME(h.object_id)) as [table],   
  i.name                   as [index],   
  h.total_bucket_count,  
  h.empty_bucket_count,  
    
  FLOOR((  
    CAST(h.empty_bucket_count as float) /  
      h.total_bucket_count) * 100)  
                            as [empty_bucket_percent],  
  h.avg_chain_length,   
  h.max_chain_length  
FROM  
        sys.dm_db_xtp_hash_index_stats  as h   
  JOIN sys.indexes                     as i  
          ON h.object_id = i.object_id  
          AND h.index_id  = i.index_id  
JOIN sys.memory_optimized_tables_internal_attributes ia ON h.xtp_object_id=ia.xtp_object_id
JOIN sys.tables t on h.object_id=t.object_id
WHERE ia.type=1
ORDER BY [table], [index];

Vergleichen Sie die SELECT-Ergebnisse mit den folgenden statistischen Richtlinien:

Leere Buckets:
- 33 % ist ein guter Zielwert, aber ein größerer Prozentsatz (sogar 90 %) ist in der Regel in Ordnung.
- Wenn die Bucketanzahl der Anzahl der eindeutigen Schlüsselwerte entspricht, sind etwa 33 % der Buckets leer.
- Ein Wert unter 10 % ist zu niedrig.
Ketten innerhalb von Buckets:
- Eine durchschnittliche Kettenlänge von 1 ist ideal für den Fall, dass es keine doppelten Indexschlüsselwerte gibt. Kettenlängen bis zu 10 sind üblicherweise brauchbar.
- Wenn die durchschnittliche Kettenlänge größer als 10 ist und der Prozentsatz an leeren Buckets größer als 10 % ist, enthalten die Daten so viele Duplikate, dass ein Hashindex möglicherweise nicht der am besten geeignete Typ ist.

Demonstration von Ketten und leeren Buckets

Der folgende T-SQL-Codeblock bietet Ihnen eine einfache Möglichkeit zum Testen von SELECT * FROM sys.dm_db_xtp_hash_index_stats;. Der Codeblock wird in einer Minute ausgeführt. Hier sind die Phasen des folgenden Codeblocks:

Erstellen einer speicheroptimiertem Tabelle mit wenigen Hashindizes.
Ausfüllen der Tabelle mit Tausenden von Zeilen.
a. Es wird ein modulo-Operator verwendet, um die Rate der duplizierten Werte in der StatusCode-Spalte zu konfigurieren.
b. Die Schleife fügt 262.144 Zeilen in etwa einer Minute ein.
Ausdrucken einer Meldung (PRINT), mit der Sie aufgefordert werden, die frühere SELECT-Anweisung aus sys.dm_db_xtp_hash_index_statsauszuführen.

DROP TABLE IF EXISTS SalesOrder_Mem;  
go  
  
  
CREATE TABLE SalesOrder_Mem  
(  
  SalesOrderId   uniqueidentifier  NOT NULL  DEFAULT newid(),  
  OrderSequence  int               NOT NULL,  
  OrderDate      datetime2(3)      NOT NULL,  
  StatusCode     tinyint           NOT NULL,  
  
  PRIMARY KEY NONCLUSTERED  
      HASH (SalesOrderId)  WITH (BUCKET_COUNT = 262144),  
  
  INDEX ix_OrderSequence  
      HASH (OrderSequence) WITH (BUCKET_COUNT = 20000),  
  
  INDEX ix_StatusCode  
      HASH (StatusCode)    WITH (BUCKET_COUNT = 8),  
  
  INDEX ix_OrderDate       NONCLUSTERED (OrderDate DESC)  
)  
  WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA)  
go  
  
--------------------  
  
SET NOCOUNT ON;  
  
-- Same as PK bucket_count.  68 seconds to complete.  
DECLARE @i int = 262144;  
  
BEGIN TRANSACTION;  
  
WHILE @i > 0  
BEGIN  
  
  INSERT SalesOrder_Mem  
      (OrderSequence, OrderDate, StatusCode)  
    Values  
      (@i, GetUtcDate(), @i % 8);  -- Modulo technique.  
  
  SET @i -= 1;  
END  
COMMIT TRANSACTION;  
  
PRINT 'Next, you should query:  sys.dm_db_xtp_hash_index_stats .';  
go

Die vorangehende INSERT-Schleife führt Folgendes aus:

Einfügen von eindeutigen Werten für den Primärschlüsselindex und für ix_OrderSequence.
Fügt ein paar hunderttausend Zeilen ein, die nur acht unterschiedliche Werte darstellen.StatusCode Daher kommt es zu einer hohen Rate von Wertduplikaten im Index ix_StatusCode.

Überprüfen Sie zur Problembehandlung bei nicht optimaler Bucketanzahl die folgende Ausgabe der SELECT-Anweisung von sys.dm_db_xtp_hash_index_stats. Für diese Ergebnisse haben wir der aus Abschnitt D.1 kopierten SELECT-Anweisung WHERE Object_Name(h.object_id) = 'SalesOrder_Mem' hinzugefügt.

Unsere SELECT-Ergebnisse werden nach dem Code angezeigt, zur besseren Anzeige künstlich aufgeteilt in zwei schmalere Ergebnistabellen.

Hier sind die Ergebnisse für die Bucketanzahl.

IndexName	total_bucket_count	empty_bucket_count	EmptyBucketPercent
ix_OrderSequence	32768	13	0
ix_StatusCode	8	4	50
PK_SalesOrd_B14003...	262144	96525	36

Das Nächste sind die Ergebnisse für die Kettenlänge.

IndexName	avg_chain_length	max_chain_length
ix_OrderSequence	8	26
ix_StatusCode	65536	65536
PK_SalesOrd_B14003...	1	8

Untersuchen Sie die oben stehenden Ergebnistabellen auf die drei Hashindizes:

ix_StatusCode:

50 %der Buckets sind leer, das ist positiv.
Die durchschnittliche Kettenlänge ist jedoch mit 65536 sehr hoch.
- Dies weist auf eine große Anzahl duplizierter Werte hin.
- Die Verwendung eines Hashindexes ist in diesem Fall daher nicht angemessen. Stattdessen sollte ein nicht gruppierter Index verwendet werden.

ix_OrderSequence:

0 % der Buckets sind leer. Dieser Wert ist zu niedrig.
Die durchschnittliche Kettenlänge beträgt 8, auch wenn alle Werte in diesem Index eindeutig sind.
- Daher sollte die Bucketanzahl erhöht werden, um die durchschnittliche Kettenlänge an einen Wert in der Nähe von 2 oder 3 zu reduzieren.
Da der Indexschlüssel 262144 eindeutige Werte enthält, sollte die Bucketanzahl mindestens 262144 betragen.
- Wenn zukünftiges Wachstum erwartet wird, sollte die Bucketanzahl größer sein.

Primärschlüsselindex (PK_SalesOrder_…):

36 %der Buckets sind leer, das ist positiv.
Die durchschnittliche Kettenlänge beträgt 1, was ebenfalls positiv ist. Es ist keine Änderung erforderlich.

Abwägung

OLTP-Arbeitsauslastungen konzentrieren sich auf einzelne Zeilen. Vollständige Tabellenscans befinden sich normalerweise nicht im leistungskritischen Pfad für OLTP-Arbeitsauslastungen. Deshalb müssen Sie die Arbeitsspeicherauslastung und die Leistung von Gleichheitstests und Einfügevorgängen gegeneinander abwägen.

Wenn die Arbeitsspeicherauslastung wichtiger ist:

Wählen Sie eine Bucketanzahl in der Nähe der Anzahl eindeutiger Indexschlüsselwerte aus.
Die Bucketanzahl sollte nicht wesentlich niedriger sein als die Anzahl eindeutiger 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.

Wenn die Leistung von Gleichheitstests wichtiger ist:

Ist eine höhere Bucketanzahl mit der doppelten oder drei Mal so großen Anzahl von eindeutigen Indexwerten geeignet. Eine höhere Anzahl bedeutet:
- Schnellerer Abruf bei der Suche nach einem bestimmten Wert.
- Eine erhöhte Arbeitsspeicherauslastung.
- Ein größerer Zeitaufwand für eine vollständige Überprüfung des Hashindexes.

Weiterführende Lektüre

Hashindizes für speicheroptimierte Tabellen
Nicht gruppierter Indizes für speicheroptimierte Tabellen