Rozwiązywanie problemów z indeksami skrótów dla tabel Memory-Optimized

Artykuł
2025-03-05

Dotyczy:SQL Server Azure SQL Database Azure SQL Managed Instance

Warunek wstępny

Ważne informacje kontekstowe dotyczące zrozumienia tego artykułu są dostępne pod adresem:

indeksy dla tabel Memory-Optimized
indeksy skrótów dla tabel Memory-Optimized

Liczby praktyczne

Podczas tworzenia indeksu skrótu dla tablicy zoptymalizowanej pod kątem pamięci, należy podczas tworzenia określić liczbę zasobników. W większości przypadków liczba koszy powinna idealnie wynosić od 1 do 2 razy liczby unikatowych wartości w kluczu indeksu.

Jednak nawet jeśli BUCKET_COUNT jest umiarkowanie poniżej lub powyżej preferowanego zakresu, wydajność indeksu skrótu może być tolerowana lub akceptowalna. Rozważ co najmniej nadanie indeksowi skrótu BUCKET_COUNT w przybliżeniu równej liczbie wierszy przewidywanych przez tabelę zoptymalizowaną pod kątem pamięci.
Załóżmy, że rosnąca tabela ma 2 000 000 wierszy, ale przewiduje się, że wzrośnie 10 razy do 20 000 000 wierszy. Zacznij od liczby zasobników, która jest równa 10-krotności liczby wierszy w tabeli. Zapewnia to miejsce na zwiększoną liczbę wierszy.

Najlepiej zwiększyć liczbę zasobników, gdy liczba wierszy osiągnie początkową liczbę zasobników.
Nawet jeśli liczba wierszy wzrośnie do pięciu razy większa niż liczba zasobników, wydajność jest nadal dobra w większości sytuacji.

Załóżmy, że indeks skrótu ma 10 000 000 unikatowych wartości klucza.

Liczba kubełków wynosząca 2 000 000 byłaby najniższą akceptowalną wartością. Stopień obniżenia wydajności może być tolerowany.

Zbyt wiele zduplikowanych wartości w indeksie?

Jeśli indeksowane wartości skrótu mają dużą liczbę duplikatów, zasobniki skrótu cierpią na dłuższe łańcuchy.

Załóżmy, że masz tę samą tabelę SupportEvent z wcześniejszego bloku kodu składni języka T-SQL. Poniższy kod języka T-SQL pokazuje, jak można znaleźć i wyświetlić stosunek wszystkich wartości do unikatowych wartości:

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

Współczynnik 10,0 lub wyższy oznacza, że skrót byłby złym typem indeksu. Rozważ użycie indeksu nieklastrowanego zamiast tego,

Rozwiązywanie problemów z liczbą zasobników indeksu skrótu

W tej sekcji omówiono sposób rozwiązywania problemów z liczbą zasobników dla indeksu skrótu.

Monitorowanie statystyk dla łańcuchów i pustych zasobników

Możesz monitorować statystyczną kondycję indeksów skrótów, uruchamiając następujące polecenie T-SQL SELECT. Funkcja SELECT używa widoku zarządzania danymi (DMV) o nazwie 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];

Porównaj wyniki SELECT z następującymi wytycznymi statystycznymi:

Puste zasobniki:
- 33% jest dobrą wartością docelową, ale większy procent (nawet 90%) jest zwykle w porządku.
- Gdy liczba zasobników jest równa liczbie unikatowych wartości klucza, około 33% zasobników są puste.
- Wartość poniżej 10% jest zbyt niska.
Łańcuchy w zasobnikach:
- Średnia długość łańcucha 1 jest idealna w przypadku braku zduplikowanych wartości klucza indeksu. Długości łańcucha do 10 są zwykle akceptowalne.
- Jeśli średnia długość łańcucha jest większa niż 10, a procent pustego zasobnika jest większy niż 10%, dane mają tak wiele duplikatów, że indeks skrótu może nie być najbardziej odpowiednim typem.

Pokaz łańcuchów i pustych zasobników

Poniższy blok kodu T-SQL daje łatwy sposób na przetestowanie SELECT * FROM sys.dm_db_xtp_hash_index_stats;. Blok kodu zostanie ukończony w ciągu 1 minuty. Oto fazy następującego bloku kodu:

Tworzy tabelę zoptymalizowaną pod kątem pamięci zawierającą kilka indeksów skrótów.
Wypełnia tabelę tysiącami wierszy.
a. Operator modulo służy do konfigurowania szybkości zduplikowanych wartości w kolumnie StatusCode.
b. Pętla wstawia 262,144 wierszy w około 1 minutę.
Wyświetla komunikat proszący o uruchomienie wcześniejszej komendy 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

Poprzednia pętla INSERT wykonuje następujące czynności:

Wstawia unikatowe wartości dla indeksu klucza podstawowego i dla ix_OrderSequence.
Wstawia kilkaset tysięcy wierszy, które reprezentują tylko osiem odrębnych wartości dla StatusCode. W związku z tym występuje duża liczba duplikacji wartości w indeksie ix_StatusCode.

Aby rozwiązać problemy, gdy liczba zasobników nie jest optymalna, sprawdź następujące wyniki polecenia SELECT z sys.dm_db_xtp_hash_index_stats. W przypadku tych wyników dodaliśmy WHERE Object_Name(h.object_id) = 'SalesOrder_Mem' do sekcji SELECT skopiowanej z sekcji D.1.

Nasze wyniki SELECT są wyświetlane po kodzie, celowo podzielone na dwie węższe tabele, dla lepszego wyświetlania.

Oto wyniki dla liczby kubełków .

Nazwa indeksu	łączna_liczba_kubełków	liczba_pustych_wiaderek	EmptyBucketPercent
ix_OrderSequence	32768	13	0
ix_StatusCode	8	4	50
PK_SalesOrd_B14003...	262144	96525	36

Oto wyniki dla długości łańcucha .

Nazwa indeksu	średnia_długość_łańcucha	maks_długość_łańcucha
ix_OrderSequence	8	26
ix_StatusCode	65536	65536
PK_SalesOrd_B14003...	1	8

Zinterpretujmy powyższe tabele wyników dla trzech indeksów skrótów:

ix_StatusCode:

50% zasobników jest pustych, co jest korzystne.
Jednak średnia długość łańcucha jest bardzo wysoka na poziomie 65536.
- Oznacza to wysoki wskaźnik zduplikowanych wartości.
- W związku z tym użycie indeksu skrótu nie jest odpowiednie w tym przypadku. Zamiast tego należy użyć indeksu nieklastrowanego.

ix_OrderSequence:

0% zasobników są puste, co jest zbyt niskie.
Średnia długość łańcucha wynosi 8, mimo że wszystkie wartości w tym indeksie są unikatowe.
- Dlatego należy zwiększyć liczbę koszyków, aby zmniejszyć średnią długość łańcucha do około 2 lub 3.
Ponieważ klucz indeksu ma 262144 unikatowych wartości, liczba zasobników powinna być co najmniej 262144.
- Jeśli oczekuje się przyszłego wzrostu, liczba bucketów powinna być większa.

indeks klucza podstawowego (PK_SalesOrd_...):

36% zasobników są puste, co jest dobre.
Średnia długość łańcucha wynosi 1, co jest również dobre. Nie jest wymagana żadna zmiana.

Równoważenie kompromisu

Obciążenia OLTP koncentrują się na poszczególnych wierszach. Skanowanie pełnej tabeli zwykle nie należy do ścieżki krytycznej dla wydajności dla obciążeń OLTP. W związku z tym kompromis, który należy zrównoważyć, to między ilością wykorzystania pamięci a wydajności ą testów równości i operacjami wstawiania.

Jeśli wykorzystanie pamięci jest większym problemem:

Wybierz liczbę zasobników zbliżoną do liczby unikatowych wartości klucza indeksu.
Liczba zasobników nie powinna być znacznie niższa niż liczba unikatowych wartości klucza indeksu, ponieważ ma to wpływ na większość operacji DML, a także czas potrzebny na odzyskanie bazy danych po ponownym uruchomieniu serwera.

Jeśli wydajność testów równości jest większym problemem:

Większa liczba zasobników, wynosząca dwukrotność lub trzykrotność liczby unikatowych wartości indeksu, jest odpowiednia. Większa liczba oznacza:
- Szybsze pobieranie podczas wyszukiwania jednej określonej wartości.
- Zwiększone wykorzystanie pamięci.
- Wzrost czasu wymaganego do pełnego skanowania indeksu skrótu.

Dodatkowa lektura

indeksy skrótów dla tabel Memory-Optimized
indeksy nieklastrowane dla tabel Memory-Optimized

Udostępnij za pośrednictwem