Felsöka Hash-index för Memory-Optimized-tabeller

Artikel
2025-03-05

gäller för:SQL Server Azure SQL Database Azure SQL Managed Instance

Förutsättning

Viktig kontextinformation för att förstå den här artikeln finns på:

Praktiska siffror

När du skapar ett hash-index för en minnesoptimerad tabell måste antalet bucketar anges vid skapandetillfället. I de flesta fall skulle antalet bucketar helst vara mellan 1 och 2 gånger antalet distinkta värden i indexnyckeln.

Men även om BUCKET_COUNT är måttligt under eller över det föredragna intervallet, kommer prestandan för ditt hashindex sannolikt att vara acceptabel eller acceptabel. Överväg åtminstone att ge hashindexet en BUCKET_COUNT ungefär lika med det antal rader som du förutsäger att din minnesoptimerade tabell kommer att växa till.
Anta att din växande tabell har 2 000 000 rader, men förutsägelsen är att den växer 10 gånger till 20 000 000 rader. Börja med ett antal skopor som är 10 gånger antalet rader i tabellen. Detta ger dig utrymme för en ökad mängd rader.

Idealiskt sett bör du öka antalet bucketar när antalet rader når det ursprungliga antalet bucketar.
Även om antalet rader växer till fem gånger större än antalet bucketar är prestandan fortfarande bra i de flesta situationer.

Anta att ett hash-index har 10 000 000 distinkta nyckelvärden.

Ett bucketantal på 2 000 000 skulle vara ungefär så lågt som du kunde acceptera. Graden av prestandaförsämring kan vara acceptabel.

För många duplicerade värden i indexet?

Om de hash-indexerade värdena har en hög dubblettfrekvens drabbas hash-bucketarna av längre kedjor.

Anta att du har samma SupportEvent-tabell från det tidigare T-SQL-syntaxkodblocket. Följande T-SQL-kod visar hur du kan hitta och visa förhållandet mellan alla värden och unika värden:

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

Ett förhållande på 10,0 eller högre innebär att en hash skulle vara en dålig typ av index. Överväg att använda ett icke-grupperat index i stället,

Felsöka antalet hashindex bucketar

I det här avsnittet beskrivs hur du felsöker antalet bucketar för ditt hash-index.

Övervaka statistik för kedjor och tomma hinkar

Du kan övervaka den statistiska hälsan för dina hash-index genom att köra följande T-SQL SELECT. SELECT använder datahanteringsvyn (DMV) med namnet 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];

Jämför SELECT-resultaten med följande statistiska riktlinjer:

Tomma bucketar:
- 33% är ett bra målvärde, men en större procentandel (även 90%) är vanligtvis bra.
- När antalet bucketar är lika med antalet distinkta nyckelvärden är cirka 33% av bucketarna tomma.
- Ett värde under 10% är för lågt.
Kedjor inom bucketar:
- En genomsnittlig kedjelängd på 1 är idealisk om det inte finns några duplicerade indexnyckelvärden. Kedjelängder upp till 10 är vanligtvis acceptabla.
- Om den genomsnittliga kedjelängden är större än 10 och den tomma bucketprocenten är större än 10%har data så många dubbletter att ett hashindex kanske inte är den lämpligaste typen.

Demonstration av kedjor och tomma bucketar

Följande T-SQL-kodblock ger dig ett enkelt sätt att testa en SELECT * FROM sys.dm_db_xtp_hash_index_stats;. Kodblocket slutförs på 1 minut. Här är faserna i följande kodblock:

Skapar en minnesoptimerad tabell med några hash-index.
Fyller tabellen med tusentals rader.
a. En modulooperator används för att konfigurera hastigheten för duplicerade värden i kolumnen StatusCode.
b. Slingan infogar 262 144 rader på cirka 1 minut.
PRINTs ett meddelande som ber dig att köra den tidigare SELECT från 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

Föregående INSERT-loop gör följande:

Infogar unika värden för primärnyckelindexet och för ix_OrderSequence.
Infogar ett par hundra tusen rader som endast representerar åtta distinkta värden för StatusCode. Därför finns det en hög grad av värdeduplicering i indexet ix_StatusCode.

Om du vill felsöka när antalet bucketar inte är optimalt kan du granska följande utdata från SELECT från sys.dm_db_xtp_hash_index_stats. För dessa resultat har vi lagt till WHERE Object_Name(h.object_id) = 'SalesOrder_Mem' i SELECT som kopierats från avsnittet D.1.

Våra SELECT resultat visas efter koden, artificiellt uppdelade i två smalare resultattabeller för bättre visning.

Här är resultatet för antal hinkar.

Indexnamn	total_bucket_count	antal_tomma_hinkar	EmptyBucketPercent
ix_OrderSequence	32768	13	0
ix_StatusCode	8	4	50
PK_SalesOrd_B14003...	262144	96525	36

Härnäst visas resultatet för kedjelängd.

IndexName	genomsnittlig_kedjelängd	max_kedjelängd
ix_OrderSequence	8	26
ix_StatusCode	65536	65536
PK_SalesOrd_B14003...	1	8

Låt oss tolka de föregående resultattabellerna för de tre hash-indexen:

ix_StatusCode:

50% av hinkarna är tomma, vilket är bra.
Den genomsnittliga kedjelängden är dock mycket hög vid 65536.
- Detta indikerar en hög frekvens av duplicerade värden.
- Därför är det inte lämpligt att använda ett hash-index i det här fallet. Ett icke-grupperat index ska användas i stället.

ix_OrderSequence:

0% av bucketarna är tomma, vilket är för lågt.
Den genomsnittliga kedjelängden är 8, även om alla värden i det här indexet är unika.
- Därför bör bucketantalet ökas för att minska den genomsnittliga kedjelängden närmare 2 eller 3.
Eftersom indexnyckeln har 262144 unika värden bör antalet bucketar vara minst 262144.
- Om framtida tillväxt förväntas bör antalet hinkar vara högre.

Primärnyckelindex (PK_SalesOrd_...):

36% av hinkarna är tomma, vilket är bra.
Den genomsnittliga kedjelängden är 1, vilket också är bra. Ingen ändring krävs.

Balansera kompromissen

OLTP-arbetsbelastningar fokuserar på enskilda rader. Fullständiga tabellgenomsökningar finns vanligtvis inte i den prestandakritiska sökvägen för OLTP-arbetsbelastningar. Därför är kompromissen du måste balansera mellan mängd minnesanvändning mot prestanda för likhetstester och infogningar.

Om minnesanvändning är det största problemet:

Välj ett bucketantal nära antalet unika indexnyckelvärden.
Antalet bucketar bör inte vara betydligt lägre än antalet unika indexnyckelvärden, eftersom detta påverkar de flesta DML-åtgärder samt den tid det tar att återställa databasen efter omstart av servern.

Om prestanda för likhetstester är det största problemet:

Ett högre bucketantal, med två eller tre gånger så många unika indexvärden, är lämpligt. Ett högre antal innebär:
- Snabbare hämtning när du letar efter ett specifikt värde.
- En ökad minnesanvändning.
- En ökning av den tid som krävs för en fullständig genomsökning av hash-indexet.

Ytterligare läsning

Hash-index för Memory-Optimized-tabeller
icke-klustrade index för Memory-Optimized tabeller

Dela via