Strojenie wydajności za pomocą uporządkowanego klastrowanego indeksu magazynu kolumn

  • Artykuł

Dotyczy: dedykowane pule SQL usługi Azure Synapse Analytics, SQL Server 2022 (16.x) i nowsze

Gdy użytkownicy wysyłają zapytania do tabeli magazynu kolumn w dedykowanej puli SQL, optymalizator sprawdza minimalne i maksymalne wartości przechowywane w każdym segmencie. Segmenty spoza granic predykatu zapytania nie są odczytywane z dysku na pamięć. Zapytanie może zakończyć się szybciej, jeśli liczba segmentów do odczytu i ich całkowity rozmiar są małe.

Uporządkowany i nieskondycyjny indeks magazynu kolumn

Domyślnie dla każdej tabeli utworzonej bez opcji indeksu składnik wewnętrzny (konstruktor indeksu) tworzy na nim nieskondany indeks magazynu kolumn (CCI). Dane w każdej kolumnie są kompresowane do oddzielnego segmentu grupy wierszy CCI. Istnieją metadane dla zakresu wartości każdego segmentu, więc segmenty, które wykraczają poza granice predykatu zapytania, nie są odczytywane z dysku podczas wykonywania zapytania. CCI oferuje najwyższy poziom kompresji danych i zmniejsza rozmiar segmentów do odczytu, dzięki czemu zapytania mogą działać szybciej. Jednak ponieważ konstruktor indeksów nie sortuje danych przed skompresowaniem ich do segmentów, segmenty z nakładającymi się zakresami wartości mogą wystąpić, co powoduje, że zapytania odczytują więcej segmentów z dysku i mogą trwać dłużej.

Uporządkowane indeksy magazynu kolumn klastrowanych dzięki umożliwieniu wydajnej eliminacji segmentów, co znacznie przyspiesza wydajność, pomijając duże ilości uporządkowanych danych, które nie są zgodne z predykatem zapytania. Podczas tworzenia uporządkowanego CCI dedykowany aparat puli SQL sortuje istniejące dane w pamięci według kluczy zamówienia, zanim konstruktor indeksu skompresuje je do segmentów indeksu. W przypadku posortowanych danych nakładanie się segmentów jest zmniejszane, dzięki czemu zapytania mają wydajniejszą eliminację segmentów i tym samym szybciej wydajność, ponieważ liczba segmentów do odczytu z dysku jest mniejsza. Jeśli wszystkie dane można sortować w pamięci jednocześnie, można uniknąć nakładania się segmentów. Ze względu na duże tabele w magazynach danych ten scenariusz nie zdarza się często.

Aby sprawdzić zakresy segmentów dla kolumny, uruchom następujące polecenie z nazwą tabeli i nazwą kolumny:

SELECT o.name, pnp.index_id,
cls.row_count, pnp.data_compression_desc,
pnp.pdw_node_id, pnp.distribution_id, cls.segment_id,
cls.column_id,
cls.min_data_id, cls.max_data_id,
cls.max_data_id-cls.min_data_id as difference
FROM sys.pdw_nodes_partitions AS pnp
   JOIN sys.pdw_nodes_tables AS Ntables ON pnp.object_id = NTables.object_id AND pnp.pdw_node_id = NTables.pdw_node_id
   JOIN sys.pdw_table_mappings AS Tmap  ON NTables.name = TMap.physical_name AND substring(TMap.physical_name,40, 10) = pnp.distribution_id
   JOIN sys.objects AS o ON TMap.object_id = o.object_id
   JOIN sys.pdw_nodes_column_store_segments AS cls ON pnp.partition_id = cls.partition_id AND pnp.distribution_id  = cls.distribution_id
JOIN sys.columns as cols ON o.object_id = cols.object_id AND cls.column_id = cols.column_id
WHERE o.name = '<Table Name>' and cols.name = '<Column Name>'  and TMap.physical_name  not like '%HdTable%'
ORDER BY o.name, pnp.distribution_id, cls.min_data_id;

Uwaga

W uporządkowanej tabeli CCI nowe dane wynikające z tej samej partii DML lub operacji ładowania danych są sortowane w tej partii, nie ma globalnego sortowania wszystkich danych w tabeli. Użytkownicy mogą ponownie skompilować uporządkowane CCI, aby posortować wszystkie dane w tabeli. W dedykowanej puli SQL indeks magazynu kolumn REBUILD jest operacją offline. W przypadku tabeli podzielonej na partycje kompilacja jest wykonywana pojedynczo po jednej partycji. Dane w partycji, która jest odbudowywana, są "w trybie offline" i niedostępne do momentu ukończenia kompilacji dla tej partycji.

Wydajność zapytań

Wydajność zapytania z uporządkowanego CCI zależy od wzorców zapytań, rozmiaru danych, sposobu sortowania danych, fizycznej struktury segmentów oraz klasy DWU i klasy zasobów wybranej do wykonania zapytania. Użytkownicy powinni przejrzeć wszystkie te czynniki przed wybraniem kolumn porządkowania podczas projektowania uporządkowanej tabeli CCI.

Zapytania ze wszystkimi tymi wzorcami są zwykle uruchamiane szybciej przy użyciu uporządkowanego CCI.

  1. Zapytania mają równość, nierówności lub predykaty zakresu
  2. Kolumny predykatu i uporządkowane kolumny CCI są takie same.

W tym przykładzie tabela T1 ma indeks klastrowanego magazynu kolumn uporządkowany w sekwencji Col_C, Col_B i Col_A.

CREATE CLUSTERED COLUMNSTORE INDEX MyOrderedCCI ON  T1
ORDER (Col_C, Col_B, Col_A);

Wydajność zapytania 1 i zapytania 2 może przynieść więcej korzyści niż w przypadku innych zapytań, ponieważ odwołują się do wszystkich uporządkowanych kolumn CCI.

-- Query #1:

SELECT * FROM T1 WHERE Col_C = 'c' AND Col_B = 'b' AND Col_A = 'a';

-- Query #2

SELECT * FROM T1 WHERE Col_B = 'b' AND Col_C = 'c' AND Col_A = 'a';

-- Query #3
SELECT * FROM T1 WHERE Col_B = 'b' AND Col_A = 'a';

-- Query #4
SELECT * FROM T1 WHERE Col_A = 'a' AND Col_C = 'c';

Wydajność ładowania danych

Wydajność ładowania danych do uporządkowanej tabeli CCI jest podobna do tabeli partycjonowanej. Ładowanie danych do uporządkowanej tabeli CCI może trwać dłużej niż nieskonsortowa tabela CCI ze względu na operację sortowania danych, jednak zapytania mogą działać szybciej później przy użyciu uporządkowanego CCI.

Oto przykładowe porównanie wydajności ładowania danych do tabel z różnymi schematami.

Wykres słupkowy przedstawiający porównanie wydajności ładowania danych do tabel z różnymi schematami.

Oto przykładowe porównanie wydajności zapytań między CCI i uporządkowanym CCI.

Wykres słupkowy porównujący wydajność podczas data_loading. Uporządkowany indeks magazynu kolumn w klastrze ma krótszy czas trwania.

Zmniejszanie nakładających się segmentów

Liczba nakładających się segmentów zależy od rozmiaru danych do sortowania, dostępnej pamięci i maksymalnego stopnia równoległości (MAXDOP) podczas uporządkowanego tworzenia CCI. Poniższe strategie zmniejszają nakładanie się segmentów podczas tworzenia uporządkowanego CCI.

  • Użyj xlargerc klasy zasobów w większej klasie DWU, aby umożliwić więcej pamięci do sortowania danych przed skompresowanie danych przez konstruktora indeksów do segmentów. Po wybraniu segmentu indeksu nie można zmienić fizycznej lokalizacji danych. Nie ma sortowania danych w segmencie ani w różnych segmentach.

  • Utwórz uporządkowany zestaw CCI za pomocą polecenia OPTION (MAXDOP = 1). Każdy wątek używany do tworzenia uporządkowanego CCI działa na podzestawie danych i sortuje je lokalnie. Nie ma globalnego sortowania danych posortowanych według różnych wątków. Użycie wątków równoległych może skrócić czas tworzenia uporządkowanego CCI, ale spowoduje wygenerowanie większej liczby nakładających się segmentów niż użycie jednego wątku. Użycie pojedynczej operacji wątkowej zapewnia najwyższą jakość kompresji. Przykład:

CREATE TABLE Table1 WITH (DISTRIBUTION = HASH(c1), CLUSTERED COLUMNSTORE INDEX ORDER(c1) )
AS SELECT * FROM ExampleTable
OPTION (MAXDOP 1);

Uwaga

Obecnie w dedykowanych pulach SQL w usłudze Azure Synapse Analytics opcja MAXDOP jest obsługiwana tylko podczas tworzenia uporządkowanej tabeli CCI przy użyciu CREATE TABLE AS SELECT polecenia . Tworzenie uporządkowanego CCI za pośrednictwem CREATE INDEX polecenia lub CREATE TABLE nie obsługuje opcji MAXDOP. To ograniczenie nie dotyczy SQL Server 2022 i nowszych wersji, w których można określić wartość MAXDOP za pomocą CREATE INDEX poleceń lub CREATE TABLE .

  • Przed załadowaniem ich do tabel należy wstępnie posortować dane według kluczy sortowania.

Oto przykład uporządkowanej dystrybucji tabeli CCI, która ma zerowy segment nakładający się na powyższe zalecenia. Uporządkowana tabela CCI jest tworzona w bazie danych DWU1000c za pośrednictwem CTAS z tabeli sterty o rozmiarze 20 GB przy użyciu polecenia MAXDOP 1 i xlargerc. CCI jest uporządkowane w kolumnie BIGINT bez duplikatów.

Zrzut ekranu przedstawiający dane tekstowe bez nakładania się segmentów.

Tworzenie uporządkowanego CCI w dużych tabelach

Tworzenie uporządkowanego CCI jest operacją w trybie offline. W przypadku tabel bez partycji dane nie będą dostępne dla użytkowników do momentu ukończenia uporządkowanego procesu tworzenia CCI. W przypadku tabel podzielonych na partycje, ponieważ aparat tworzy uporządkowaną partycję CCI według partycji, użytkownicy nadal mogą uzyskiwać dostęp do danych w partycjach, w których uporządkowane tworzenie CCI nie jest przetwarzane. Tej opcji można użyć, aby zminimalizować przestoje podczas uporządkowanego tworzenia CCI w dużych tabelach:

  1. Utwórz partycje w docelowej dużej tabeli (o nazwie Table_A).
  2. Utwórz pustą uporządkowaną tabelę CCI (o nazwie Table_B) z tą samą tabelą i schematem partycji co Table_A.
  3. Przełącz jedną partycję z na Table_ATable_B.
  4. Uruchom polecenie , ALTER INDEX <Ordered_CCI_Index> ON <Table_B> REBUILD PARTITION = <Partition_ID> aby ponownie skompilować partycję przełączoną w systemie Table_B.
  5. Powtórz krok 3 i 4 dla każdej partycji w elemencie Table_A.
  6. Gdy wszystkie partycje zostaną przełączone z Table_A na Table_B i zostały ponownie skompilowane, upuść Table_Ai zmień nazwę Table_B na Table_A.

Porada

W przypadku dedykowanej tabeli puli SQL ze uporządkowanym zestawem CCI funkcja ALTER INDEX REBUILD ponownie posortuje dane przy użyciu polecenia tempdb. Monitorowanie tempdb podczas operacji ponownej kompilacji. Jeśli potrzebujesz więcej tempdb miejsca, przeprowadź skalowanie w górę puli. Skaluj z powrotem w dół po zakończeniu ponownego kompilowania indeksu.

W przypadku dedykowanej tabeli puli SQL ze uporządkowaną funkcją CCI funkcja ALTER INDEX REORGANIZE nie sortuje ponownie danych. Aby uzyskać dostęp do danych, użyj polecenia ALTER INDEX REBUILD.

Aby uzyskać więcej informacji na temat uporządkowanej konserwacji CCI, zobacz Optymalizowanie klastrowanych indeksów magazynu kolumn.

Różnice funkcji w funkcjach SQL Server 2022

SQL Server 2022 (16.x) wprowadzono uporządkowane indeksy magazynu kolumn klastrowanych podobne do funkcji w dedykowanych pulach SQL Azure Synapse.

  • Obecnie tylko SQL Server 2022 (16.x) i nowszych wersjach obsługują rozszerzone funkcje eliminacji segmentów magazynu kolumn klastra dla typów danych ciągów, binarnych i guid oraz typu danych typu data datetimeoffset w celu skalowania większego niż dwa. Wcześniej ta eliminacja segmentu ma zastosowanie do typów danych liczbowych, daty i godziny oraz typu danych datetimeoffset o skali mniejszej lub równej dwóch.
  • Obecnie tylko SQL Server 2022 (16.x) i nowsze wersje obsługują eliminację grup wierszy magazynu kolumn klastra dla prefiksu LIKE predykatów, na przykład column LIKE 'string%'. Eliminacja segmentów nie jest obsługiwana w przypadku użycia prefiksu LIKE, takiego jak column LIKE '%string'.

Aby uzyskać więcej informacji, zobacz Co nowego w indeksach magazynu kolumn.

Przykłady

A. Aby sprawdzić uporządkowane kolumny i kolejność porządkową:

SELECT object_name(c.object_id) table_name, c.name column_name, i.column_store_order_ordinal
FROM sys.index_columns i
JOIN sys.columns c ON i.object_id = c.object_id AND c.column_id = i.column_id
WHERE column_store_order_ordinal <>0;

B. Aby zmienić porządkowość kolumn, dodaj lub usuń kolumny z listy zamówień lub zmień wartość z CCI na uporządkowaną CCI:

CREATE CLUSTERED COLUMNSTORE INDEX InternetSales ON dbo.InternetSales
ORDER (ProductKey, SalesAmount)
WITH (DROP_EXISTING = ON);

Następne kroki