Zwiększanie wydajności zapytań przy użyciu indeksów
Po utworzeniu tabeli domyślnie struktura danych nie zawiera indeksów i jest nazywana stertą. Dobrze zaprojektowana strategia indeksowania może zmniejszyć liczbę operacji we/wy dysku i zużywać mniej zasobów systemowych, co zwiększa wydajność zapytań, zwłaszcza w przypadku używania filtrowania, skanowania i sprzężeń w zapytaniu.
Dedykowane pule SQL mają dostępne następujące opcje indeksowania:
Indeks klastrowanego magazynu kolumn (CCI)
Dedykowane pule SQL tworzą indeks klastrowanego magazynu kolumn, gdy nie określono żadnych opcji indeksu w tabeli i umożliwia korzystanie z adaptacyjnego buforowania. Klastrowane indeksy magazynu kolumn oferują zarówno najwyższy poziom kompresji danych, jak i najlepszą ogólną wydajność zapytań. Klastrowane indeksy magazynu kolumn zwykle przewyższają klastrowane indeksy magazynu wierszy lub tabele stert i są zwykle najlepszym wyborem dla dużych tabel.
Magazyn kolumn działa w segmentach 1048 576 wierszy, które są kompresowane i zoptymalizowane według kolumn. Ta segmentacja dodatkowo pomaga odfiltrować i zmniejszyć dostęp do danych przy użyciu metadanych przechowywanych, które zawierają podsumowanie zakresu i wartości w poszczególnych segmentach podczas optymalizacji zapytań.
Ponieważ interfejsy CCI organizują dane w segmenty, chcemy jak najwięcej nasycenia danych w segmencie, co można zobaczyć powyżej, jest mierzone przez liczbę wierszy w skompresowanej grupie wierszy. aby zoptymalizować jakość segmentu, docelowy co najmniej 100 000 wierszy na skompresowaną grupę wierszy i wydajność będzie nadal widzieć zyski, dopóki nie osiągnie bieżącego maksymalnego rozmiaru.
Użycie widoku podobnego do wymienionego poniżej może pomóc zidentyfikować średnie wiersze na grupę wierszy i zidentyfikować dowolne nieoptymalne wskaźniki CCI. Ostatnia kolumna w tym udostępnionym języku DDL może służyć do odbudowy tych nieoptymalnych indeksów.
CREATE VIEW dbo.vColumnstoreDensity
AS
SELECT
GETDATE() AS [execution_date]
, DB_Name() AS [database_name]
, s.name AS [schema_name]
, t.name AS [table_name]
, COUNT(DISTINCT rg.[partition_number]) AS [table_partition_count]
, SUM(rg.[total_rows]) AS [row_count_total]
, SUM(rg.[total_rows])/COUNT(DISTINCT rg.[distribution_id]) AS [row_count_per_distribution_MAX]
, CEILING ((SUM(rg.[total_rows])*1.0/COUNT(DISTINCT rg.[distribution_id]))/1048576) AS [rowgroup_per_distribution_MAX]
, SUM(CASE WHEN rg.[State] = 0 THEN 1 ELSE 0 END) AS [INVISIBLE_rowgroup_count]
, SUM(CASE WHEN rg.[State] = 0 THEN rg.[total_rows] ELSE 0 END) AS [INVISIBLE_rowgroup_rows]
, MIN(CASE WHEN rg.[State] = 0 THEN rg.[total_rows] ELSE NULL END) AS [INVISIBLE_rowgroup_rows_MIN]
, MAX(CASE WHEN rg.[State] = 0 THEN rg.[total_rows] ELSE NULL END) AS [INVISIBLE_rowgroup_rows_MAX]
, AVG(CASE WHEN rg.[State] = 0 THEN rg.[total_rows] ELSE NULL END) AS [INVISIBLE_rowgroup_rows_AVG]
, SUM(CASE WHEN rg.[State] = 1 THEN 1 ELSE 0 END) AS [OPEN_rowgroup_count]
, SUM(CASE WHEN rg.[State] = 1 THEN rg.[total_rows] ELSE 0 END) AS [OPEN_rowgroup_rows]
, MIN(CASE WHEN rg.[State] = 1 THEN rg.[total_rows] ELSE NULL END) AS [OPEN_rowgroup_rows_MIN]
, MAX(CASE WHEN rg.[State] = 1 THEN rg.[total_rows] ELSE NULL END) AS [OPEN_rowgroup_rows_MAX]
, AVG(CASE WHEN rg.[State] = 1 THEN rg.[total_rows] ELSE NULL END) AS [OPEN_rowgroup_rows_AVG]
, SUM(CASE WHEN rg.[State] = 2 THEN 1 ELSE 0 END) AS [CLOSED_rowgroup_count]
, SUM(CASE WHEN rg.[State] = 2 THEN rg.[total_rows] ELSE 0 END) AS [CLOSED_rowgroup_rows]
, MIN(CASE WHEN rg.[State] = 2 THEN rg.[total_rows] ELSE NULL END) AS [CLOSED_rowgroup_rows_MIN]
, MAX(CASE WHEN rg.[State] = 2 THEN rg.[total_rows] ELSE NULL END) AS [CLOSED_rowgroup_rows_MAX]
, AVG(CASE WHEN rg.[State] = 2 THEN rg.[total_rows] ELSE NULL END) AS [CLOSED_rowgroup_rows_AVG]
, SUM(CASE WHEN rg.[State] = 3 THEN 1 ELSE 0 END) AS [COMPRESSED_rowgroup_count]
, SUM(CASE WHEN rg.[State] = 3 THEN rg.[total_rows] ELSE 0 END) AS [COMPRESSED_rowgroup_rows]
, SUM(CASE WHEN rg.[State] = 3 THEN rg.[deleted_rows] ELSE 0 END) AS [COMPRESSED_rowgroup_rows_DELETED]
, MIN(CASE WHEN rg.[State] = 3 THEN rg.[total_rows] ELSE NULL END) AS [COMPRESSED_rowgroup_rows_MIN]
, MAX(CASE WHEN rg.[State] = 3 THEN rg.[total_rows] ELSE NULL END) AS [COMPRESSED_rowgroup_rows_MAX]
, AVG(CASE WHEN rg.[State] = 3 THEN rg.[total_rows] ELSE NULL END) AS [COMPRESSED_rowgroup_rows_AVG]
, 'ALTER INDEX ALL ON ' + s.name + '.' + t.NAME + ' REBUILD;' AS [Rebuild_Index_SQL]
FROM sys.[pdw_nodes_column_store_row_groups] rg
JOIN sys.[pdw_nodes_tables] nt ON rg.[object_id] = nt.[object_id]
AND rg.[pdw_node_id] = nt.[pdw_node_id]
AND rg.[distribution_id] = nt.[distribution_id]
JOIN sys.[pdw_table_mappings] mp ON nt.[name] = mp.[physical_name]
JOIN sys.[tables] t ON mp.[object_id] = t.[object_id]
JOIN sys.[schemas] s ON t.[schema_id] = s.[schema_id]
GROUP BY
s.[name]
, t.[name];
Po utworzeniu widoku uruchom następujące zapytanie, aby zidentyfikować tabele z grupami wierszy z mniej niż 100 tys. wierszy. Po rozwiązaniu tych problemów zwiększ próg, aby wyszukać dodatkowe możliwości uzyskania optymalnej jakości segmentu.
SELECT *
FROM [dbo].[vColumnstoreDensity]
WHERE COMPRESSED_rowgroup_rows_AVG < 100000
OR INVISIBLE_rowgroup_rows_AVG < 100000;
Jak uzyskać wyniki i czego szukać w wynikowej analizie grup wierszy.
| Kolumna | Jak używać wyników |
|---|---|
| [liczba_partycji_w_tabeli] | Jeśli tabela jest podzielona na partycje, możesz spodziewać się wyższych liczby otwartych grup wierszy. Każda partycja w dystrybucji może teoretycznie mieć skojarzona z nią otwartą grupę wierszy. Uwzględnij to w analizie. Mała tabela, która została podzielona na partycje, może zostać zoptymalizowana przez całkowite usunięcie partycjonowania, ponieważ poprawiłoby to kompresję. |
| [row_count_total] | Łączna liczba wierszy dla tabeli. Na przykład możesz użyć tej wartości do obliczenia wartości procentowej wierszy w stanie skompresowanym. |
| [row_count_per_distribution_MAX] | Jeśli wszystkie wiersze są równomiernie rozłożone, ta wartość będzie docelową liczbą wierszy na dystrybucję. Porównaj tę wartość z compressed_rowgroup_count. |
| [SKOMPRESOWANE_rowgroup_rows] | Całkowita liczba wierszy w formacie magazynu kolumn dla tabeli. |
| [COMPRESSED_rowgroup_rows_AVG] | Jeśli średnia liczba wierszy jest znacznie mniejsza niż maksymalna liczba wierszy dla grupy wierszy, rozważ użycie funkcji CTAS lub ALTER INDEX REBUILD w celu ponownego skompresowania danych |
| [COMPRESSED_rowgroup_count] | Liczba grup wierszy w formacie magazynu kolumn. Jeśli ta liczba jest bardzo wysoka względem tabeli, oznacza to, że gęstość magazynu kolumn jest niska. |
| [COMPRESSED_rowgroup_rows_DELETED] | Wiersze są logicznie usuwane w formacie magazynu kolumn. Jeśli liczba jest duża względem rozmiaru tabeli, rozważ ponowne utworzenie partycji lub ponowne skompilowanie indeksu, ponieważ spowoduje to usunięcie ich fizycznie. |
| [SKOMPRESOWANE_grupa_wierszy_MIN] | Użyj tej funkcji z kolumnami AVG i MAX, aby zrozumieć zakres wartości dla grup wierszy w magazynie kolumn. Niska liczba progowa obciążenia (102 400 na rozkład wyrównany do partycji) sugeruje, że optymalizacje są dostępne w obciążeniu danych |
| [COMPRESSED_rowgroup_rows_MAX] | Jak wyżej |
| [OPEN_rowgroup_count] | Otwarte grupy wierszy są normalne. Można by rozsądnie oczekiwać jednej grupy wierszy OPEN na rozkład tabeli (60). Nadmierne liczby sugerują ładowanie danych między partycjami. Dokładnie sprawdź strategię partycjonowania, aby upewnić się, że jest to dźwięk |
| [OPEN_rowgroup_rows] | Każda grupa wierszy może zawierać maksymalnie 1 048 576 wierszy. Użyj tej wartości, aby zobaczyć, jak pełne są obecnie otwarte grupy wierszy |
| [OPEN_rowgroup_rows_MIN] | Otwarte grupy wskazują, że dane są ładowane do tabeli lub że poprzednie obciążenie rozlało pozostałe wiersze do tej grupy wierszy. Użyj kolumn MIN, MAX, AVG, aby zobaczyć, ile danych jest usiadnych w grupach wierszy OPEN. W przypadku małych tabel może to być 100% wszystkich danych. W tym przypadku ALTER INDEX REBUILD w celu wymuszenia użycia danych w magazynie kolumn. |
| [OPEN_rowgroup_rows_MAX] | Zobacz powyżej |
| [OPEN_rowgroup_rows_AVG] | Zobacz powyżej |
| [CLOSED_rowgroup_rows] | Spójrz na zamknięte wiersze grupy wierszy jako sprawdzanie kondycji. |
| [CLOSED_rowgroup_count] | Liczba zamkniętych grup wierszy powinna być niska, jeśli w ogóle jest widoczna. Zamknięte grupy wierszy można przekonwertować na skompresowane grupy wierszy przy użyciu ALTER INDEX ... ZREORGANIZOWANIE polecenia. Nie jest to jednak zwykle wymagane. Zamknięte grupy są automatycznie konwertowane na grupy wierszy magazynu kolumn przez proces "krotki krotki". |
| [ZAMKNIĘTY_rowgroup_rows_MIN] | Zamknięte grupy wierszy powinny mieć bardzo wysoką częstotliwość wypełniania. Jeśli szybkość wypełnienia zamkniętej grupy wierszy jest niska, wymagana jest dalsza analiza magazynu kolumn. |
| [CLOSED_rowgroup_rows_MAX] | Zobacz powyżej |
| [CLOSED_rowgroup_rows_AVG] | Zobacz powyżej |
| [Rebuild_Index_SQL] | Sql do ponownego kompilowania indeksu magazynu kolumn dla tabeli |
Zagadnienia dotyczące tego, kiedy klastrowane indeksy magazynu kolumn mogą nie być dobrym rozwiązaniem
- Tabele magazynu kolumn nie obsługują funkcji varchar(max), nvarchar(max) i varbinary(max). Rozważ zamiast tego stertę lub indeks klastrowany.
- Tabele magazynu kolumn mogą być mniej wydajne w przypadku danych przejściowych. Rozważ stertę, a może nawet tabele tymczasowe.
- Małe tabele z mniej niż 60 milionami wierszy. Rozważ tabele stert.
Indeks klastrowany
Indeksy klastrowanego magazynu wierszy definiują sposób przechowywania samej tabeli uporządkowanej według kolumn używanych dla indeksu. W tabeli może znajdować się tylko jeden indeks klastrowany.
Indeksy klastrowane najlepiej nadają się do zapytań i sprzężeń, które wymagają skanowania zakresów danych, najlepiej w takiej samej kolejności, jak zdefiniowany indeks.
Indeks nieklasterowany
Indeks nieklasterowany można zdefiniować w tabeli lub widoku z indeksem klastrowanym lub stertą. Każdy wiersz indeksu w indeksie, który nie jest klastrowany, zawiera wartość klucza bez klastra i lokalizator wierszy. Jest to struktura danych oddzielna/dodatkowa dla tabeli lub sterta. W tabeli można utworzyć wiele indeksów nieklasterowanych.
Indeksy nieklaterowane najlepiej używać w przypadku kolumn w sprzężeniu, grupowania według instrukcji lub klauzul, które zwracają dokładne dopasowanie lub kilka wierszy.
Najlepsze rozwiązania dotyczące indeksów
Aby uzyskać dodatkowe informacje na temat tego, jak upewnić się, że indeksowanie ma znaczący wpływ, skorzystaj z poniższego łącza: Najlepsze praktyki dotyczące indeksowania