Statistiques dans l’entrepôt de données Fabric

  • Article

S’applique à : point de terminaison d’analytique SQL et entrepôt dans Microsoft Fabric

L’entrepôt dans Microsoft Fabric utilise un moteur de requête pour créer un plan d’exécution pour une requête SQL donnée. Lorsque vous envoyez une requête, l’optimiseur de requête tente d’énumérer tous les plans possibles et de choisir le candidat le plus efficace. Pour déterminer quel plan nécessiterait le moins de surcharge (E/S, processeur, mémoire), le moteur doit être en mesure d’évaluer la quantité de travail ou de lignes pouvant être traitées à chaque opérateur. Ensuite, en fonction du coût de chaque plan, il choisit celui qui a le moins de travail estimé. Les statistiques sont des objets qui contiennent des informations pertinentes sur vos données, pour permettre à l’optimiseur de requête d’estimer ces coûts.

Comment tirer parti des statistiques

Pour obtenir des performances de requête optimales, il est important d’avoir des statistiques précises. Microsoft Fabric prend actuellement en charge les chemins d’accès suivants pour fournir des statistiques pertinentes et à jour :

Statistiques manuelles pour toutes les tables

L’option traditionnelle de maintien de l’intégrité des statistiques est disponible dans Microsoft Fabric. Les utilisateurs peuvent créer, mettre à jour et supprimer des statistiques basées sur un histogramme unique avec CREATE STATISTICS, UPDATE STATISTICS et DROP STATISTICS, respectivement. Les utilisateurs peuvent également afficher le contenu des statistiques basées sur une seule colonne basées sur un histogramme avec DBCC SHOW_STATISTICS. Actuellement, une version limitée de ces instructions est prise en charge.

  • Si vous créez des statistiques manuellement, envisagez de vous concentrer sur celles qui sont fortement utilisées dans votre charge de travail de requête (en particulier dans GROUP BY, ORDER BY, les filtres et les JOIN).
  • Envisagez de mettre à jour régulièrement les statistiques au niveau des colonnes après des modifications de données qui modifient considérablement le nombre de lignes ou la distribution des données.

Exemples de maintenance manuelle des statistiques

Pour créer des statistiques sur la dbo.DimCustomer table, en fonction de toutes les lignes d’une colonne CustomerKey :

CREATE STATISTICS DimCustomer_CustomerKey_FullScan
ON dbo.DimCustomer (CustomerKey) WITH FULLSCAN;

Pour mettre à jour manuellement l’objet DimCustomer_CustomerKey_FullScande statistiques , peut-être après une mise à jour de données volumineuse :

UPDATE STATISTICS dbo.DimCustomer (DimCustomer_CustomerKey_FullScan) WITH FULLSCAN;

Pour afficher des informations sur l’objet de statistiques :

DBCC SHOW_STATISTICS ("dbo.DimCustomer", "DimCustomer_CustomerKey_FullScan");

Pour afficher uniquement des informations sur l’histogramme de l’objet de statistiques :

DBCC SHOW_STATISTICS ("dbo.DimCustomer", "DimCustomer_CustomerKey_FullScan") WITH HISTOGRAM;

Pour supprimer manuellement l’objet de statistiquesDimCustomer_CustomerKey_FullScan :

DROP STATISTICS dbo.DimCustomer.DimCustomer_CustomerKey_FullScan;

Les objets T-SQL suivants peuvent également être utilisés pour case activée des statistiques créées manuellement et automatiquement dans Microsoft Fabric :

Statistiques automatiques lors de la requête

Chaque fois que vous émettez une requête et que l’optimiseur de requête nécessite des statistiques pour l’exploration du plan, Microsoft Fabric crée automatiquement ces statistiques si elles n’existent pas déjà. Une fois les statistiques créées, l’optimiseur de requête peut les utiliser pour estimer les coûts de plan de la requête de déclenchement. En outre, si le moteur de requête détermine que les statistiques existantes pertinentes pour la requête ne reflètent plus précisément les données, ces statistiques sont automatiquement actualisées. Étant donné que ces opérations automatiques sont effectuées de manière synchrone, vous pouvez vous attendre à ce que la durée de la requête inclue cette durée si les statistiques nécessaires n’existent pas encore ou si des modifications de données significatives se sont produites depuis la dernière actualisation des statistiques.

Pour vérifier les statistiques automatiques au moment de la requête

Il existe différents cas où vous pouvez vous attendre à un certain type de statistiques automatiques. Les statistiques les plus courantes sont des statistiques basées sur l’histogramme, qui sont demandées par l’optimiseur de requête pour les colonnes référencées dans GROUP BYs, JOINs, clauses DISTINCT, filtres (clauses WHERE) et BYs ORDER. Par exemple, si vous souhaitez voir la création automatique de ces statistiques, une requête déclenche la création si les statistiques pour COLUMN_NAME n’existent pas encore. Par exemple :

SELECT <COLUMN_NAME>
FROM <YOUR_TABLE_NAME>
GROUP BY <COLUMN_NAME>;

Dans ce cas, vous devez vous attendre à ce que des statistiques pour COLUMN_NAME aient été créées. Si la colonne était également une colonne varchar, vous verrez également des statistiques de longueur moyenne de colonne créées. Si vous souhaitez vérifier que les statistiques ont été créées automatiquement, vous pouvez exécuter la requête suivante :

select
    object_name(s.object_id) AS [object_name],
    c.name AS [column_name],
    s.name AS [stats_name],
    s.stats_id,
    STATS_DATE(s.object_id, s.stats_id) AS [stats_update_date], 
    s.auto_created,
    s.user_created,
    s.stats_generation_method_desc 
FROM sys.stats AS s 
INNER JOIN sys.objects AS o 
ON o.object_id = s.object_id 
INNER JOIN sys.stats_columns AS sc 
ON s.object_id = sc.object_id 
AND s.stats_id = sc.stats_id 
INNER JOIN sys.columns AS c 
ON sc.object_id = c.object_id 
AND c.column_id = sc.column_id
WHERE o.type = 'U' -- Only check for stats on user-tables
    AND s.auto_created = 1
    AND o.name = '<YOUR_TABLE_NAME>'
ORDER BY object_name, column_name;

Cette requête recherche uniquement des statistiques basées sur des colonnes. Si vous souhaitez voir toutes les statistiques qui existent pour cette table, supprimez les VALEURS JOIN sur sys.stats_columns et sys.columns.

À présent, vous pouvez trouver le statistics_name de la statistique d’histogramme généré automatiquement (qui doit ressembler _WA_Sys_00000007_3B75D760à ) et exécuter le T-SQL suivant :

DBCC SHOW_STATISTICS ('<YOUR_TABLE_NAME>', '<statistics_name>');

Par exemple :

DBCC SHOW_STATISTICS ('sales.FactInvoice', '_WA_Sys_00000007_3B75D760');

La valeur Updated dans le jeu de résultats de DBCC SHOW_STATISTICS doit être une date (utc) similaire à celle de l’exécution de la requête GROUP BY d’origine.

Ces statistiques générées automatiquement peuvent ensuite être exploitées dans les requêtes suivantes par le moteur de requête pour améliorer le coût du plan et l’efficacité de l’exécution. Si suffisamment de modifications se produisent dans la table, le moteur de requête actualise également ces statistiques pour améliorer l’optimisation des requêtes. Le même exercice précédent peut être appliqué après avoir modifié la table de manière significative. Dans Fabric, le moteur de requête SQL utilise le même seuil de recompilation que SQL Server 2016 (13.x) pour actualiser les statistiques.

Types de statistiques générées automatiquement

Dans Microsoft Fabric, plusieurs types de statistiques sont générés automatiquement par le moteur pour améliorer les plans de requête. Actuellement, ils peuvent être trouvés dans sys.stats , bien que tous ne soient pas actionnables :

  • Statistiques d’histogramme
    • Créé par colonne nécessitant des statistiques d’histogramme au moment de la requête
    • Ces objets contiennent des informations d’histogramme et de densité concernant la distribution d’une colonne particulière. Semblable aux statistiques créées automatiquement au moment de la requête dans les pools dédiés Azure Synapse Analytics.
    • Le nom commence par _WA_Sys_.
    • Le contenu peut être consulté avec DBCC SHOW_STATISTICS
  • Statistiques de longueur moyenne de colonne
    • Créé pour les colonnes de caractères variables (varchar) supérieures à 100 nécessitant une longueur moyenne de colonne au moment de la requête.
    • Ces objets contiennent une valeur représentant la taille de ligne moyenne de la colonne varchar au moment de la création des statistiques.
    • Le nom commence par ACE-AverageColumnLength_.
    • Le contenu ne peut pas être consulté et ne peut pas être consulté par l’utilisateur.
  • Statistiques de cardinalité basées sur des tables
    • Créé par table nécessitant une estimation de cardinalité au moment de la requête.
    • Ces objets contiennent une estimation du nombre de lignes d’une table.
    • Nommé ACE-Cardinality.
    • Le contenu ne peut pas être consulté et ne peut pas être consulté par l’utilisateur.

Limites

  • Seules les statistiques d’histogramme à colonne unique peuvent être créées et modifiées manuellement.
  • La création de statistiques à plusieurs colonnes n’est pas prise en charge.
  • D’autres objets statistiques peuvent s’afficher dans sys.stats en dehors des statistiques créées manuellement et des statistiques créées automatiquement. Ces objets ne sont pas utilisés pour l’optimisation des requêtes.

Contenu connexe