Évaluer et corriger l’intégrité de l’index columnstore en cluster dans un pool SQL dédié
S’applique à : Azure Synapse Analytics
Cet article présente une approche légèrement différente de l’évaluation de l’intégrité de l’index columnstore cluster (CCI). Suivez les étapes décrites dans les sections suivantes ou exécutez les étapes du notebook via Azure Data Studio.
Note
Avant d’essayer d’ouvrir ce notebook, vérifiez qu’Azure Data Studio est installé sur votre ordinateur local. Pour l’installer, accédez à Découvrir comment installer Azure Data Studio.
En général, deux facteurs majeurs affectent la qualité d’une CCI :
Compacter les rowgroups et les métadonnées : le nombre de rowgroups réels est proche du nombre idéal pour le nombre de lignes dans le rowgroup.
Rowgroups compressés : les rowgroups utilisent la compression columnstore.
D’autres conditions, telles que les petites tables, les tables sur partitionnée ou les tables sous-partitionnée, sont sans doute de mauvaise qualité ou d’intégrité. Toutefois, ces conditions sont mieux classées comme des opportunités d’amélioration de la conception qui peuvent être évaluées à l’étape 4.
Étape 1 : Analyser le récapitulatif de l’intégrité de votre index columnstore cluster
Utilisez la requête suivante pour obtenir une seule ligne de métriques.
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
À partir du résultat, vous pouvez obtenir une vue d’ensemble de l’intégrité de l’index columnstore cluster pour votre pool SQL dédié. Ces informations ne sont pas directement exploitables, mais vous aident à comprendre l’importance des routines de maintenance pour obtenir un état idéal.
| Nom de colonne | Description |
|---|---|
tables_assessed_count |
Nombre de tables d’index columnstore cluster |
partitions_assessed_count |
Nombre de partitions Remarque : Les tables non partitionnées seront comptabilisées comme 1 table. |
actual_rowgroup_count |
Nombre physique de rowgroups |
ideal_rowgroup_count |
Nombre calculé de rowgroups qui serait idéal pour le nombre de lignes |
uncompressed_rowgroup_count |
Nombre de rowgroups qui contiennent des données non compressées. (également appelées : lignes OPEN) |
actual_size_in_mb |
Taille physique des données de l’index columnstore cluster en Mo |
uncompressed_size_in_mb |
Taille physique des données non compressées en Mo |
excess_pct |
Pourcentage de rowgroups qui pourraient être optimisés davantage |
excess_size_in_mb |
Estimation du nombre de Mo à partir des rowgroups non optimisés |
Étape 2 : Analyser les informations détaillées concernant l’index columnstore cluster
La requête suivante fournit un rapport détaillé sur les partitions de table qui sont candidates à la regénération. Les détails relatifs à l’index columnstore cluster sont fournis dans trois métriques qui permettent d’identifier et de prioriser les tables/partitions qui bénéficieraient le plus d’une maintenance. Définissez les valeurs de seuil appropriées pour ces métriques dans la clause WHERE, puis, dans la clause ORDER BY, utilisez les métriques qui vous intéressent le plus. Les informations détaillées peuvent également être utiles pour déterminer si votre pool SQL dédié est impacté par un grand nombre de petites tables fragmentées, ce qui peut entraîner des retards dans la compilation.
Note
La fonction commentée fnMs_GenerateIndexMaintenanceScript est une fonction table (TVF) qui peut générer des scripts courants pour la maintenance des index. Si vous souhaitez obtenir les scripts de maintenance dans le résultat, supprimez les marques de commentaire des lignes 37 et 39. Avant d’exécuter la requête, utilisez le script de la section Générer des scripts de maintenance d’index pour créer la fonction. Lorsque vous exécutez le script de maintenance que vous obtenez à partir du résultat, veillez à utiliser une classe de ressources de taille appropriée, telle que plusc ou xlargerc.
| Nom de la colonne | Caractéristique de qualité | Description |
|---|---|---|
excess_pct |
Compacité | Pourcentage de rowgroups qui pourraient être compactés davantage |
excess_size_in_mb |
Compacité | Estimation du nombre de Mo à partir des rowgroups non optimisés |
OPEN_rowgroup_size_in_mb |
Compression | Quantité réelle de données non compressées dans l’index, en Mo |
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;
Étape 3 : Que faire quand la maintenance n’améliore pas l’intégrité de l’index columnstore cluster
L’exécution d’une maintenance sur une table/partition peut entraîner l’un des scénarios suivants :
excess_pctouexcess_size_in_mbest plus grand qu’avant la maintenance.- L’instruction de maintenance échoue avec une erreur de mémoire insuffisante.
Causes typiques
- Ressources insuffisantes.
- Niveau de service insuffisant (DWU).
- La table est volumineuse et non partitionnée.
Atténuations recommandées
- Augmentez les ressources pour les instructions de maintenance en modifiant la classe de ressources ou le groupe de charge de travail de l’utilisateur actuel.
- Augmentez temporairement le niveau de DWU pour effectuer la maintenance.
- Implémentez une stratégie de partitionnement pour la table problématique, puis effectuez la maintenance sur les partitions.
Étape 4 : Vérifier les opportunités d’amélioration de la conception
Même si elle n’est pas complète, la requête suivante peut vous aider à identifier les opportunités potentielles qui génèrent souvent des problèmes de performances ou de maintenance au niveau de l’index columnstore cluster.
| Titre de l’opportunité | Description | Recommandations |
|---|---|---|
| Petite table | La table contient moins de 15 millions de lignes | Envisagez de modifier l’index de cci en :
|
| Opportunité de partitionnement ou table sous-partitionnée | Le nombre de rowgroups idéaux calculés est supérieur à 180M (ou ~188M lignes) | Implémenter une stratégie de partitionnement ou modifier la stratégie de partitionnement existante pour réduire le nombre de lignes par partition à moins de 188 M (environ trois groupes de lignes par partition par distribution) |
| Table surpartitionnée | La table contient moins de 15 millions de lignes pour la plus grande partition | Réfléchissez à ce qui suit :
|
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
Générer des scripts de maintenance d’index
Exécutez la requête suivante pour créer dbo.fnMs_GenerateIndexMaintenanceScript une fonction sur votre pool SQL dédié. Cette fonction génère des scripts pour optimiser votre cci de trois façons. Vous pouvez utiliser cette fonction pour gérer non seulement les interfaces réseau réseau, mais également les index cluster (rowstore).
Paramètres
| Nom du paramètre | Requise | Description |
|---|---|---|
@object_id |
Y | object_id de la table à cibler |
@partition_number |
Y | partition_number de sys.partitions la cible. Si la table n’est pas partitionnée, spécifiez 1. |
Table de sortie
| Nom de colonne | Description |
|---|---|
rebuild_script |
Instruction générée ALTER INDEX ALL ... REBUILD pour la table/la partition donnée. Les segments de mémoire non partitionnés retournent NULL. |
reorganize_script |
Instruction générée ALTER INDEX ALL ... REORGANIZE pour la table/la partition donnée. Les segments de mémoire non partitionnés retournent NULL. |
partition_switch_script |
S’applique uniquement aux tables partitionnée ; sera NULL si la table n’est pas partitionnée ou si un numéro de partition non valide est spécifié. Si l’interface CCI a été créée avec une ORDER clause, elle sera rendue. |
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
Plus d’informations
Pour obtenir une compréhension plus approfondie et acquérir des outils d’évaluation supplémentaires pour cci sur le pool SQL dédié, consultez :