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

Gilt für:SQL ServerAzure SQL-DatenbankAzure SQL Managed Instance

Voraussetzungen

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

• Indizes für speicheroptimierte Tabellen
• Hashindizes für speicheroptimierte Tabellen

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 Anzahl der Buckets idealerweise zwischen dem Ein- und Zweifachen der Anzahl eindeutiger Werte im Indexschlüssel liegen.

Auch wenn BUCKET_COUNT geringfügig unter oder über dem bevorzugten Bereich liegt, ist die Leistung des Hashindexes wahrscheinlich tolerierbar oder akzeptabel. Ziehen Sie zumindest in Betracht, für Ihren Hashindex eine BUCKET_COUNT festzulegen, die ungefähr der Anzahl der Zeilen entspricht, auf die Ihre speicheroptimierte Tabelle voraussichtlich anwachsen wird.
Angenommen, Ihre wachsende Tabelle verfügt über 2.000.000 Zeilen, Sie gehen jedoch davon aus, dass die Anzahl auf das Zehnfache, sprich 20.000.000 Zeilen, anwachsen wird. Beginnen Sie mit einer Anzahl von Buckets, die dem Zehnfachen der Anzahl der Zeilen in der Tabelle entspricht. Dieses bietet Ihnen Raum für eine größere Anzahl von Zeilen.

• Im Idealfall würden Sie die Bucket-Anzahl erhöhen, wenn die Anzahl der Zeilen die ursprüngliche Bucket-Anzahl erreicht.
• Selbst wenn die Anzahl der Zeilen auf das Fünffache der Anzahl der Buckets steigt, ist die Leistung in den meisten Fällen immer noch gut.

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

• Eine Anzahl von 2.000.000 Buckets wäre ungefähr das Minimum, das Sie akzeptieren könnten. Das Ausmaß der Leistungseinbußen könnte tolerierbar sein.

Zu viele duplizierte Werte im Index?

Wenn die hash-indizierten Werte eine hohe Duplikatrate aufweisen, leiden die Hash-Buckets unter längeren Ketten.

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.

Fehlerbehebung für die Anzahl der Buckets im Hash-Index

Dieser Abschnitt erläutert, wie Sie Probleme mit der Anzahl der Buckets für Ihren Hash-Index beheben.

Statistiken für Ketten und leere Behälter überwachen

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:

• Buckets leeren:
  • 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 Eimern:
  • 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:

1. Erstellt eine speicheroptimierte Tabelle, die über einige Hashindizes verfügt.
2. Füllt die 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.
3. 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 für StatusCode darstellen. 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 Anzahl der Buckets.

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

Interpretieren wir die vorstehenden Ergebnistabellen für die drei Hash-Indizes:

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 Anzahl der Buckets erhöht werden, um die durchschnittliche Kettenlänge näher an 2 oder 3 heranzubringen.
• Da der Indexschlüssel 262144 eindeutige Werte hat, sollte die Anzahl der Buckets mindestens 262144 betragen.
  • Wenn zukünftiges Wachstum erwartet wird, sollte die Anzahl der Buckets höher sein.

Primärschlüsselindex (PK_SalesOrd_...):

• 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 gehören bei OLTP-Workloads normalerweise nicht zum leistungskritischen Pfad. 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 Anzahl von Buckets, die nahe an der Anzahl der eindeutigen Werte des Indexschlüssels liegt.
• Die Anzahl der Buckets sollte nicht signifikant niedriger sein als die Anzahl der eindeutigen Indexschlüsselwerte, da dies die meisten DML-Operationen sowie die Zeit zur Wiederherstellung der Datenbank nach einem Server-Neustart beeinflusst.

Wenn die Leistung von Gleichheitstests wichtiger ist:

• Eine höhere Anzahl von Buckets, etwa das Zwei- bis Dreifache der Anzahl eindeutiger Indexwerte, ist angemessen. 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.

Zusätzliche Lektüre

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