Udostępnij za pomocą

Maksymalizowanie jakości grup wierszy pod kątem wydajności indeksu kolumnowego

Jakość grupy wierszy jest określana przez liczbę wierszy w tej grupie. Zwiększenie dostępnej pamięci może zmaksymalizować liczbę wierszy skompresowanych przez indeks magazynu kolumn do każdej grupy wierszy. Użyj tych metod, aby zwiększyć współczynniki kompresji i wydajność zapytań dla indeksów magazynu kolumn.

Dlaczego rozmiar grupy wierszy ma znaczenie

Ponieważ indeks magazynu kolumn skanuje tabelę przez skanowanie segmentów kolumn poszczególnych grup wierszy, maksymalizowanie liczby wierszy w każdej grupie wierszy zwiększa wydajność zapytań. Gdy grupy wierszy mają dużą liczbę wierszy, kompresja danych ulepsza się, co oznacza, że jest mniej danych do odczytu z dysku.

Aby uzyskać więcej informacji na temat grup wierszy, zobacz Przewodnik po indeksach magazynu kolumn.

Rozmiar docelowy dla grup wierszy

Aby uzyskać najlepszą wydajność zapytań, celem jest zmaksymalizowanie liczby wierszy na grupę wierszy w indeksie kolumnowym. Grupa wierszy może zawierać maksymalnie 1 048 576 wierszy. Można mieć mniej niż maksymalną liczbę wierszy na grupę wierszy. Indeksy kolumnowe osiągają dobrą wydajność, jeśli grupy wierszy mają co najmniej 100 000 wierszy.

Grupy wierszy mogą być przycinane podczas kompresji

Podczas zbiorczego ładowania lub ponownego kompilowania indeksu magazynu kolumn czasami nie ma wystarczającej ilości pamięci do skompresowania wszystkich wierszy wyznaczonych dla każdej grupy wierszy. Gdy występuje presja na pamięć, indeksy magazynu kolumn ograniczają rozmiary grup wierszy, dzięki czemu kompresja do magazynu kolumn może zakończyć się powodzeniem.

Jeśli nie ma wystarczającej ilości pamięci do skompresowania co najmniej 10 000 wierszy do każdej grupy wierszy, zostanie wygenerowany błąd.

Aby uzyskać więcej informacji na temat ładowania zbiorczego, zobacz ładowanie zbiorcze do sklastrowanego indeksu magazynującego kolumny.

Jak monitorować jakość grup wierszy

Dynamiczny widok zarządzania (DMV) (sys.dm_db_column_store_row_group_physical_stats zawiera definicję widoku zgodną z bazą danych SQL), która uwidacznia przydatne informacje, takie jak liczba wierszy w grupach wierszy i przyczynę przycinania, jeśli wystąpiło przycinanie. Możesz utworzyć następujący widok jako przydatny sposób, aby zapytać ten DMV i uzyskać informacje na temat trimmowania grupy wierszy.

CREATE VIEW dbo.vCS_rg_physical_stats
AS
WITH cte
AS
(
select   tb.[name]                    AS [logical_table_name]
,        rg.[row_group_id]            AS [row_group_id]
,        rg.[state]                   AS [state]
,        rg.[state_desc]              AS [state_desc]
,        rg.[total_rows]              AS [total_rows]
,        rg.[trim_reason_desc]        AS trim_reason_desc
,        mp.[physical_name]           AS physical_name
FROM    sys.[schemas] sm
JOIN    sys.[tables] tb               ON  sm.[schema_id]          = tb.[schema_id]
JOIN    sys.[pdw_table_mappings] mp   ON  tb.[object_id]          = mp.[object_id]
JOIN    sys.[pdw_nodes_tables] nt     ON  nt.[name]               = mp.[physical_name]
JOIN    sys.[dm_pdw_nodes_db_column_store_row_group_physical_stats] rg      ON  rg.[object_id]     = nt.[object_id]
                                                                            AND rg.[pdw_node_id]   = nt.[pdw_node_id]
                                        AND rg.[distribution_id]    = nt.[distribution_id]
)
SELECT *
FROM cte;

Kolumna trim_reason_desc wskazuje, czy grupa wierszy została przycięta (trim_reason_desc = NO_TRIM oznacza, że nie było przycinania, a grupa wierszy ma optymalną jakość). Następujące przyczyny przycinania wskazują przedwczesne przycinanie grupy wierszy:

  • BULKLOAD: Ta przyczyna przycinania jest używana, gdy przychodząca grupa wierszy do załadowania miała mniej niż 1 milion wierszy. Silnik utworzy skompresowane grupy wierszy, jeśli wstawia się więcej niż 100 000 wierszy (w przeciwieństwie do wstawiania do przechowalni różnic), ale ustawia powód przycinania na BULKLOAD. W tym scenariuszu rozważ zwiększenie partii danych, aby uwzględnić większą liczbę wierszy. Ponadto ponownie oceń swój schemat partycjonowania, aby upewnić się, że nie jest on zbyt szczegółowy, ponieważ grupy wierszy nie mogą przekraczać granic partycji.
  • MEMORY_LIMITATION: Aby utworzyć grupy wierszy z 1 milionami wierszy, aparat wymaga pewnej ilości pamięci roboczej. Jeśli dostępna pamięć sesji ładowania jest mniejsza niż wymagana pamięć robocza, grupy wierszy są przedwcześnie przycinane. W poniższych sekcjach wyjaśniono, jak oszacować wymaganą pamięć i przydzielić więcej pamięci.
  • DICTIONARY_SIZE: Ten powód przycinania grupy wierszy wskazuje, że wystąpiło przycinanie grupy wierszy, ponieważ istniała co najmniej jedna kolumna ciągu z ciągami o szerokości i/lub wysokiej kardynalności. Rozmiar słownika jest ograniczony do 16 MB w pamięci i po osiągnięciu tego limitu grupa wierszy zostanie skompresowana. Jeśli wystąpi taka sytuacja, rozważ izolowanie problematycznej kolumny do oddzielnej tabeli.

Jak oszacować wymagania dotyczące pamięci

Maksymalna wymagana pamięć do skompresowania jednej grupy wierszy wynosi w przybliżeniu w następujący sposób:

  • 72 MB +
  • #rows * #columns * 8 bajtów +
  • #rows * #short-string-columns * 32 bajtów +
  • #long-string-columns * 16 MB dla słownika kompresji danych

Uwaga

Gdzie kolumny krótkiego ciągu używają typów danych ciągów <= 32 bajtów, a kolumny długiego ciągu używają typów danych ciągów > 32 bajtów.

Długie ciągi są kompresowane za pomocą metody kompresji przeznaczonej do kompresowania tekstu. Ta metoda kompresji używa słownika do przechowywania wzorców tekstu. Maksymalny rozmiar słownika to 16 MB. W grupie wierszy (rowgroup) istnieje tylko jeden słownik dla każdej długiej kolumny tekstowej.

Sposoby zmniejszenia wymagań dotyczących pamięci

Użyj poniższych technik, aby zmniejszyć wymagania pamięciowe podczas kompresowania segmentów wierszy do indeksów kolumnowych.

Użyj mniejszej liczby kolumn

Jeśli to możliwe, zaprojektuj tabelę z mniejszą liczbą kolumn. Gdy grupa wierszy jest kompresowana do magazynu kolumn, indeks magazynu kolumn kompresuje oddzielnie każdy segment kolumny. W związku z tym wymagania dotyczące pamięci w celu skompresowania grupy wierszy zwiększają się wraz ze wzrostem liczby kolumn.

Użyj mniejszej liczby kolumn tekstowych

Kolumny typów danych ciągów wymagają więcej pamięci niż typy danych liczbowych i dat. Aby zmniejszyć wymagania dotyczące pamięci, rozważ usunięcie kolumn ciągów z tabel faktów i umieszczenie ich w mniejszych tabelach wymiarów.

Dodatkowe wymagania dotyczące pamięci dla kompresji ciągów:

  • Typy danych ciągów do 32 znaków mogą wymagać 32 dodatkowych bajtów na wartość.
  • Typy danych ciągów z więcej niż 32 znakami są kompresowane przy użyciu metod słownika. Każda kolumna w grupie wierszy może wymagać maksymalnie dodatkowego 16 MB do skompilowania słownika.

Unikaj nadmiernego partycjonowania

Indeksy kolumnowe tworzą jedną lub więcej grup wierszy na każdą partycję. W przypadku magazynowania danych w usłudze Azure Synapse Analytics liczba partycji rośnie szybko, ponieważ dane są dystrybuowane, a każda dystrybucja jest partycjonowana. Jeśli tabela ma zbyt wiele partycji, może zabraknąć wystarczającej liczby wierszy, aby wypełnić grupy wierszy. Brak wierszy nie powoduje presji na pamięć podczas kompresji, ale prowadzi do tego, że grupy wierszy nie osiągają optymalnej wydajności zapytań w magazynie kolumn.

Innym powodem uniknięcia nadmiernego partycjonowania jest obciążenie pamięcią podczas ładowania wierszy do indeksu magazynu kolumn w tabeli podzielonej na partycje. Podczas ładowania wiele partycji może odbierać przychodzące wiersze, które są przechowywane w pamięci, dopóki każda partycja nie będzie mieć wystarczającej liczby wierszy do skompresowania. Posiadanie zbyt wielu partycji powoduje dodatkowe wykorzystanie pamięci.

Uproszczenie zapytania ładowania

Baza danych udostępnia przydział pamięci dla zapytania wśród wszystkich operatorów w zapytaniu. Gdy zapytanie obciążeniowe ma złożone sortowanie i sprzężenia, pamięć dostępna dla kompresji zostanie zmniejszona.

Zaprojektuj zapytanie dotyczące ładowania, aby skoncentrować się tylko na ładowaniu zapytania. Jeśli musisz uruchomić przekształcenia na danych, uruchom je oddzielnie od zapytania obciążeniowego. Na przykład przygotuj dane w tabeli stert, uruchom przekształcenia, a następnie załaduj tabelę przejściową do indeksu magazynu kolumn.

Dostosuj opcję MAXDOP

Każda dystrybucja kompresuje grupy wierszy do magazynu kolumnowego równolegle, gdy dostępnych jest więcej niż jeden rdzeń procesora na dystrybucję. Równoległość wymaga dodatkowych zasobów pamięci, co może prowadzić do przeciążenia pamięci i przycinania grup wierszy.

Aby zmniejszyć wykorzystanie pamięci, możesz użyć wskazówki zapytania MAXDOP, aby wymusić uruchomienie operacji ładowania w trybie szeregowym w każdej dystrybucji.

CREATE TABLE MyFactSalesQuota
WITH (DISTRIBUTION = ROUND_ROBIN)
AS SELECT * FROM FactSalesQuota
OPTION (MAXDOP 1);

Sposoby przydzielania większej ilości pamięci

Rozmiar jednostek DWU i klasa zasobów użytkownika określają, ile pamięci jest dostępne dla zapytania użytkownika. Aby zwiększyć przydział pamięci dla zapytania obciążeniowego, można zwiększyć liczbę jednostek DWU lub zwiększyć klasę zasobów.

  • Aby zwiększyć liczbę jednostek DWU, zobacz Jak mogę skalować wydajność?
  • Aby zmienić klasę zasobów dla zapytania, zobacz przykład zmiany klasy zasobów użytkownika .

Następne kroki

Aby znaleźć więcej sposobów poprawy wydajności w usłudze Synapse SQL, zobacz Omówienie wydajności.

  • Last updated on