Problemen met hash-indexen voor Memory-Optimized tabellen oplossen

Van toepassing op:SQL Server Azure SQL Database Azure SQL Managed Instance

Voorwaarde

Belangrijke contextinformatie voor het begrijpen van dit artikel is beschikbaar op:

Praktische getallen

Bij het maken van een hash-index voor een tabel die is geoptimaliseerd voor geheugen, moet het aantal buckets worden opgegeven tijdens het maken. In de meeste gevallen is het aantal buckets in het ideale geval tussen 1 en 2 keer het aantal afzonderlijke waarden in de indexsleutel.

Zelfs als de BUCKET_COUNT zich gemiddeld onder of boven het voorkeursbereik bevindt, is de prestaties van uw hash-index waarschijnlijk acceptabel of acceptabel. Overweeg minimaal uw hash-index een BUCKET_COUNT te geven die ongeveer gelijk is aan het aantal rijen dat u voorspelt dat uw geheugen-geoptimaliseerde tabel zal hebben.
Stel dat uw groeiende tabel 2.000.000 rijen bevat, maar de voorspelling is dat deze 10 keer toeneemt tot 20.000.000 rijen. Begin met bucketaantallen die 10 maal het aantal rijen in de tabel zijn. Dit geeft u ruimte voor een grotere hoeveelheid rijen.

In het ideale gevallen verhoogt u het aantal buckets wanneer het aantal rijen het aanvankelijke aantal buckets bereikt.
Zelfs als het aantal rijen vijf keer groter is dan het aantal buckets, zijn de prestaties in de meeste situaties nog steeds goed.

Stel dat een hash-index 10.000.000 unieke sleutelwaarden heeft.

Een aantal buckets van 2.000.000 zou ongeveer zo laag zijn als u zou kunnen accepteren. De mate van prestatievermindering kan acceptabel zijn.

Te veel dubbele waarden in de index?

Als de geïndexeerde hashwaarden een hoge mate van duplicaten hebben, lijden de hash-buckets aan langere ketens.

Stel dat u dezelfde SupportEvent-tabel hebt van het eerdere T-SQL-syntaxisblok. De volgende T-SQL-code laat zien hoe u de verhouding van alle waarden tot unieke waarden kunt vinden en weergeven:

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

Een verhouding van 10,0 of hoger betekent dat een hash een slecht type index is. Overweeg in plaats daarvan een niet-geclusterde index te gebruiken.

Problemen met het aantal hash-indexbuckets oplossen

In deze sectie wordt beschreven hoe u problemen met het aantal buckets van uw hash-index kunt opsporen.

Statistieken voor ketens en lege buckets monitoren

U kunt de statistische status van uw hash-indexen bewaken door de volgende T-SQL SELECT uit te voeren. Select gebruikt de gegevensbeheerweergave (DMV) met de naam 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];

Vergelijk de SELECT-resultaten met de volgende statistische richtlijnen:

Lege buckets:
- 33% is een goede doelwaarde, maar een groter percentage (zelfs 90%) is meestal prima.
- Wanneer het aantal buckets gelijk is aan het aantal afzonderlijke sleutelwaarden, zijn ongeveer 33% van de buckets leeg.
- Een waarde onder de 10% is te laag.
Ketens binnen buckets:
- Een gemiddelde ketenlengte van 1 is ideaal voor het geval er geen dubbele indexsleutelwaarden zijn. Kettinglengten tot 10 zijn meestal acceptabel.
- Als de gemiddelde ketenlengte groter is dan 10 en het lege bucketpercentage groter is dan 10%, hebben de gegevens zoveel duplicaten dat een hash-index mogelijk niet het meest geschikte type is.

Demonstratie van kettingen en lege buckets

Het volgende T-SQL-codeblok biedt een eenvoudige manier om een SELECT * FROM sys.dm_db_xtp_hash_index_stats; te testen. Het codeblok wordt binnen 1 minuut voltooid. Dit zijn de fasen van het volgende codeblok:

Hiermee maakt u een tabel die is geoptimaliseerd voor geheugen met een paar hash-indexen.
Hiermee wordt de tabel gevuld met duizenden rijen.
a. Een modulo-operator wordt gebruikt om de frequentie van dubbele waarden in de kolom StatusCode te configureren.
b. De lus voegt in ongeveer 1 minuut 262.144 rijen in.
Drukt een bericht af waarin u wordt gevraagd om de eerder SELECT uit te voeren van sys.dm_db_xtp_hash_index_stats.

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

De voorgaande INSERT lus doet het volgende:

Voegt unieke waarden in voor de primaire-sleutelindex en voor ix_OrderSequence.
Voegt een paar honderdduizenden rijen in die slechts acht afzonderlijke waarden vertegenwoordigen voor StatusCode. Daarom is er een hoge mate van waardeduplicatie in index ix_StatusCode.

Als u problemen wilt oplossen wanneer het aantal buckets niet optimaal is, bekijkt u de volgende uitvoer van select uit sys.dm_db_xtp_hash_index_stats. Voor deze resultaten hebben we WHERE Object_Name(h.object_id) = 'SalesOrder_Mem' toegevoegd bij de SELECT die is gekopieerd uit sectie D.1.

Onze SELECT resultaten worden na de code weergegeven, kunstmatig gesplitst in twee smalle resultatentabellen voor een betere weergave.

Dit zijn de resultaten voor het aantal buckets.

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

Hierna volgen de resultaten voor de lengte van de keten.

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

Laten we de voorgaande resultatentabellen voor de drie hash-indexen interpreteren:

ix_StatusCode:

50% van de buckets zijn leeg, wat goed is.
De gemiddelde ketenlengte is echter zeer hoog bij 65536.
- Dit geeft een hoge frequentie van dubbele waarden aan.
- Daarom is het gebruik van een hash-index in dit geval niet geschikt. In plaats daarvan moet een niet-geclusterde index worden gebruikt.

ix_OrderSequence:

0% van de buckets zijn leeg, wat te laag is.
De gemiddelde ketenlengte is 8, ook al zijn alle waarden in deze index uniek.
- Daarom moet het aantal buckets worden verhoogd om de gemiddelde ketenlengte te verkleinen naar gemiddeld 2 of 3.
Omdat de indexsleutel 262144 unieke waarden heeft, moet het aantal buckets ten minste 262144 zijn.
- Als toekomstige groei wordt verwacht, moet het aantal buckets hoger zijn.

Primaire-sleutelindex (PK_SalesOrd_...):

36% van de emmers zijn leeg, wat goed is.
De gemiddelde kettinglengte is 1, wat ook goed is. Er is geen wijziging nodig.

Het afwegen van het compromis

OLTP-workloads richten zich op afzonderlijke rijen. Volledige tabelscans bevinden zich meestal niet in het prestatiekritieke pad voor OLTP-workloads. De afweging die u moet maken, is daarom tussen het geheugengebruik en de prestaties van gelijkheidstests en het invoegen van bewerkingen.

Als geheugengebruik de grootste zorg is:

Kies een bucketaantal dicht bij het aantal unieke indexsleutelwaarden.
Het aantal buckets mag niet aanzienlijk lager zijn dan het aantal unieke indexsleutelwaarden, omdat dit van invloed is op de meeste DML-bewerkingen en de tijd die nodig is om de database te herstellen nadat de server opnieuw is opgestart.

Als de prestaties van gelijkheidstests de grootste zorg zijn:

Een hoger aantal buckets, van twee of drie keer het aantal unieke indexwaarden, is geschikt. Een hogere telling betekent:
- Sneller ophalen bij het zoeken naar één specifieke waarde.
- Een verhoogd geheugengebruik.
- Een toename van de tijd die nodig is voor een volledige scan van de hash-index.

Aanvullende literatuur

Hashindexen voor geheugen-geoptimaliseerde tabellen
Niet-geclusterde indexen voor geheugen-geoptimaliseerde tabellen

Feedback

Is deze pagina nuttig?

Last updated on 2025-12-08