De status van de geclusterde columnstore-index in een toegewezen SQL-pool beoordelen en corrigeren
Van toepassing op: Azure Synapse Analytics
In dit artikel wordt een enigszins andere benadering geïntroduceerd voor het beoordelen van de status van de geclusterde columnstore-index (CCI). Volg de stappen in de volgende secties of voer de stappen in het notebook uit via Azure Data Studio.
Notitie
Voordat u dit notebook probeert te openen, moet u ervoor zorgen dat Azure Data Studio is geïnstalleerd op uw lokale computer. Als u deze wilt installeren, gaat u naar Meer informatie over het installeren van Azure Data Studio.
Over het algemeen zijn twee belangrijke factoren van invloed op de kwaliteit van een CCI:
Compacte rijgroepen en metagegevens : het werkelijke aantal rijengroepen ligt dicht bij het ideale aantal rijen in de rijgroep.
Gecomprimeerde rijgroepen : rowgroups maken gebruik van columnstore-compressie.
Andere voorwaarden, zoals kleine tabellen, overgepartitioneerde tabellen of ondergepartitioneerde tabellen, zijn waarschijnlijk van slechte kwaliteit of status. Deze voorwaarden worden echter beter geclassificeerd als ontwerpverbeteringsmogelijkheden die kunnen worden beoordeeld in stap 4.
Stap 1: analyseer een samenvatting van uw CCI-status
Gebruik de volgende query om één rij met metrische gegevens op te halen.
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
In het resultaat krijgt u een overzicht van de CCI-status voor uw toegewezen SQL-pool. Deze informatie kan niet rechtstreeks worden uitgevoerd, maar helpt u inzicht te hebben in het belang van onderhoudsroutines voor het bereiken van een ideale status.
| Kolomnaam | Beschrijving |
|---|---|
tables_assessed_count |
Aantal CCI-tabellen |
partitions_assessed_count |
Aantal partities Opmerking: niet-gepartitioneerde tabellen worden geteld als 1. |
actual_rowgroup_count |
Fysiek aantal rijengroepen |
ideal_rowgroup_count |
Berekend aantal rijengroepen dat ideaal is voor het aantal rijen |
uncompressed_rowgroup_count |
Aantal rijgroepen dat ongecomprimeerde gegevens bevat. (Ook bekend als: OPEN rijen) |
actual_size_in_mb |
Fysieke grootte van CCI-gegevens in MB |
uncompressed_size_in_mb |
Fysieke grootte van ongecomprimeerde gegevens in MB |
excess_pct |
Percentage rijengroepen dat verder kan worden geoptimaliseerd |
excess_size_in_mb |
Geschatte MB van niet-geoptimaliseerde rijengroepen |
Stap 2: analyseer gedetailleerde CCI-informatie
De volgende query biedt een gedetailleerd rapport van welke tabelpartities in aanmerking komen voor herbouwen. CCI-details worden verstrekt in drie metrische gegevens die helpen bij het identificeren en prioriteren van tabellen/partities die het meeste baat zouden hebben bij onderhoud. Stel de juiste drempelwaarden in voor deze metrische gegevens in de component WHERE en gebruik vervolgens in de component ORDER BY de metrische gegevens die voor u het meest van belang zijn. De gedetailleerde informatie kan ook nuttig zijn om te bepalen of uw toegewezen SQL-pool wordt beïnvloed door een groot aantal kleine, gefragmenteerde tabellen, wat kan leiden tot vertragingen in de compilatie.
Notitie
De opmerkingsfunctie fnMs_GenerateIndexMaintenanceScript is een functie met tabelwaarde (TVF) die algemene scripts kan genereren voor het onderhouden van indexen. Als u de onderhoudsscripts in het resultaat wilt ophalen, moet u opmerkingen bij regel 37 en 39 ongedaan maken. En voordat u de query uitvoert, gebruikt u het script in de sectie Indexonderhoudsscripts genereren om de functie te maken. Wanneer u het onderhoudsscript uitvoert dat u op basis van het resultaat krijgt, moet u een resourceklasse met de juiste grootte gebruiken, zoals largerc of xlargerc.
| Kolomnaam | Kwaliteitskenmerk | Description |
|---|---|---|
excess_pct |
Compactheid | Percentage rijengroepen dat verder kan worden gecomprimeerd |
excess_size_in_mb |
Compactheid | Geschatte MB van niet-geoptimaliseerde rijengroepen |
OPEN_rowgroup_size_in_mb |
Compressie | Werkelijke MB aan niet-gecomprimeerde gegevens in de 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;
Stap 3: wat te doen wanneer onderhoud de CCI-status niet beter maakt
Het uitvoeren van onderhoud op een tabel/partitie kan leiden tot een van de volgende scenario's:
excess_pctofexcess_size_in_mbgroter is dan voor het onderhoud.- De onderhoudsinstructie mislukt met onvoldoende geheugen.
Typische oorzaken
- Onvoldoende resources.
- Onvoldoende serviceniveau (DWU).
- De tabel is groot en niet gepartitioneerd.
Aanbevolen oplossingen
- Vergroot de resources voor de onderhoudsinstructies door de resourceklasse of workloadgroep van de uitvoerende gebruiker te wijzigen.
- Verhoog tijdelijk het DWU-niveau om het onderhoud uit te voeren.
- Implementeer een partitioneringsstrategie voor de problematische tabel en voer vervolgens onderhoud uit op de partities.
Stap 4: controleer op mogelijkheden voor verbetering van het ontwerp
Hoewel niet allesomvattend, kan de volgende query u helpen bij het identificeren van potentiële kansen die vaak prestatie- of onderhoudsproblemen met betrekking tot CCI's veroorzaken.
| Kanstitel | Beschrijving | Aanbevelingen |
|---|---|---|
| Kleine tabel | Tabel bevat minder dan 15 miljoen rijen | Overweeg om de index te wijzigen van CCI in:
|
| Partitioneringskans of ondergepartitioneerde tabel | Berekend ideaal aantal rijengroepen is groter dan 180M (of ~188M-rijen) | Implementeer een partitioneringsstrategie of wijzig de bestaande partitioneringsstrategie om het aantal rijen per partitie te verminderen in minder dan 188M (ongeveer drie rijgroepen per partitie per distributie) |
| Overgepartitioneerde tabel | Tabel bevat minder dan 15 miljoen rijen voor de grootste partitie | Overwegen:
|
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
Indexonderhoudsscripts genereren
Voer de volgende query uit om een functie te maken dbo.fnMs_GenerateIndexMaintenanceScript in uw toegewezen SQL-pool. Met deze functie worden scripts gegenereerd om uw CCI op drie manieren te optimaliseren. U kunt deze functie gebruiken om niet alleen CCI's, maar ook geclusterde (rowstore)-indexen te onderhouden.
Parameters
| Parameternaam | Vereist | Beschrijving |
|---|---|---|
@object_id |
J | object_id van de tabel die moet worden gericht |
@partition_number |
J | partition_number van sys.partitions naar doel. Als de tabel niet is gepartitioneerd, geeft u 1 op. |
Uitvoertabel
| Kolomnaam | Beschrijving |
|---|---|
rebuild_script |
Gegenereerde ALTER INDEX ALL ... REBUILD instructie voor de opgegeven tabel/partitie. Niet-gepartitioneerde heaps retourneren NULL. |
reorganize_script |
Gegenereerde ALTER INDEX ALL ... REORGANIZE instructie voor de opgegeven tabel/partitie. Niet-gepartitioneerde heaps retourneren NULL. |
partition_switch_script |
Alleen van toepassing op gepartitioneerde tabellen; NULL is als de tabel niet is gepartitioneerd of als er een ongeldig partitienummer is opgegeven. Als de CCI is gemaakt met een ORDER component, wordt deze weergegeven. |
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
Meer informatie
Zie voor een uitgebreider begrip en het verkrijgen van extra evaluatiehulpprogramma's voor CCI in de toegewezen SQL-pool: