Vérifier l’exactitude des statistiques sur un pool SQL dédié
S’applique à : Azure Synapse Analytics
Les statistiques mises à jour sont essentielles à la génération d’un plan d’exécution optimal. Il existe deux perspectives différentes que vous devez évaluer en ce qui concerne la détermination de l’exactitude des statistiques :
Étape 1 : Vérifier la précision du nombre de lignes du nœud de contrôle
Dans le pool SQL dédié, le moteur principal pour la création de plans de requête distribués doit être mis à jour sur le nombre de lignes sur les nœuds de calcul. Exécutez la requête suivante pour identifier les tables qui présentent des disparités entre le nombre de lignes :
SELECT objIdsWithStats.[object_id]
,actualRowCounts.[schema]
,actualRowCounts.logical_table_name
,statsRowCounts.stats_row_count
,actualRowCounts.actual_row_count
,row_count_difference = CASE
WHEN actualRowCounts.actual_row_count >= statsRowCounts.stats_row_count
THEN actualRowCounts.actual_row_count - statsRowCounts.stats_row_count
ELSE statsRowCounts.stats_row_count - actualRowCounts.actual_row_count
END
,percent_deviation_from_actual = CASE
WHEN actualRowCounts.actual_row_count = 0
THEN statsRowCounts.stats_row_count
WHEN statsRowCounts.stats_row_count = 0
THEN actualRowCounts.actual_row_count
WHEN actualRowCounts.actual_row_count >= statsRowCounts.stats_row_count
THEN CONVERT(NUMERIC(18, 0), CONVERT(NUMERIC(18, 2), (actualRowCounts.actual_row_count - statsRowCounts.stats_row_count)) / CONVERT(NUMERIC(18, 2), actualRowCounts.actual_row_count) * 100)
ELSE CONVERT(NUMERIC(18, 0), CONVERT(NUMERIC(18, 2), (statsRowCounts.stats_row_count - actualRowCounts.actual_row_count)) / CONVERT(NUMERIC(18, 2), actualRowCounts.actual_row_count) * 100)
END
,'UPDATE STATISTICS ' + quotename(actualRowCounts.[schema]) + '.' + quotename(actualRowCounts.logical_table_name) + ';' as update_stats_stmt
FROM (
SELECT DISTINCT object_id
FROM sys.stats
WHERE stats_id > 1
) objIdsWithStats
LEFT JOIN (
SELECT object_id
,sum(rows) AS stats_row_count
FROM sys.partitions
GROUP BY object_id
) statsRowCounts ON objIdsWithStats.object_id = statsRowCounts.object_id
LEFT JOIN (
SELECT sm.name [schema]
,tb.name logical_table_name
,tb.object_id object_id
,SUM(rg.row_count) actual_row_count
FROM sys.schemas sm
INNER JOIN sys.tables tb ON sm.schema_id = tb.schema_id
INNER JOIN sys.pdw_table_mappings mp ON tb.object_id = mp.object_id
INNER JOIN sys.pdw_nodes_tables nt ON nt.name = mp.physical_name
INNER JOIN sys.dm_pdw_nodes_db_partition_stats rg ON rg.object_id = nt.object_id
AND rg.pdw_node_id = nt.pdw_node_id
AND rg.distribution_id = nt.distribution_id
INNER JOIN sys.indexes ind on tb.object_id = ind.object_id
WHERE rg.index_id < 2 -- In case this condition removed the number of rows will gets duplicated based on the number of index.
AND ind.type_desc IN ('CLUSTERED COLUMNSTORE', 'HEAP') -- Switch between the CCI (Column store) and HEAP, You should at least keep one value or else the total number of rows will gets duplicated based on the number of indexes.
GROUP BY sm.name
,tb.name
,tb.object_id
) actualRowCounts ON objIdsWithStats.object_id = actualRowCounts.object_id
Étape 2 : Vérifier que les statistiques sont à jour
La mise à jour des données peut affecter considérablement les histogrammes de statistiques utilisés pour générer des plans d’exécution efficaces. Exécutez la requête suivante pour déterminer si la date de la dernière mise à jour de vos statistiques s’aligne sur les modèles de modification de la table :
SELECT ob.[object_id],max(sm.[name]) AS [schema_name]
,max(tb.[name]) AS [table_name]
,st.[stats_id]
,max(st.[name]) AS [stats_name]
,string_agg(co.[name], ',') AS [stats_column_names]
,STATS_DATE(ob.[object_id], st.[stats_id]) AS [stats_last_updated_date]
,'UPDATE STATISTICS ' + quotename(max(sm.[name])) + '.' + quotename(max(tb.[name])) + ';' as [update_stats_stmt]
FROM sys.objects ob
JOIN sys.stats st ON ob.[object_id] = st.[object_id]
JOIN sys.stats_columns sc ON st.[stats_id] = sc.[stats_id]
AND st.[object_id] = sc.[object_id]
JOIN sys.columns co ON sc.[column_id] = co.[column_id]
AND sc.[object_id] = co.[object_id]
JOIN sys.types ty ON co.[user_type_id] = ty.[user_type_id]
JOIN sys.tables tb ON co.[object_id] = tb.[object_id]
JOIN sys.schemas sm ON tb.[schema_id] = sm.[schema_id]
WHERE st.[stats_id] > 1
GROUP BY ob.[object_id], st.[stats_id]
ORDER BY stats_last_updated_date
Étape 3 : Mettre à jour les statistiques sur les tables identifiées
Après avoir identifié les tables candidates dans les étapes précédentes, exécutez la ou les instructions générées dans la update_stats_stmt colonne des requêtes pour mettre à jour les statistiques.
Remarque
Nous vous déconseillons de mettre à jour les statistiques individuelles, même en cas de création par l’utilisateur. En exécutant UPDATE STATISTICS sans spécifier de nom de statistiques, toutes les statistiques associées à la table, ainsi que le nombre de lignes du nœud de contrôle, sont mises à jour. Vous pouvez envisager de remplacer le pourcentage d’analyse par défaut en utilisant WITH FULLSCAN ou WITH SAMPLE <SamplePercent> PERCENT pour obtenir une précision appropriée pour les tables volumineuses. Consultez UPDATE STATISTICS (Transact-SQL) pour obtenir la syntaxe complète.
Par exemple :
UPDATE STATISTICS [dbo].[MyAwesomeTable];
Après avoir mis à jour les statistiques, réexécutez la requête du problème pour déterminer si les mises à jour des statistiques ont amélioré la durée d’exécution.