Maksymalizowanie jakości grupy wierszy pod kątem wydajności indeksu magazynu kolumn
Jakość grupy wierszy jest określana przez liczbę wierszy w grupie wierszy. Zwiększenie dostępnej pamięci może zmaksymalizować liczbę wierszy, które indeks magazynu kolumn kompresuje 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ę, skanując segmenty 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 poprawia, 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 ma 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 za mało pamięci, aby skompresować wszystkie wiersze przeznaczone dla każdej grupy wierszy. Gdy występuje ciśnienie pamięci, indeksy magazynu kolumn przycinają rozmiary grup wierszy, aby kompresja do magazynu kolumn mogła zakończyć się powodzeniem.
W przypadku niewystarczają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 Zbiorcze ładowanie do klastrowanego indeksu magazynu kolumn.
Jak monitorować jakość grupy 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 powód przycinania w przypadku przycinania. Aby uzyskać informacje na temat przycinania grupy wierszy, możesz utworzyć następujący widok jako przydatny sposób wykonywania zapytań dotyczących dynamicznego widoku zarządzania.
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 sugeruje, że nie było przycinania, a grupa wierszy ma optymalną jakość). Następujące przyczyny przycinania wskazują przedwczesne przycinanie grupy wierszy:
- BULKLOAD: Ten powód przycinania jest używany, gdy przychodząca partia wierszy 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 różnicowego), 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, wymagana jest pewna ilość pamięci roboczej przez aparat. Gdy 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 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ż odizolowanie problematycznej kolumny do oddzielnej tabeli.
Jak oszacować wymagania dotyczące pamięci
Maksymalna wymagana pamięć do skompresowania jednej grupy wierszy wynosi w przybliżeniu:
- 72 MB +
- #rows * #columns * 8 bajtów +
- #rows * #short-string-columns * 32 bajty +
- #long-string-columns * 16 MB dla słownika kompresji
Uwaga
Gdzie krótkie kolumny ciągów używają typów <danych ciągu = 32 bajtów i kolumn długich ciągów używają typów > danych ciągu 32 bajtów.
Długie ciągi są kompresowane przy użyciu 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ągów.
Sposoby zmniejszenia wymagań dotyczących pamięci
Użyj poniższych technik, aby zmniejszyć wymagania dotyczące pamięci na potrzeby kompresji grup wierszy do indeksów magazynu kolumn.
Używanie mniejszej liczby kolumn
Jeśli to możliwe, zaprojektuj tabelę z mniejszą liczbą kolumn. Gdy grupa wierszy zostanie skompresowana do magazynu kolumn, indeks magazynu kolumn kompresuje poszczególne segmenty kolumn oddzielnie. W związku z tym wymagania dotyczące pamięci w celu skompresowania grupy wierszy zwiększają się wraz ze wzrostem liczby kolumn.
Używanie mniejszej liczby kolumn ciągów
Kolumny typów danych ciągów wymagają większej ilości 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 16 MB utworzenia słownika.
Unikaj nadmiernego partycjonowania
Indeksy magazynu kolumn tworzą co najmniej jedną grupę wierszy na 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 nie być wystarczającej liczby wierszy, aby wypełnić grupy wierszy. Brak wierszy nie tworzy ciśnienia pamięci podczas kompresji, ale prowadzi do grup wierszy, które nie osiągają najlepszej wydajności zapytań magazynu kolumn.
Innym powodem uniknięcia nadmiernego partycjonowania jest obciążenie pamięcią ł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 ilości wierszy do skompresowania. Zbyt wiele partycji powoduje dodatkowe wykorzystanie pamięci.
Upraszczanie zapytania o ładowanie
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 do kompresji zostanie zmniejszona.
Zaprojektuj zapytanie obciążeniowe, aby skoncentrować się tylko na ładowaniu zapytania. Jeśli musisz uruchomić przekształcenia na danych, uruchom je niezależnie od zapytania ładowania. Na przykład należy przygotować dane w tabeli stertowej, uruchomić przekształcenia, a następnie załadować tabelę przemieszczania do indeksu magazynu kolumn.
Dostosowywanie opcji MAXDOP
Każda dystrybucja kompresuje grupy wierszy do magazynu kolumn równolegle, gdy na dystrybucję jest dostępnych więcej niż jeden rdzeń procesora CPU. Równoległość wymaga dodatkowych zasobów pamięci, co może prowadzić do przycinania pamięci i przycinania grup wierszy.
Aby zmniejszyć ciśnienie pamięci, możesz użyć wskazówki zapytania MAXDOP, aby wymusić uruchomienie operacji ładowania w trybie seryjnym 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 razem określają, ile pamięci jest dostępne dla zapytania użytkownika. Aby zwiększyć przydział pamięci dla zapytania obciążeniowego, możesz 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 w usłudze Synapse SQL, zobacz Omówienie wydajności.