Évaluer et corriger l’intégrité des 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.
Remarque
Avant de tenter d’ouvrir ce notebook, assurez-vous 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 :
Rowgroups compacts et métadonnées : le nombre réel de rowgroups 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 qualité ou d’intégrité médiocres. 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 un résumé de l’intégrité de votre CCI
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é cci 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 CCI |
partitions_assessed_count |
Nombre de partitions Note: Les tables non partitionnée sont comptées comme 1. |
actual_rowgroup_count |
Nombre physique de rowgroups |
ideal_rowgroup_count |
Nombre calculé de rowgroups qui seraient idéaux pour le nombre de lignes |
uncompressed_rowgroup_count |
Nombre de rowgroups qui contiennent des données non compressées. (Également appelé : OUVRIR des lignes) |
actual_size_in_mb |
Taille physique des données CCI en Mo |
uncompressed_size_in_mb |
Taille physique des données non compressées en Mo |
excess_pct |
Pourcentage de rowgroups pouvant être optimisés |
excess_size_in_mb |
Mo estimés à partir de rowgroups non optimisés |
Étape 2 : Analyser les informations détaillées de l’icc
La requête suivante fournit un rapport détaillé sur les partitions de table qui sont candidates à la reconstruction. Les détails de l’ICC sont fournis dans trois métriques qui permettent d’identifier et de hiérarchiser les tables/partitions qui bénéficieraient le plus de la maintenance. Définissez les valeurs de seuil appropriées pour ces métriques dans la WHERE clause, puis dans la ORDER BY clause, 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 affecté par un grand nombre de petites tables fragmentées, ce qui peut entraîner des retards dans la compilation.
Remarque
La fonction commentée fnMs_GenerateIndexMaintenanceScript est une fonction table (TVF) qui peut générer des scripts courants pour la gestion des index. Si vous souhaitez obtenir les scripts de maintenance dans le résultat, supprimez les marques de commentaire 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 obtenu à partir du résultat, veillez à utiliser une classe de ressources de taille appropriée, telle que largerc ou xlargerc.
| Nom de colonne | Caractéristique de qualité | Description |
|---|---|---|
excess_pct |
Compacité | Pourcentage de rowgroups pouvant être compactés davantage |
excess_size_in_mb |
Compacité | Mo estimés à partir de rowgroups non optimisés |
OPEN_rowgroup_size_in_mb |
Compression | Mo réel de données non compressées dans l’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;
Étape 3 : Que faire quand la maintenance n’améliore pas l’intégrité de l’ICC
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 mémoire insuffisante.
Causes typiques
- Ressources insuffisantes.
- Niveau de service insuffisant (DWU).
- La table est volumineuse et n’est pas 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 en cours d’exécution.
- Augmentez temporairement le niveau 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 : Rechercher les opportunités d’amélioration de la conception
Bien qu’elle ne soit pas complète, la requête suivante peut vous aider à identifier les opportunités potentielles susceptibles de provoquer des problèmes de performances ou de maintenance concernant les cci.
| Titre de l’opportunité | Description | Recommandations |
|---|---|---|
| Petite table | La table contient moins de 15 millions de lignes | Envisagez de remplacer l’index cci par :
|
| Opportunité de partitionnement ou table sous-partitionnée | Le nombre de rowgroups idéaux calculé est supérieur à 180M (ou ~188M de 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 millions (environ trois groupes de lignes par partition et par distribution) |
| Table sur partitionnée | La table contient moins de 15 millions de lignes pour la plus grande partition | Considérez :
|
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 une dbo.fnMs_GenerateIndexMaintenanceScript fonction sur votre pool SQL dédié. Cette fonction génère des scripts pour optimiser votre cci de trois manières. Vous pouvez utiliser cette fonction pour gérer non seulement les cci, mais également les index cluster (rowstore).
Paramètres
| Nom du paramètre | Obligatoire | Description |
|---|---|---|
@object_id |
O |
object_id de la table à cibler |
@partition_number |
O |
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/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/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 est 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
Informations supplémentaires
Pour obtenir une compréhension plus approfondie et acquérir des outils d’évaluation supplémentaires pour l’icc sur le pool SQL dédié, consultez :