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

gäller för:SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

Förutsättning

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

• index för Memory-Optimized tabeller
• Hash-index för Memory-Optimized-tabeller

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:

1. Skapar en minnesoptimerad tabell med några hash-index.
2. 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.
3. 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