Maksymalizacja jakości grup wierszy dla indeksów magazynu kolumn w dedykowanej puli SQL

Jakość grupy wierszy jest określana przez liczbę wierszy w grupie wierszy. 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 poprawić 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, maksymalizacja liczby wierszy w każdej grupie wierszy zwiększa wydajność zapytań.

Gdy grupy wierszy mają dużą liczbę wierszy, kompresja danych zwiększa 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 magazynu kolumn. Grupa wierszy może zawierać maksymalnie 1 048 576 wierszy.

Nie można mieć maksymalnej liczby wierszy na grupę wierszy. Indeksy magazynu kolumn osiągają dobrą wydajność, gdy 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 wykorzystanie pamięci, indeksy magazynu kolumn przycinają rozmiary grup wierszy, aby kompresja do magazynu kolumn mogła zakończyć się powodzeniem.

Jeśli za mało 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 Zbiorcze ładowanie do klastrowanego indeksu magazynu kolumn.

Jak monitorować jakość grupy wierszy

Sys.dm_pdw_nodes_db_column_store_row_group_physical_stats 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 przyczyna przycinania, jeśli wystąpiło przycinanie.

Możesz utworzyć następujący widok jako przydatny sposób wykonywania zapytań względem tego dynamicznego widoku zarządzania, aby uzyskać informacje na temat przycinania 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;

Trim_reason_desc informuje, czy grupa wierszy została przycięta (trim_reason_desc = NO_TRIM wskazuje, że nie było przycinania, a grupa wierszy jest optymalna. Następujące przyczyny przycinania wskazują na przedwczesne przycinanie grupy wierszy:

• BULKLOAD: Ta przyczyna przycinania jest używana, gdy przychodząca partia wierszy dla obciążenia miała mniej niż 1 milion wierszy. Aparat utworzy skompresowane grupy wierszy, jeśli jest wstawionych więcej niż 100 000 wierszy (w przeciwieństwie do wstawiania do magazynu delty), ale ustawia przyczynę przycinania na BULKLOAD. W tym scenariuszu rozważ zwiększenie obciążenia wsadowego w celu uwzględnienia większej liczby wierszy. Ponadto przeszacuj schemat partycjonowania, aby upewnić się, że nie jest on zbyt szczegółowy, ponieważ grupy wierszy nie mogą obejmować 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: Przyczyna przycinania tej 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

Aby wyświetlić oszacowanie wymagań dotyczących pamięci w celu skompresowania grupy wierszy o maksymalnym rozmiarze do indeksu magazynu kolumn, rozważ utworzenie widoku przykładowego dbo.vCS_mon_mem_grant. To zapytanie pokazuje rozmiar przydziału pamięci wymaganego przez grupę wierszy do kompresji w magazynie kolumn.

Maksymalna ilość pamięci wymaganej do skompresowania jednej grupy wierszy wynosi około

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

Uwaga

Kolumny z krótkimi ciągami używają typów danych ciągów <= 32 bajtów, a kolumny ciągów używają typów danych ciągów o > długości 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 istnieje tylko jeden słownik dla każdej długiej kolumny ciągu.

Sposoby zmniejszenia wymagań dotyczących pamięci

Użyj poniższych technik, aby zmniejszyć wymagania dotyczące pamięci dotyczące kompresowania grup wierszy do indeksów magazynu kolumn.

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 ciągów

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 dotyczące kompresji ciągu:

• 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 magazynu kolumn tworzą co najmniej jedną grupę wierszy na partycję. W przypadku dedykowanej puli SQL 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 nie być wystarczającej liczby wierszy, aby wypełnić grupy wierszy. Brak wierszy nie tworzy ciśnienia pamięci podczas kompresji. Jednak prowadzi to do grup wierszy, które nie osiągają najlepszej wydajności zapytań magazynu kolumn.

Innym powodem unikania nadmiernego partycjonowania jest obciążenie pamięci podczas ładowania wierszy do indeksu magazynu kolumn w tabeli partycjonowanej.

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. Zbyt wiele partycji tworzy dodatkowe wykorzystanie pamięci.

Upraszczanie 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, ilość dostępnej pamięci dla kompresji zostanie zmniejszona.

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

Porada

Możesz również najpierw załadować dane, a następnie użyć systemu MPP do przekształcenia danych.

Dostosowywanie opcji MAXDOP

Każda dystrybucja kompresuje grupy wierszy do magazynu kolumn równolegle, gdy jest dostępny więcej niż jeden rdzeń procesora CPU na dystrybucję.

Równoległość wymaga dodatkowych zasobów pamięci, co może prowadzić do wykorzystania 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 ramach 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ążenia, można zwiększyć liczbę jednostek DWU lub zwiększyć klasę zasobów.

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

Następne kroki

Aby znaleźć więcej sposobów poprawy wydajności dedykowanej puli SQL, zobacz Omówienie wydajności.