Memory-Optimized-táblák kivonatindexeinek hibaelhárítása

A következőkre vonatkozik:SQL ServerAzure SQL DatabaseAzure SQL Kezelt Példány

Előfeltétel

A cikk megértéséhez fontos környezeti információk a következő címen érhetők el:

• Indexek a Memory-Optimized táblákhoz
• Hash indexek Memory-Optimized táblákhoz

Gyakorlati számok

Ha hash indexet hoz létre egy memóriaoptimalizált táblához, a vödrök számát a létrehozáskor kell megadni. Ideális esetben a vödörszám az indexkulcs különböző értékeinek számához viszonyítva 1 és 2 közötti szorzat lehet.

Ha azonban a BUCKET_COUNT mérsékelten az előnyben részesített tartomány alatt vagy felett van, a kivonatindex teljesítménye valószínűleg elviselhető vagy elfogadható. Érdemes legalább úgy beállítani a kivonatindexének BUCKET_COUNT értékét, hogy az nagyjából egyezzen meg azzal a sor számmal, amelyre a memóriaoptimalizált táblázata várhatóan növekedni fog.
Tegyük fel, hogy a növekvő táblázat 2 000 000 sort tartalmaz, de az előrejelzés szerint tízszeresére nő 20 000 000 sorra. Kezdje egy gyűjtőszámmal, amely a tábla sorainak tízszerese. Ez lehetővé teszi a sorok mennyiségének növelését.

• Ideális esetben növelni kellene a vödrök számát, amikor a sorok száma eléri a kezdeti vödörszámot.
• Még ha a sorok száma ötször nagyobb is, mint a gyűjtők száma, a teljesítmény a legtöbb helyzetben továbbra is jó.

Tegyük fel, hogy egy kivonatindex 10 000 000 különböző kulcsértéket tartalmaz.

• A 2 000 000 vödör száma körülbelül a lehető legalacsonyabb lenne, amit még elfogadhatna. A teljesítménycsökkenés mértéke elviselhető lehet.

Túl sok ismétlődő érték van az indexben?

Ha a kivonat indexelt értékei magas duplikációval rendelkeznek, a kivonatgyűjtők hosszabb láncokat szenvednek.

Tegyük fel, hogy ugyanazzal a SupportEvent táblával rendelkezik, amely a korábbi T-SQL szintaxiskódblokkból származik. Az alábbi T-SQL-kód bemutatja, hogyan keresheti meg és jelenítheti meg az összes érték arányát egyedi értékekhez:

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

• A 10,0 vagy magasabb arány azt jelenti, hogy a hash nem megfelelő indextípus lehet. Fontolja meg inkább a nem klaszteres index használatát.

Hash index rekeszszámának hibaelhárítása

Ez a szakasz a kivonatindex gyűjtőszámának hibaelhárítását ismerteti.

Láncok és üres tárolók statisztikáinak figyelése

A kivonatindexek statisztikai állapotát az alábbi T-SQL SELECT futtatásával figyelheti. A SELECT a sys.dm_db_xtp_hash_index_statsnevű adatkezelési nézetet (DMV) használja.

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

Hasonlítsa össze a SELECT eredményeket a következő statisztikai irányelvekkel:

• Üres vödrök
  • 33% jó célérték, de a nagyobb százalék (még 90%) általában rendben van.
  • Ha a gyűjtők száma megegyezik a különböző kulcsértékek számával, a gyűjtők körülbelül 33% üres.
  • A 10% alatti érték túl alacsony.
• Gyűjtőkben lévő láncok:
  • Az átlagos 1 lánchossz ideális abban az esetben, ha nincsenek ismétlődő indexkulcs-értékek. A legfeljebb 10 lánchossz általában elfogadható.
  • Ha az átlagos lánchossz nagyobb, mint 10, és az üres vödör százalék nagyobb, mint 10%, akkor az adatok annyi duplikátumot tartalmaznak, hogy a hash-index nem feltétlenül a legmegfelelőbb típus.

Láncok és üres vödrök bemutatása

Az alábbi T-SQL-kódblokk segítségével egyszerűen tesztelheti a SELECT * FROM sys.dm_db_xtp_hash_index_stats;. A kódblokk 1 perc alatt befejeződik. A következő kódblokk fázisai:

1. Létrehoz egy memóriaoptimalizált táblát, amely néhány kivonatindexet tartalmaz.
2. Több ezer sorból álló táblázatot tölt fel.
   a. A StatusCode oszlopban a duplikált értékek sebességének konfigurálására modulo operátor szolgál.
   b. Körülbelül 1 perc alatt a hurok 262 144 sort szúr be.
3. Egy üzenetet ír ki, amely arra kéri, hogy futtassa a korábbi SELECT parancsot 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  

Az előző INSERT ciklus a következőket teszi:

• Az elsődleges kulcsindex és a ix_OrderSequenceegyedi értékeit szúrja be.
• Néhány százezer sort szúr be, amelyek csak nyolc különböző értéket jelölnek StatusCode. Ezért az index ix_StatusCodeesetében magas az értékek duplikációjának aránya.

Hibaelhárításhoz, ha a gyűjtők száma nem optimális, vizsgálja meg a következő SELECT kimenetet a sys.dm_db_xtp_hash_index_stats-ból. Az eredmények eléréséhez hozzáadtuk a WHERE Object_Name(h.object_id) = 'SalesOrder_Mem'-t a D.1-es szakaszból másolt SELECT parancshoz.

A SELECT eredményei a kód után jelennek meg, mesterségesen két keskenyebb eredménytáblára felosztva a jobb megjelenítés érdekében.

• Az alábbiakban láthatók a vödrök számaalapján kapott eredmények.

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

• A következő eredmények a lánchosszesetén találhatók.

IndexNév	avg_chain_length	max_lánchossz
ix_OrderSequence	8	26
ix_StatusCode	65536	65536
PK_SalesOrd_B14003...	1	8

Értelmezzük a három kivonatindex előző eredménytábláit:

ix_StatusCode:

• 50% vödör üres, ami jó.
• Az átlagos lánchossz azonban nagyon magas, 65536.
  • Ez az ismétlődő értékek magas arányát jelzi.
  • Ezért ebben az esetben a kivonatindex használata nem megfelelő. Ehelyett inkább egy nem klaszterevezett indexet kell használni.

ix_OrderSequence:

• 0% a vödrök üresek, ami túl kevés.
• Az átlagos lánchossz 8, annak ellenére, hogy az indexben szereplő összes érték egyedi.
  • Ezért a vödrök számát növelni kell, hogy az átlagos lánchossz 2-hez vagy 3-hoz közelebb legyen.
• Mivel az indexkulcs 262144 egyedi értékkel rendelkezik, a vödörszámnak legalább 262144-nek kell lennie.
  • Ha a jövőbeni növekedés várható, a vödrök számát magasabbra kell állítani.

elsődleges kulcsindex (PK_SalesOrd_...):

• 36% a gyűjtők üresek, ami jó.
• Az átlagos lánchossz 1, ami szintén jó. Nincs szükség módosításra.

A kompromisszum kiegyensúlyozása

Az OLTP számítási feladatai az egyes sorokra összpontosítanak. A teljes táblavizsgálatok általában nem szerepelnek az OLTP-számítási feladatok teljesítmény szempontjából kritikus elérési útjában. Ezért az egyensúlyt memóriakihasználtság mennyisége és egyenlőségi tesztek teljesítménye és a műveletek beszúrásaközött kell kiegyensúlyozni.

Ha a memóriahasználat a nagyobb gond:

• Válasszon egy gyűjtőszámot az egyedi indexkulcs-értékek számához közel.
• A gyűjtők száma nem lehet jelentősen alacsonyabb, mint az egyedi indexkulcs-értékek száma, mivel ez hatással van a legtöbb DML-műveletre, valamint az adatbázis helyreállításához szükséges időre a kiszolgáló újraindítása után.

Ha az egyenlőségi tesztek teljesítménye a nagyobb gond:

• Az egyedi indexértékek számának két vagy háromszorosával nagyobb gyűjtőszám megfelelő. A magasabb szám a következőt jelenti:
  • Gyorsabb lekérések egy adott érték keresésekor.
  • Nagyobb memóriakihasználtság.
  • A kivonatindex teljes vizsgálatához szükséges idő növekedése.

További olvasás

Hash indexek Memory-Optimized táblákhoz
Nem klaszterezett indexek Memory-Optimized táblákhoz