Bewerten und Korrigieren der Integrität von gruppierten Columnstore-Indizes in einem dedizierten SQL-Pool
Gilt für: Azure Synapse Analytics
In diesem Artikel wird ein etwas anderer Ansatz zum Bewerten der Integrität des gruppierten Columnstore-Indexes (CCI) vorgestellt. Führen Sie die Schritte in den folgenden Abschnitten aus, oder führen Sie die Schritte im Notebook über Azure Data Studio aus.
Hinweis
Bevor Sie versuchen, dieses Notebook zu öffnen, stellen Sie sicher, dass Azure Data Studio auf Ihrem lokalen Computer installiert ist. Informationen zur Installation finden Sie unter Installieren von Azure Data Studio.
Im Allgemeinen beeinflussen zwei Hauptfaktoren die Qualität einer CCI:
Komprimieren von Zeilengruppen und Metadaten : Die tatsächliche Zeilengruppenanzahl liegt nahe an der idealen Anzahl für die Anzahl der Zeilen in der Zeilengruppe.
Komprimierte Zeilengruppen : Zeilengruppen verwenden columnstore-Komprimierung.
Andere Bedingungen, z. B. kleine Tabellen, überpartitionierte Tabellen oder unterpartitionierte Tabellen, sind wohl von schlechter Qualität oder Integrität. Diese Bedingungen werden jedoch besser als Entwicklungsmöglichkeiten klassifiziert, die in Schritt 4 bewertet werden können.
Schritt 1: Analysieren einer Zusammenfassung Ihrer CCI-Integrität
Verwenden Sie die folgende Abfrage, um eine einzelne Zeile mit Metriken abzurufen.
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
Aus dem Ergebnis erhalten Sie einen Überblick über die CCI-Integrität für Ihren dedizierten SQL-Pool. Diese Informationen sind nicht direkt umsetzbar, helfen Ihnen jedoch, die Bedeutung von Wartungsroutinen für das Erzielen eines idealen Zustands zu verstehen.
| Spaltenname | Beschreibung |
|---|---|
tables_assessed_count |
Anzahl der CCI-Tabellen |
partitions_assessed_count |
Anzahl der Partitionen Hinweis: Nicht partitionierte Tabellen werden als 1 gezählt. |
actual_rowgroup_count |
Physische Anzahl von Zeilengruppen |
ideal_rowgroup_count |
Berechnete Anzahl von Zeilengruppen, die für die Anzahl der Zeilen ideal wären |
uncompressed_rowgroup_count |
Anzahl der Zeilengruppen, die nicht komprimierte Daten enthalten. (Auch bekannt als: OPEN rows) |
actual_size_in_mb |
Physische Größe von CCI-Daten in MB |
uncompressed_size_in_mb |
Physische Größe nicht komprimierter Daten in MB |
excess_pct |
Prozent der Zeilengruppen, die weiter optimiert werden könnten |
excess_size_in_mb |
Geschätzte MB aus nicht optimierten Zeilengruppen |
Schritt 2: Analysieren detaillierter CCI-Informationen
Die folgende Abfrage enthält einen detaillierten Bericht darüber, welche Tabellenpartitionen für die Neuerstellung in Frage kommen. CCI-Details werden in drei Metriken bereitgestellt, die dabei helfen, Tabellen/Partitionen zu identifizieren und zu priorisieren, die am meisten von der Wartung profitieren würden. Legen Sie die entsprechenden Schwellenwerte für diese Metriken in der WHERE -Klausel fest, und verwenden Sie dann in der ORDER BY -Klausel die Metriken, die für Sie am wichtigsten sind. Die detaillierten Informationen können auch hilfreich sein, um zu bestimmen, ob Ihr dedizierter SQL-Pool von einer großen Anzahl kleiner, fragmentierter Tabellen betroffen ist, was zu Verzögerungen bei der Kompilierung führen kann.
Hinweis
Die kommentierte fnMs_GenerateIndexMaintenanceScript Funktion ist eine Tabellenwertfunktion (Table-Valued Function, TVF), die allgemeine Skripts zum Verwalten von Indizes generieren kann. Wenn Sie die Wartungsskripts im Ergebnis abrufen möchten, heben Sie die Auskommentierung der Zeilen 37 und 39 auf. Bevor Sie die Abfrage ausführen, verwenden Sie das Skript im Abschnitt Generieren von Indexwartungsskripts , um die Funktion zu erstellen. Achten Sie beim Ausführen des Wartungsskripts, das Sie aus dem Ergebnis erhalten, darauf, eine Ressourcenklasse mit angemessener Größe zu verwenden, z. B. "largerc" oder "xlargerc".
| Spaltenname | Qualitätsmerkmal | Beschreibung |
|---|---|---|
excess_pct |
Kompaktheit | Prozentsatz der Zeilengruppen, die weiter komprimiert werden könnten |
excess_size_in_mb |
Kompaktheit | Geschätzte MB aus nicht optimierten Zeilengruppen |
OPEN_rowgroup_size_in_mb |
Komprimierung | Tatsächliche MB an unkomprimierten Daten im Index |
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;
Schritt 3: Was ist zu tun, wenn die Wartung die CCI-Integrität nicht verbessert
Die Durchführung einer Wartung für eine Tabelle/Partition kann zu einem der folgenden Szenarien führen:
excess_pctoderexcess_size_in_mbist größer als vor der Wartung.- Die Wartungssanweisung schlägt fehl, da nicht genügend Arbeitsspeicher vorhanden ist.
Typische Ursachen
- Unzureichende Ressourcen.
- Unzureichender Servicelevel (DWU).
- Die Tabelle ist groß und nicht partitioniert.
Empfohlene Entschärfungen
- Erhöhen Sie die Ressourcen für die Wartungsanweisungen, indem Sie die Ressourcenklasse oder Arbeitsauslastungsgruppe des ausführenden Benutzers ändern.
- Erhöhen Sie vorübergehend die DWU-Ebene, um die Wartung durchzuführen.
- Implementieren Sie eine Partitionierungsstrategie für die problematische Tabelle, und führen Sie dann Wartungen für die Partitionen durch.
Schritt 4: Überprüfen auf Möglichkeiten zur Entwurfsverbesserung
Obwohl die folgende Abfrage nicht umfassend ist, können Sie potenzielle Möglichkeiten identifizieren, die häufig zu Leistungs- oder Wartungsproblemen in Bezug auf CCIs führen.
| Titel der Verkaufschance | Beschreibung | Empfehlungen |
|---|---|---|
| Kleine Tabelle | Tabelle enthält weniger als 15 Millionen Zeilen | Erwägen Sie, den Index von CCI in zu ändern:
|
| Partitionierungschance oder unterpartitionierte Tabelle | Die berechnete anzahl der idealen Zeilengruppen ist größer als 180M (oder ~188M Zeilen) | Implementieren Sie eine Partitionierungsstrategie, oder ändern Sie die vorhandene Partitionierungsstrategie, um die Anzahl der Zeilen pro Partition auf weniger als 188 Millionen zu reduzieren (ungefähr drei Zeilengruppen pro Partition und Verteilung). |
| Überpartitionierte Tabelle | Tabelle enthält weniger als 15 Millionen Zeilen für die größte Partition | Erwägen Sie Folgendes:
|
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
Generieren von Indexwartungsskripts
Führen Sie die folgende Abfrage aus, um eine Funktion in Ihrem dedizierten SQL-Pool zu erstellen dbo.fnMs_GenerateIndexMaintenanceScript . Diese Funktion generiert Skripts, um Ihre CCI auf drei Arten zu optimieren. Sie können diese Funktion verwenden, um nicht nur CCIs, sondern auch gruppierte Indizes (Rowstore) zu verwalten.
Parameter
| Parametername | Erforderlich | Beschreibung |
|---|---|---|
@object_id |
J | object_id der Tabelle, die als Ziel verwendet werden soll |
@partition_number |
J | partition_number von sys.partitions zum Ziel. Wenn die Tabelle nicht partitioniert ist, geben Sie 1 an. |
Ausgabetabelle
| Spaltenname | Beschreibung |
|---|---|
rebuild_script |
Generierte ALTER INDEX ALL ... REBUILD Anweisung für die angegebene Tabelle/Partition. Nicht partitionierte Heaps geben zurück NULL. |
reorganize_script |
Generierte ALTER INDEX ALL ... REORGANIZE Anweisung für die angegebene Tabelle/Partition. Nicht partitionierte Heaps geben zurück NULL. |
partition_switch_script |
Gilt nur für partitionierte Tabellen. ist NULL , wenn die Tabelle nicht partitioniert ist oder wenn eine ungültige Partitionsnummer angegeben wird. Wenn die CCI mit einer ORDER -Klausel erstellt wurde, wird sie gerendert. |
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
Weitere Informationen
Weitere Informationen und zusätzliche Bewertungstools für CCI im dedizierten SQL-Pool finden Sie unter:
Feedback
Bald verfügbar: Im Laufe des Jahres 2024 werden wir GitHub-Issues stufenweise als Feedbackmechanismus für Inhalte abbauen und durch ein neues Feedbacksystem ersetzen. Weitere Informationen finden Sie unter https://aka.ms/ContentUserFeedback.
Feedback senden und anzeigen für