Řešení potíží s indexy hodnot hash pro tabulky Memory-Optimized

Článek
03/05/2025

platí pro:SQL Server Azure SQL Database azure SQL Managed Instance

Předpoklad

Důležité kontextové informace pro pochopení tohoto článku najdete tady:

Praktická čísla

Při vytváření indexu hash pro tabulku optimalizovanou pro paměť je potřeba zadat počet kbelíků v době vytvoření. Ve většině případů by počet kbelíků byl v ideálním případě mezi 1 a 2násobky počtu jedinečných hodnot v klíči indexu.

I když je ale BUCKET_COUNT mírně nižší nebo vyšší než upřednostňovaný rozsah, bude výkon vašeho hash indexu pravděpodobně přijatelný nebo tolerovatelný. Alespoň zvažte, že váš index hash bude mít BUCKET_COUNT přibližně roven počtu řádků, které předpokládáte, že tabulka optimalizovaná pro paměť bude mít.
Předpokládejme, že vaše rostoucí tabulka má 2 000 000 řádků, ale předpovídá se, že se tento počet zvětší 10krát na 20 000 000 řádků. Začněte počtem kbelíků, který je 10krát větší než počet řádků v tabulce. Tím získáte prostor pro větší množství řádků.

V ideálním případě byste počet kbelíků zvýšili, když množství řádků dosáhne počátečního počtu kbelíků.
I když počet řádků roste na pětkrát větší než počet kbelíků, je výkon ve většině situací stále dobrý.

Předpokládejme, že index hash má 10 000 000 jedinečných hodnot klíče.

Počet kbelíků 2 000 000 by byl přibližně nejméně, co byste mohli akceptovat. Stupeň snížení výkonu může býtlerovatelný.

Příliš mnoho duplicitních hodnot v indexu?

Pokud mají indexované hodnoty hash vysokou míru duplicit, kontejnery hash trpí delšími řetězy.

Předpokládejme, že máte stejnou tabulku SupportEvent ze staršího bloku kódu syntaxe T-SQL. Následující kód T-SQL ukazuje, jak můžete najít a zobrazit poměr všech hodnot k jedinečných hodnot:

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

Poměr 10,0 nebo vyšší znamená, že hash není vhodná jako typ indexu. Zvažte místo toho použití neclusterovaného indexu.

Řešení potíží s počtem kontejnerů indexu hash

Tato část popisuje, jak řešit potíže s počtem kontejnerů pro index hash.

Monitorování statistik pro řetězy a prázdné kontejnery

Statistický stav indexů hash můžete monitorovat spuštěním následujícího příkazu T-SQL SELECT. Select používá zobrazení správy dat (DMV) s názvem 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];

Porovnejte výsledky SELECT s následujícími statistickými pokyny:

Prázdné kontejnery:
- 33% je dobrá cílová hodnota, ale větší procento (i 90%) je obvykle v pořádku.
- Pokud se počet kbelíků rovná počtu jedinečných hodnot klíče, je přibližně 33% kbelíků prázdných.
- Hodnota nižší než 10% je příliš nízká.
Řetězy v kontejnerech:
- Průměrná délka řetězce 1 je ideální v případě, že neexistují žádné duplicitní hodnoty klíče indexu. Délky řetězů až do 10 jsou obvykle přijatelné.
- Pokud je průměrná délka řetězu větší než 10 a procento prázdného kbelíku je větší než 10%, data mají tolik duplicit, že index hash nemusí být nejvhodnějším typem.

Ukázka řetězů a prázdných kbelíků

Následující blok kódu T-SQL poskytuje snadný způsob, jak otestovat SELECT * FROM sys.dm_db_xtp_hash_index_stats;. Blok kódu se dokončí za 1 minutu. Tady jsou fáze následujícího bloku kódu:

Vytvoří tabulku optimalizovanou pro paměť s několika indexy hash.
Naplní tabulku tisíci řádků.
a. Operátor modulo slouží ke konfiguraci rychlosti duplicitních hodnot ve sloupci StatusCode.
b. Smyčka vloží 262,144 řádků přibližně za 1 minutu.
PRINT zobrazí zprávu s výzvou ke spuštění předchozího příkazu SELECT z 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

Předchozí smyčka INSERT provede následující kroky:

Vloží jedinečné hodnoty pro index primárního klíče a pro ix_OrderSequence.
Vloží několik stovek tisíc řádků, které představují pouze osm jedinečných hodnot pro StatusCode. Proto existuje vysoká míra duplikace hodnot v indexu ix_StatusCode.

Při řešení potíží, pokud počet kbelíků není optimální, prozkoumejte následující výstup příkazu SELECT z sys.dm_db_xtp_hash_index_stats. Pro tyto výsledky jsme přidali WHERE Object_Name(h.object_id) = 'SalesOrder_Mem' do SELECT zkopírovaného z části D.1.

Naše výsledky SELECT jsou zobrazeny za kódem a pro lepší zobrazení jsou uměle rozděleny do dvou užších tabulek.

Zde jsou výsledky pro počet věder .

Název indexu	Počet_celkových_kbelíků	počet_prázdných_kbelíků	EmptyBucketPercent
ix_OrderSequence	32768	13	0
ix_StatusCode	8	4	50
PK_SalesOrd_B14003...	262144	96525	36

Dále jsou výsledky pro délku řetězu .

Název indexu	průměrná délka řetězce	maximální_délka_řetězce
ix_OrderSequence	8	26
ix_StatusCode	65536	65536
PK_SalesOrd_B14003...	1	8

Pojďme interpretovat předchozí tabulky výsledků pro tři indexy hash:

ix_StatusCode:

50% kbelíků je prázdných, což je dobré.
Průměrná délka řetězu je však velmi vysoká při 65536.
- To označuje vysokou míru duplicitních hodnot.
- Proto použití indexu hash není v tomto případě vhodné. Místo toho by se měl použít neclusterovaný index.

ix_OrderSequence:

0% kbelíků je prázdný, což je příliš malé množství.
Průměrná délka řetězu je 8, i když jsou všechny hodnoty v tomto indexu jedinečné.
- Proto by se měl zvýšit počet kbelíků, aby se snížila průměrná délka řetězu blíž ke 2 nebo 3.
Vzhledem k tomu, že klíč indexu má 262144 jedinečné hodnoty, měl by být počet kbelíků alespoň 262144.
- Pokud se očekává budoucí růst, měl by být počet kbelíků vyšší.

index primárního klíče (PK_SalesOrd_...):

36% kbelíků jsou prázdné, což je dobré.
Průměrná délka řetězu je 1, což je také dobré. Není nutná žádná změna.

Vyvážení kompromisu

Úlohy OLTP se zaměřují na jednotlivé řádky. Úplná skenování tabulek obvykle nejsou kritickou cestou z hlediska výkonu pro úlohy OLTP. Kompromis, který musíte vyvážit, je mezi množstvím využití paměti a výkonem testů rovnosti a operací vkládání.

Pokud je využití paměti větší problém:

Zvolte počet kontejnerů blízko počtu jedinečných hodnot klíče indexu.
Počet kontejnerů by neměl být výrazně nižší než počet jedinečných hodnot klíče indexu, protože to má vliv na většinu operací DML i dobu potřebnou k obnovení databáze po restartování serveru.

Pokud je výkon testů rovnosti větší problém:

Je vhodné mít počet kbelíků ve výši dvojnásobku nebo trojnásobku počtu jedinečných hodnot indexu. Vyšší počet znamená:
- Rychlejší načítání při hledání jedné konkrétní hodnoty
- Zvýšené využití paměti.
- Zvýšení doby potřebné pro úplnou kontrolu indexu hash.

Další čtení

indexy hodnot hash pro tabulky Memory-Optimized
neclusterované indexy pro tabulky Memory-Optimized

Sdílet prostřednictvím