Ocena i poprawianie kondycji klastrowanego indeksu magazynu kolumn w dedykowanej puli SQL
Dotyczy: Azure Synapse Analytics
W tym artykule przedstawiono nieco inne podejście do oceny kondycji klastrowanego indeksu magazynu kolumn (CCI). Wykonaj kroki opisane w poniższych sekcjach lub wykonaj kroki opisane w notesie za pośrednictwem narzędzia Azure Data Studio.
Uwaga 16.
Przed podjęciem próby otwarcia tego notesu upewnij się, że program Azure Data Studio jest zainstalowany na komputerze lokalnym. Aby go zainstalować, przejdź do artykułu Dowiedz się, jak zainstalować program Azure Data Studio.
Ogólnie rzecz biorąc, dwa główne czynniki wpływają na jakość CCI:
Kompaktowanie grup wierszy i metadanych — rzeczywista liczba grup wierszy jest zbliżona do idealnej liczby wierszy w grupie wierszy.
Skompresowane grupy wierszy — grupy wierszy używają kompresji magazynu kolumn.
Inne warunki, takie jak małe tabele, tabele podzielone na partycje lub tabele podzielone na partycje, są prawdopodobnie niskiej jakości lub kondycji. Jednak te warunki są lepiej klasyfikowane jako możliwości poprawy projektu, które można ocenić w kroku 4.
Krok 1. Analizowanie podsumowania kondycji klastrowanego indeksu magazynu kolumn
Użyj następującego zapytania, aby pobrać jeden wiersz metryk.
WITH cci_detail AS (
SELECT t.object_id,
rg.partition_number,
COUNT(*) AS total_rowgroup_count,
SUM(CASE WHEN rg.state = 1 THEN 1 END) AS open_rowgroup_count,
CEILING((SUM(rg.[total_rows]) - SUM(rg.deleted_rows))/COUNT(DISTINCT rg.distribution_id)/1048576.) * COUNT(DISTINCT rg.distribution_id) AS [ideal_rowgroup_count],
SUM(rg.size_in_bytes/1024/1024.) AS size_in_mb,
SUM(CASE WHEN rg.state = 1 THEN rg.size_in_bytes END /1024/1024.) AS open_size_in_mb
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
GROUP BY t.object_id,
rg.partition_number
)
SELECT COUNT(DISTINCT object_id) AS tables_assessed_count,
COUNT(*) AS partitions_assessed_count,
SUM(total_rowgroup_count) AS actual_rowgroup_count,
SUM(ideal_rowgroup_count) AS ideal_rowgroup_count,
SUM(open_rowgroup_count) AS uncompressed_rowgroup_count,
CAST(SUM(size_in_mb) AS DECIMAL(19, 4)) AS actual_size_in_mb,
CAST(SUM(open_size_in_mb) AS DECIMAL(19, 4)) AS uncompressed_size_in_mb,
CAST(((SUM(total_rowgroup_count) - SUM(ideal_rowgroup_count)) / SUM(total_rowgroup_count)) * 100. AS DECIMAL(9, 4)) AS excess_pct,
CAST(((SUM(total_rowgroup_count) - SUM(ideal_rowgroup_count)) / SUM(total_rowgroup_count)) * 1. AS DECIMAL(9, 4)) * SUM(size_in_mb) AS excess_size_in_mb
FROM cci_detail
Na podstawie wyniku możesz uzyskać przegląd kondycji klastrowanego indeksu magazynu kolumn dla dedykowanej puli SQL. Te informacje nie są bezpośrednio możliwe do działania, ale pomagają zrozumieć znaczenie procedur konserwacji w celu osiągnięcia idealnego stanu.
| Nazwa kolumny | Opis |
|---|---|
tables_assessed_count |
Liczba tabel klastrowanego indeksu magazynu kolumn |
partitions_assessed_count |
Liczba partycji Uwaga: Tabele bez partycjonowania będą liczone jako 1. |
actual_rowgroup_count |
Fizyczna liczba grup wierszy |
ideal_rowgroup_count |
Obliczona liczba grup wierszy, która byłaby idealna pod względem liczby wierszy |
uncompressed_rowgroup_count |
Liczba grup wierszy zawierających nieskompresowane dane. (Określana też jako: wiersze OPEN) |
actual_size_in_mb |
Fizyczny rozmiar danych klastrowanego indeksu magazynu kolumn w MB |
uncompressed_size_in_mb |
Fizyczny rozmiar nieskompresowanych danych w MB |
excess_pct |
Procent grup wierszy, które można dodatkowo zoptymalizować |
excess_size_in_mb |
Szacowana liczba MB z niezoptymalizowanych grup wierszy |
Krok 2. Analizowanie szczegółowych informacji o klastrowanym indeksie magazynu kolumn
Poniższe zapytanie udostępnia szczegółowy raport partycji tabel, które są kandydatami do ponownego skompilowania. Szczegóły klastrowanego indeksu magazynu kolumn są udostępniane w trzech metrykach ułatwiających identyfikowanie i określanie priorytetów tabel/partycji, które najbardziej skorzystałyby na konserwacji. Ustaw odpowiednie wartości progowe dla tych metryk w klauzuli WHERE, a następnie w klauzuli ORDER BY użyj metryk, które są dla Ciebie najbardziej interesujące. Szczegółowe informacje mogą być też przydatne do ustalenia, czy na dedykowaną pulę SQL ma negatywny wpływ duża liczba małych, pofragmentowanych tabel, co może prowadzić do opóźnień kompilacji.
Uwaga 16.
Funkcja skomentowana fnMs_GenerateIndexMaintenanceScript jest funkcją o wartości tabeli (TVF), która może generować typowe skrypty do obsługi indeksów. Jeśli chcesz uzyskać skrypty konserwacji w wyniku, usuń komentarze wiersze 37 i 39. Przed uruchomieniem zapytania użyj skryptu w sekcji Generowanie skryptów konserwacji indeksu , aby utworzyć funkcję. Podczas uruchamiania skryptu konserwacji uzyskanym z wyniku pamiętaj, aby użyć odpowiednio dużej klasy zasobów, takiej jak większe lub xlargerc.
| Nazwa kolumny | Charakterystyka jakości | Opis |
|---|---|---|
excess_pct |
Zwartość | Procent grup wierszy, które można dodatkowo skompaktować |
excess_size_in_mb |
Zwartość | Szacowana liczba MB z niezoptymalizowanych grup wierszy |
OPEN_rowgroup_size_in_mb |
Kompresja | Rzeczywista liczba MB nieskompresowanych danych w indeksie |
WITH cci_info AS(
SELECT t.object_id AS [object_id],
MAX(schema_name(t.schema_id)) AS [schema_name],
MAX(t.name) AS [table_name],
rg.partition_number AS [partition_number],
COUNT(DISTINCT rg.distribution_id) AS [distribution_count],
SUM(rg.size_in_bytes/1024/1024) AS [size_in_mb],
SUM(rg.[total_rows]) AS [row_count_total],
COUNT(*) AS [total_rowgroup_count],
CEILING((SUM(rg.[total_rows]) - SUM(rg.[deleted_rows]))/COUNT(DISTINCT rg.distribution_id)/1048576.) * COUNT(DISTINCT rg.distribution_id) AS [ideal_rowgroup_count],
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],
CAST(SUM(CASE WHEN rg.[State] = 1 THEN rg.[size_in_bytes]/1024./1024. ELSE 0 END) AS DECIMAL(19, 4)) AS [OPEN_rowgroup_size_in_mb],
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],
CAST(SUM(CASE WHEN rg.[State] = 2 THEN rg.[size_in_bytes]/1024./1024. ELSE 0 END) AS DECIMAL(19, 4)) AS [CLOSED_size_in_mb],
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],
CAST(SUM(CASE WHEN rg.[State] = 3 THEN rg.[size_in_bytes]/1024./1024. ELSE 0 END) AS DECIMAL(19, 4)) AS [COMPRESSED_size_in_mb],
SUM(CASE WHEN rg.[State] = 3 THEN rg.[deleted_rows] ELSE 0 END) AS [COMPRESSED_rowgroup_rows_DELETED]
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]
GROUP BY t.object_id,
rg.partition_number
)
, calc_excess AS(
SELECT *,
CAST(((total_rowgroup_count - ideal_rowgroup_count) / total_rowgroup_count) * 100. AS DECIMAL(9, 4)) AS [excess_pct],
CAST(((total_rowgroup_count - ideal_rowgroup_count) / total_rowgroup_count) * 1. AS DECIMAL(9, 4)) * size_in_mb AS [excess_size_in_mb]
FROM cci_info
)
SELECT calc_excess.*
-- , script.*
FROM calc_excess
-- CROSS APPLY dbo.fnMs_GenerateIndexMaintenanceScript(object_id, partition_number) AS script
WHERE -- set your own threshold(s) for the following; 0 is the ideal, but usually not practical
calc_excess.[excess_size_in_mb] > 300
OR calc_excess.excess_pct > 0.1
OR calc_excess.OPEN_rowgroup_size_in_mb > 100
ORDER BY calc_excess.[excess_size_in_mb] DESC;
Krok 3. Co zrobić, gdy konserwacja nie poprawi kondycji klastrowanego indeksu magazynu kolumn
Wykonanie konserwacji tabeli/partycji może spowodować powstanie jednego z następujących scenariuszy:
- Wartość
excess_pctlubexcess_size_in_mbjest większa niż przed konserwacją. - Instrukcja konserwacji kończy się niepowodzeniem z powodu niewystarczającej ilości pamięci.
Typowe przyczyny
- Niewystarczające zasoby.
- Niewystarczający poziom usług (DWU).
- Tabela jest duża i nie jest podzielona na partycje.
Zalecane środki zaradcze
- Zwiększ zasoby dla instrukcji konserwacji, zmieniając klasę zasobów lub grupę obciążeń użytkownika uruchamiającego operację.
- Tymczasowo zwiększ poziom jednostek DWU w celu przeprowadzenia konserwacji.
- Zaimplementuj strategię partycjonowania dla problematycznej tabeli, a następnie przeprowadź konserwację na partycjach.
Krok 4. Sprawdzanie możliwości ulepszenia projektu
Chociaż nie jest to kompleksowe rozwiązanie, poniższe zapytanie może pomóc w zidentyfikowaniu potencjalnych typowych przyczyn problemów z wydajnością lub konserwacją klastrowanego indeksu magazynu kolumn.
| Możliwość | Opis | Zalecenia |
|---|---|---|
| Mała tabela | Tabela zawiera mniej niż 15 mln wierszy | Rozważ zmianę indeksu z CCI na:
|
| Partycjonowanie szansy sprzedaży lub tabeli podzielonej na partycje | Obliczona idealna liczba wierszy jest większa niż 180 mln (lub ok. 188 mln wierszy) | Zaimplementuj strategię partycjonowania lub zmień istniejącą strategię partycjonowania, aby zmniejszyć liczbę wierszy na partycję do mniej niż 188 mln (około trzy grupy wierszy na partycję na dystrybucję) |
| Tabela zbytnio podzielona na partycje | Tabela zawiera mniej niż 15 mln wierszy dla największej partycji | Rozważać:
|
WITH cci_info AS (
SELECT t.object_id AS [object_id],
MAX(SCHEMA_NAME(t.schema_id)) AS [schema_name],
MAX(t.name) AS [table_name],
rg.partition_number AS [partition_number],
SUM(rg.[total_rows]) AS [row_count_total],
CEILING((SUM(rg.[total_rows]) - SUM(rg.[deleted_rows]))/COUNT(DISTINCT rg.distribution_id)/1048576.) * COUNT(DISTINCT rg.distribution_id) AS [ideal_rowgroup_count]
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]
GROUP BY t.object_id,
rg.partition_number
)
SELECT object_id,
MAX(SCHEMA_NAME),
MAX(TABLE_NAME),
COUNT(*) AS number_of_partitions,
MAX(row_count_total) AS max_partition_row_count,
MAX(ideal_rowgroup_count) partition_ideal_row_count,
CASE
-- non-partitioned tables
WHEN COUNT(*) = 1 AND MAX(row_count_total) < 15000000 THEN 'Small table'
WHEN COUNT(*) = 1 AND MAX(ideal_rowgroup_count) > 180 THEN 'Partitioning opportunity'
-- partitioned tables
WHEN COUNT(*) > 1 AND MAX(row_count_total) < 15000000 THEN 'Over-partitioned table'
WHEN COUNT(*) > 1 AND MAX(ideal_rowgroup_count) > 180 THEN 'Under-partitioned table'
END AS warning_category
FROM cci_info
GROUP BY object_id
Generowanie skryptów konserwacji indeksu
Uruchom następujące zapytanie, aby utworzyć dbo.fnMs_GenerateIndexMaintenanceScript funkcję w dedykowanej puli SQL. Ta funkcja generuje skrypty, aby zoptymalizować CCI na trzy sposoby. Za pomocą tej funkcji można obsługiwać nie tylko interfejsy CCI, ale także indeksy klastrowane (rowstore).
Parametry
| Nazwa parametru | Wymagania | opis |
|---|---|---|
@object_id |
Y | object_id tabeli docelowej |
@partition_number |
Y | partition_number od sys.partitions do elementu docelowego. Jeśli tabela nie jest partycjonowana, określ wartość 1. |
Tabela danych wyjściowych
| Nazwa kolumny | opis |
|---|---|
rebuild_script |
Wygenerowana ALTER INDEX ALL ... REBUILD instrukcja dla danej tabeli/partycji. Niedzielone sterty będą zwracać wartość NULL. |
reorganize_script |
Wygenerowana ALTER INDEX ALL ... REORGANIZE instrukcja dla danej tabeli/partycji. Niedzielone sterty będą zwracać wartość NULL. |
partition_switch_script |
Dotyczy tylko tabel partycjonowanych; będzie mieć NULL wartość , jeśli tabela nie jest partycjonowana lub jeśli określono nieprawidłowy numer partycji. Jeśli CCI został utworzony za pomocą klauzuli ORDER , zostanie on renderowany. |
CREATE FUNCTION dbo.fnMs_GenerateIndexMaintenanceScript (@object_id INT, @partition_number INT = 1)
RETURNS TABLE
AS
RETURN(
WITH base_info AS (
SELECT
t.object_id
, SCHEMA_NAME(t.schema_id) AS [schema_name]
, t.name AS table_name
, i.index_type
, i.index_cols
, i.index_type_desc
, tdp.distribution_policy_desc
, c.name hash_distribution_column_name
FROM sys.tables t
JOIN (
SELECT
i.object_id
, i.index_id
, MAX(i.type) AS index_type
, MAX(CASE WHEN i.type = 5 AND ic.column_store_order_ordinal != 0 THEN ' ORDER ' ELSE '' END)
+ '(' + STRING_AGG(
CASE
WHEN i.type IN (1, 5)
AND (ic.key_ordinal != 0 OR ic.column_store_order_ordinal != 0)
THEN c.name + CASE WHEN ic.is_descending_key = 1 THEN ' DESC' ELSE '' END
END
, ',') WITHIN GROUP(ORDER BY ic.column_store_order_ordinal, ic.key_ordinal) + ')' AS index_cols
, MAX(i.type_desc)
+ CASE
WHEN MAX(i.type) IN (1, 5) THEN ' INDEX'
ELSE ''
END COLLATE SQL_Latin1_General_CP1_CI_AS AS index_type_desc
FROM sys.indexes i
JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
WHERE i.index_id <= 1
GROUP BY i.object_id, i.index_id
) AS i
ON t.object_id = i.object_id
JOIN sys.pdw_table_distribution_properties tdp ON t.object_id = tdp.object_id
LEFT JOIN sys.pdw_column_distribution_properties cdp ON t.object_id = cdp.object_id AND cdp.distribution_ordinal = 1
LEFT JOIN sys.columns c ON cdp.object_id = c.object_id AND cdp.column_id = c.column_id
WHERE t.object_id = @object_id
)
, param_data_type AS (
SELECT
pp.function_id
, typ.name AS data_type_name
, CAST(CASE
WHEN typ.collation_name IS NOT NULL THEN 1
WHEN typ.name LIKE '%date%' THEN 1
WHEN typ.name = 'uniqueidentifier' THEN 1
ELSE 0
END AS BIT) AS use_quotes_on_values_flag
FROM sys.partition_parameters pp
JOIN sys.types typ ON pp.user_type_id = typ.user_type_id
)
, boundary AS (
SELECT
t.object_id
, c.name AS partition_column_name
, pf.boundary_value_on_right
, prv.boundary_id
, prv.boundary_id + CASE WHEN pf.boundary_value_on_right = 1 THEN 1 ELSE 0 END AS [partition_number]
, CASE
WHEN pdt.use_quotes_on_values_flag = 1 THEN '''' + CAST(
CASE pdt.data_type_name
WHEN 'date' THEN CONVERT(char(10), prv.value, 120)
WHEN 'smalldatetime' THEN CONVERT(VARCHAR, prv.value, 120)
WHEN 'datetime' THEN CONVERT(VARCHAR, prv.value, 121)
WHEN 'datetime2' THEN CONVERT(VARCHAR, prv.value, 121)
ELSE prv.value
END
AS VARCHAR(32)) + ''''
ELSE CAST(prv.value AS VARCHAR(32))
END AS boundary_value
FROM sys.tables t
JOIN sys.indexes i ON t.object_id = i.object_id AND i.index_id <= 1
JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id AND ic.partition_ordinal = 1
JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
JOIN sys.partition_schemes ps ON i.data_space_id = ps.data_space_id
JOIN sys.partition_functions pf ON ps.function_id = pf.function_id
JOIN param_data_type pdt ON pf.function_id = pdt.function_id
JOIN sys.partition_range_values prv ON pf.function_id = prv.function_id
WHERE t.object_id = @object_id
)
, partition_clause AS (
SELECT
object_id
, COUNT(*) - 1 -- should always be the 2nd to last partition in stage table
+ CASE WHEN MAX([partition_number]) = @partition_number THEN 1 ELSE 0 END -- except when last partition
AS [source_partition_number]
, 'WHERE ' + MAX(partition_column_name)
+ CASE WHEN MAX(CAST(boundary_value_on_right AS TINYINT)) = 1 THEN
' >= ' + MIN(CASE WHEN [partition_number] = @partition_number THEN boundary_value END)
ELSE
' <= ' + MAX(CASE WHEN [partition_number] = @partition_number THEN boundary_value END)
END
+ ' AND ' + MAX(partition_column_name)
+ CASE WHEN MAX(CAST(boundary_value_on_right AS TINYINT)) = 1 THEN
' < ' + MAX(boundary_value)
ELSE
' > ' + MIN(boundary_value)
END AS filter_clause
, ', PARTITION (' + MAX(partition_column_name) + ' RANGE '
+ CASE WHEN MAX(CAST(boundary_value_on_right AS TINYINT)) = 1 THEN 'RIGHT' ELSE 'LEFT' END
+ ' FOR VALUES(' + STRING_AGG(boundary_value, ',') + '))' AS [partition_clause]
FROM boundary
WHERE [partition_number] BETWEEN @partition_number - 1 AND @partition_number + 1
GROUP BY object_id
)
SELECT
CASE WHEN index_type IN (1, 5) THEN 'ALTER INDEX ALL ON [' + [schema_name] + '].[' + [table_name] + '] REBUILD'
+ CASE WHEN partition_clause.[object_id] IS NOT NULL THEN ' PARTITION = ' + CAST(@partition_number AS VARCHAR(16)) ELSE '' END + ';' END AS [rebuild_script]
, CASE WHEN index_type IN (1, 5) THEN 'ALTER INDEX ALL ON [' + [schema_name] + '].[' + [table_name] + '] REORGANIZE'
+ CASE WHEN partition_clause.[object_id] IS NOT NULL THEN ' PARTITION = ' + CAST(@partition_number AS VARCHAR(16)) ELSE '' END
+ CASE WHEN index_type = 5 THEN ' WITH (COMPRESS_ALL_ROW_GROUPS = ON)' ELSE '' END + ';' END AS [reorganize_script]
, 'CREATE TABLE [' + schema_name + '].[' + table_name + '_p' + CAST(@partition_number AS VARCHAR(16)) + '_tmp] WITH(' + index_type_desc + ISNULL(index_cols, '')
+ ', DISTRIBUTION = ' + distribution_policy_desc + CASE WHEN distribution_policy_desc = 'HASH' THEN '(' + hash_distribution_column_name + ')' ELSE '' END
+ partition_clause.partition_clause + ') AS SELECT * FROM [' + [schema_name] + '].[' + [table_name] + '] ' + filter_clause + CASE WHEN index_type = 5 AND index_cols IS NOT NULL THEN ' OPTION(MAXDOP 1)' ELSE '' END + ';'
+ ' ALTER TABLE [' + schema_name + '].[' + table_name + '_p' + CAST(@partition_number AS VARCHAR(16)) + '_tmp] SWITCH PARTITION ' + CAST(source_partition_number AS VARCHAR(16))
+ ' TO [' + [schema_name] + '].[' + [table_name] + '] PARTITION ' + CAST(@partition_number AS VARCHAR(16))
+ ' WITH (TRUNCATE_TARGET = ON);'
+ ' DROP TABLE [' + schema_name + '].[' + table_name + '_p' + CAST(@partition_number AS VARCHAR(16)) + '_tmp];' AS [partition_switch_script]
FROM base_info
LEFT JOIN partition_clause
ON base_info.object_id = partition_clause.object_id
);
GO
Więcej informacji
Aby uzyskać bardziej szczegółowe informacje i uzyskać dodatkowe narzędzia do oceny dla CCI w dedykowanej puli SQL, zobacz: