Note
L’accès à cette page nécessite une autorisation. Vous pouvez essayer de vous connecter ou de changer d’annuaire.
L’accès à cette page nécessite une autorisation. Vous pouvez essayer de changer d’annuaire.
Dans cet article, vous trouverez des recommandations et des exemples pour créer et mettre à jour des statistiques d’optimisation des requêtes à l’aide des ressources Synapse SQL : pool SQL dédié et pool SQL serverless.
Statistiques dans un pool SQL dédié
Pourquoi utiliser des statistiques ?
Le pool SQL plus dédié connaît vos données, plus il peut exécuter des requêtes plus rapidement. Après avoir chargé des données dans un pool SQL dédié, la collecte de statistiques sur vos données est l’une des opérations les plus importantes que vous pouvez effectuer pour l’optimisation des requêtes.
L’optimiseur de requête du pool SQL dédié s’appuie sur les coûts. Il compare le coût de différents plans de requête, puis choisit le plan avec le coût le plus bas. Dans la plupart des cas, il choisit le plan qui s’exécute le plus rapidement.
Par exemple, si l’optimiseur estime que la date de filtrage de votre requête va renvoyer une ligne, il choisira un seul plan. S’il estime que la date sélectionnée va renvoyer un million de lignes, il choisira un autre plan.
Création automatique de statistiques
Le moteur de pool SQL dédié analysera les requêtes utilisateur entrantes pour les statistiques manquantes lorsque l’option de base de données AUTO_CREATE_STATISTICS est définie à ON. Si des statistiques manquent, l’optimiseur de requête crée des statistiques sur des colonnes individuelles dans le prédicat de requête ou la condition de jointure.
Cette fonction est utilisée pour améliorer les estimations de cardinalité pour le plan de requête.
Important
La création automatique de statistiques est activée par défaut.
Vous pouvez vérifier si AUTO_CREATE_STATISTICS est configurée pour votre entrepôt de données en exécutant la commande suivante :
SELECT name, is_auto_create_stats_on
FROM sys.databases
Si votre entrepôt de données n’a pas AUTO_CREATE_STATISTICS activé, nous vous recommandons d’activer cette propriété en exécutant la commande suivante :
ALTER DATABASE <yourdatawarehousename>
SET AUTO_CREATE_STATISTICS ON
Ces instructions déclenchent la création automatique de statistiques :
- Sélectionner
- INSERT-SELECT
- CTAS
- MISE À JOUR
- Supprimer
- EXPLAIN quand elles contiennent une jointure ou que la présence d’un prédicat est détectée
Remarque
La création automatique de statistiques n’est pas générée sur des tables temporaires ou externes.
La création automatique de statistiques est effectuée de façon synchrone. Par conséquent, vous pourriez avoir des performances de requête légèrement dégradées si vos colonnes manquent de statistiques. La durée de création de statistiques pour une seule colonne dépend de la taille de la table.
Pour éviter toute détérioration notable des performances, vous devez vérifier que les statistiques ont été créées en exécutant la charge de travail du test d’évaluation avant de profiler le système.
Remarque
La création de statistiques est journalisée dans sys.dm_pdw_exec_requests sous un contexte utilisateur distinct.
Lorsque des statistiques automatiques sont créées, elles prendront la forme : WA_Sys<identifiant de colonne à 8 chiffres en Hex>_<identifiant de table à 8 chiffres en Hex>. Vous pouvez afficher les statistiques déjà créées en exécutant la commande DBCC SHOW_STATISTICS :
DBCC SHOW_STATISTICS (<table_name>, <target>)
Le table_name est le nom de la table qui contient les statistiques à afficher, qui ne peuvent pas être une table externe. La cible est le nom de l’index, des statistiques ou de la colonne cibles pour lesquelles afficher des informations statistiques.
Mettre à jour les statistiques
Une meilleure pratique consiste à mettre à jour les statistiques sur les colonnes de date à chaque fois qu’une date est ajoutée. Chaque fois que de nouvelles lignes sont chargées dans l’entrepôt de données, de nouvelles dates de chargement ou de dates de transaction sont ajoutées. Ces ajouts affectent la distribution des données et rendent les statistiques obsolètes.
Les statistiques sur une colonne pays ou région d’une table client n’ont peut-être jamais besoin d’être mises à jour, car la distribution des valeurs ne change généralement pas. Si l’on part du principe que la distribution des données est constante d’un client à l’autre, l’ajout de nouvelles lignes dans une table ne devrait pas affecter cette distribution.
Toutefois, lorsque votre entrepôt de données ne contient qu’un seul pays ou région et que vous apportez des données à partir d’un nouveau pays ou d’une nouvelle région, vous devez mettre à jour les statistiques sur la colonne pays ou région.
Voici certaines suggestions pour la mise à jour des statistiques :
| Catégorie | Recommandation |
|---|---|
| Fréquence des mises à jour des statistiques | Classique : quotidienne Après le chargement ou la transformation de données |
| Échantillonnage | Pour moins d’un milliard de lignes, utilisez le taux d’échantillonnage par défaut (20 %).
Pour plus d’un milliard de lignes, utilisez le taux d’échantillonnage de 2 %. |
Déterminer la dernière mise à jour des statistiques
L’une des premières questions à poser lorsque vous résolvez une requête est la suivante : « Les statistiques sont-elles à jour ? »
Cette question n’est pas une question qui peut être répondue par l’âge des données. Un objet de statistiques à jour peut être ancien si aucune modification notable n’affecte les données sous-jacentes. Lorsque le nombre de lignes a changé considérablement ou qu’une modification matérielle de la distribution des valeurs d’une colonne se produit, il est temps de mettre à jour les statistiques.
Il n’existe pas de vue de gestion dynamique disponible pour déterminer si les données de la table ont changé depuis la dernière mise à jour des statistiques. Connaître l’âge de vos statistiques peut vous fournir une partie de l’image.
Vous pouvez utiliser la requête suivante pour déterminer la dernière fois que vos statistiques ont été mises à jour sur chaque table.
Remarque
S’il existe une modification matérielle de la distribution des valeurs d’une colonne, vous devez mettre à jour les statistiques quelle que soit la dernière fois qu’elles ont été mises à jour.
SELECT
sm.[name] AS [schema_name],
tb.[name] AS [table_name],
co.[name] AS [stats_column_name],
st.[name] AS [stats_name],
STATS_DATE(st.[object_id],st.[stats_id]) AS [stats_last_updated_date]
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.[user_created] = 1;
Les colonnes de date d’un entrepôt de données, par exemple, nécessitent généralement des mises à jour fréquentes des statistiques. Chaque fois que de nouvelles lignes sont chargées dans l’entrepôt de données, de nouvelles dates de chargement ou de dates de transaction sont ajoutées. Ces ajouts affectent la distribution des données et rendent les statistiques obsolètes.
Les statistiques sur une colonne de sexe dans une table client n’ont peut-être jamais besoin d’être mises à jour. Si l’on part du principe que la distribution des données est constante d’un client à l’autre, l’ajout de nouvelles lignes dans une table ne devrait pas affecter cette distribution.
Toutefois, si votre entrepôt de données ne contient qu’un seul sexe et qu’une nouvelle exigence entraîne plusieurs sexes, vous devez mettre à jour les statistiques sur la colonne sexe.
Pour plus d’informations, consultez l’article Statistiques .
Implémenter la gestion des statistiques
Il est souvent judicieux d’étendre le processus de chargement des données afin de vérifier que les statistiques sont mises à jour à la fin du chargement. Le chargement des données se produit lorsque la taille ou la distribution des valeurs, voire les deux, sont souvent modifiées dans les tables. Dès lors, il est logique que le processus de chargement implémente certains processus de gestion.
Les principes généraux suivants sont fournis afin de vous aider à mettre à jour vos statistiques pendant le processus de chargement :
- Assurez-vous que chaque table chargée présente au moins un objet de statistiques mis à jour. Cela met à jour les informations sur la taille des tables (nombre de lignes et de pages) dans le cadre de la mise à jour des statistiques.
- Concentrez-vous sur les colonnes participant aux clauses JOIN, GROUP BY, ORDER BY et DISTINCT.
- Envisagez de mettre plus souvent à jour les colonnes de clé croissante, comme celles des dates de transactions, car ces valeurs ne seront pas incluses dans l’histogramme des statistiques.
- Envisagez de mettre moins souvent à jour les colonnes de distribution statiques.
- N’oubliez pas que chaque objet de statistiques est mis à jour dans l’ordre. L’implémentation de l’élément
UPDATE STATISTICS <TABLE_NAME>ne suffit pas toujours, notamment quand les tables sont volumineuses et incluent un grand nombre d’objets de statistiques.
Pour plus d’informations, consultez Évaluation de la cardinalité.
Exemples : Create statistics
Ces exemples indiquent comment utiliser différentes options pour créer des statistiques. Les options que vous utilisez pour chaque colonne dépendent des caractéristiques de vos données et de la façon dont la colonne sera utilisée dans les requêtes.
Créer des statistiques sur une colonne en utilisant les options par défaut
Pour créer des statistiques sur une colonne, indiquez le nom de l’objet de statistiques ainsi que celui de la colonne. Cette syntaxe a recours à toutes les options par défaut. Par défaut, le pool SQL dédié échantillonne 20 % de la table lorsqu’il crée des statistiques.
CREATE STATISTICS [statistics_name]
ON [schema_name].[table_name]([column_name]);
Par exemple:
CREATE STATISTICS col1_stats
ON dbo.table1 (col1);
Créer des statistiques sur plusieurs colonnes en examinant chaque ligne
Le taux d’échantillonnage par défaut est de 20 %, ce qui est suffisant pour la plupart des situations. Toutefois, vous pouvez l’ajuster en fonction de vos besoins. Pour échantillonner la table entière, utilisez la syntaxe suivante :
CREATE STATISTICS [statistics_name]
ON [schema_name].[table_name]([column_name])
WITH FULLSCAN;
Par exemple:
CREATE STATISTICS col1_stats
ON dbo.table1 (col1)
WITH FULLSCAN;
Créer des statistiques sur une colonne en spécifiant la taille de l’échantillon
Une autre option dont vous avez besoin consiste à spécifier la taille de l’échantillon sous la forme d’un pourcentage :
CREATE STATISTICS col1_stats
ON dbo.table1 (col1)
WITH SAMPLE 50 PERCENT;
Créer des statistiques sur une colonne sur certaines lignes uniquement
Vous pouvez également créer des statistiques sur une partie des lignes de votre table, appelée statistique filtrée.
Par exemple, vous pouvez utiliser les statistiques filtrées quand vous prévoyez d’interroger une partition spécifique dans une table partitionnée volumineuse. En créant des statistiques uniquement sur les valeurs de partition, la précision des statistiques s’améliore. Vous découvrirez également une amélioration des performances des requêtes.
Dans cet exemple, des statistiques sont créées sur une plage de valeurs. Les valeurs peuvent facilement être définies de manière à correspondre à la plage de valeurs d’une partition.
CREATE STATISTICS stats_col1
ON table1(col1)
WHERE col1 > '2000101' AND col1 < '20001231';
Remarque
Pour que l’optimiseur de requête envisage d’utiliser les statistiques filtrées lorsqu’il choisit le plan de requête distribuée, il faut que cette requête soit suffisamment petite pour pouvoir s’insérer dans la définition de l’objet de statistiques. Selon l’exemple précédent, la clause WHERE de la requête doit indiquer des valeurs incluses entre 2000101 et 20001231 dans la colonne col1.
Créer des statistiques sur une colonne en utilisant toutes les options
Vous pouvez aussi combiner les options. L’exemple suivant permet de créer un objet de statistiques filtrées avec une taille d’échantillon personnalisée :
CREATE STATISTICS stats_col1
ON table1 (col1)
WHERE col1 > '2000101' AND col1 < '20001231'
WITH SAMPLE 50 PERCENT;
Pour accéder à la référence complète, consultez CREATE STATISTICS.
Créer des statistiques sur plusieurs colonnes
Pour créer un objet de statistiques sur plusieurs colonnes, utilisez les exemples précédents en spécifiant davantage de colonnes.
Remarque
L’histogramme, qui est utilisé pour estimer le nombre de lignes dans le résultat d’une requête, est uniquement disponible pour la première colonne répertoriée dans la définition d’objet de statistiques.
Dans cet exemple, l’histogramme se trouve sur product_category. Les statistiques inter-colonnes sont calculées sur product_category et product_sub_category :
CREATE STATISTICS stats_2cols
ON table1 (product_category, product_sub_category)
WHERE product_category > '2000101' AND product_category < '20001231'
WITH SAMPLE 50 PERCENT;
Étant donné qu’une corrélation existe entre product_category et product_sub_category, un objet de statistiques à plusieurs colonnes peut être utile si ces colonnes sont accessibles en même temps. Lors de l’interrogation de cette table, les statistiques à plusieurs colonnes améliorent les estimations de cardinalité pour les jointures, les agrégations GROUP BY, les nombres distincts et les filtres WHERE (tant que la colonne de statistiques primaires fait partie du filtre).
Créer des statistiques sur toutes les colonnes d’une table
Une façon de créer des statistiques consiste à émettre des commandes CREATE STATISTICS après avoir créé la table :
CREATE TABLE dbo.table1
(
col1 int
, col2 int
, col3 int
)
WITH
(
CLUSTERED COLUMNSTORE INDEX
)
;
CREATE STATISTICS stats_col1 on dbo.table1 (col1);
CREATE STATISTICS stats_col2 on dbo.table2 (col2);
CREATE STATISTICS stats_col3 on dbo.table3 (col3);
Utiliser une procédure stockée pour créer des statistiques sur toutes les colonnes d’une base de données
Le pool SQL n’a pas de procédure stockée système équivalente à sp_create_stats dans SQL Server. Cette procédure stockée crée un objet de statistiques de colonne unique sur chaque colonne de la base de données qui n’a pas déjà de statistiques.
L’exemple suivant vous aidera à bien démarrer avec la conception de votre base de données. N’hésitez pas à l’adapter à vos besoins :
CREATE PROCEDURE [dbo].[prc_sqldw_create_stats]
( @create_type tinyint -- 1 default, 2 Fullscan, 3 Sample
, @sample_pct tinyint
)
AS
IF @create_type IS NULL
BEGIN
SET @create_type = 1;
END;
IF @create_type NOT IN (1,2,3)
BEGIN
THROW 151000,'Invalid value for @stats_type parameter. Valid range 1 (default), 2 (fullscan) or 3 (sample).',1;
END;
IF @sample_pct IS NULL
BEGIN;
SET @sample_pct = 20;
END;
IF OBJECT_ID('tempdb..#stats_ddl') IS NOT NULL
BEGIN;
DROP TABLE #stats_ddl;
END;
CREATE TABLE #stats_ddl
WITH ( DISTRIBUTION = HASH([seq_nmbr])
, LOCATION = USER_DB
)
AS
WITH T
AS
(
SELECT t.[name] AS [table_name]
, s.[name] AS [table_schema_name]
, c.[name] AS [column_name]
, c.[column_id] AS [column_id]
, t.[object_id] AS [object_id]
, ROW_NUMBER()
OVER(ORDER BY (SELECT NULL)) AS [seq_nmbr]
FROM sys.[tables] t
JOIN sys.[schemas] s ON t.[schema_id] = s.[schema_id]
JOIN sys.[columns] c ON t.[object_id] = c.[object_id]
LEFT JOIN sys.[stats_columns] l ON l.[object_id] = c.[object_id]
AND l.[column_id] = c.[column_id]
AND l.[stats_column_id] = 1
LEFT JOIN sys.[external_tables] e ON e.[object_id] = t.[object_id]
WHERE l.[object_id] IS NULL
AND e.[object_id] IS NULL -- not an external table
)
SELECT [table_schema_name]
, [table_name]
, [column_name]
, [column_id]
, [object_id]
, [seq_nmbr]
, CASE @create_type
WHEN 1
THEN CAST('CREATE STATISTICS '+QUOTENAME('stat_'+table_schema_name+ '_' + table_name + '_'+column_name)+' ON '+QUOTENAME(table_schema_name)+'.'+QUOTENAME(table_name)+'('+QUOTENAME(column_name)+')' AS VARCHAR(8000))
WHEN 2
THEN CAST('CREATE STATISTICS '+QUOTENAME('stat_'+table_schema_name+ '_' + table_name + '_'+column_name)+' ON '+QUOTENAME(table_schema_name)+'.'+QUOTENAME(table_name)+'('+QUOTENAME(column_name)+') WITH FULLSCAN' AS VARCHAR(8000))
WHEN 3
THEN CAST('CREATE STATISTICS '+QUOTENAME('stat_'+table_schema_name+ '_' + table_name + '_'+column_name)+' ON '+QUOTENAME(table_schema_name)+'.'+QUOTENAME(table_name)+'('+QUOTENAME(column_name)+') WITH SAMPLE '+CONVERT(varchar(4),@sample_pct)+' PERCENT' AS VARCHAR(8000))
END AS create_stat_ddl
FROM T
;
DECLARE @i INT = 1
, @t INT = (SELECT COUNT(*) FROM #stats_ddl)
, @s NVARCHAR(4000) = N''
;
WHILE @i <= @t
BEGIN
SET @s=(SELECT create_stat_ddl FROM #stats_ddl WHERE seq_nmbr = @i);
PRINT @s
EXEC sp_executesql @s
SET @i+=1;
END
DROP TABLE #stats_ddl;
Pour créer des statistiques sur toutes les colonnes de la table avec les valeurs par défaut, exécutez la procédure stockée.
EXEC [dbo].[prc_sqldw_create_stats] 1, NULL;
Pour créer des statistiques sur toutes les colonnes de la table à l’aide d’un balayage complet, appelez cette procédure :
EXEC [dbo].[prc_sqldw_create_stats] 2, NULL;
Pour créer des statistiques échantillonées sur toutes les colonnes de la table, entrez 3 et l’exemple de pourcentage. La procédure ci-dessous utilise un taux d’échantillonnage de 20 %.
EXEC [dbo].[prc_sqldw_create_stats] 3, 20;
Exemples : Mettre à jour les statistiques
Pour effectuer cette opération, vous avez différentes possibilités :
- Mettez à jour un objet de statistiques. Spécifiez le nom de l’objet de statistiques que vous souhaitez mettre à jour.
- Mettez à jour tous les objets de statistiques sur une table. Spécifiez le nom de la table, et non un objet de statistiques spécifique.
Mettre à jour un objet de statistiques spécifique
Pour réaliser cette opération, utilisez la syntaxe suivante :
UPDATE STATISTICS [schema_name].[table_name]([stat_name]);
Par exemple:
UPDATE STATISTICS [dbo].[table1] ([stats_col1]);
En mettant à jour des objets de statistiques spécifiques, vous pouvez réduire les ressources et le temps requis pour gérer les statistiques. Cette action nécessite une réflexion pour sélectionner les meilleurs objets de statistiques à mettre à jour.
Mettre à jour toutes les statistiques d'une table
Voici une méthode simple pour mettre à jour tous les objets de statistiques dans une table :
UPDATE STATISTICS [schema_name].[table_name];
Par exemple:
UPDATE STATISTICS dbo.table1;
L’instruction UPDATE STATISTICS est facile à utiliser. N’oubliez pas qu’il met à jour toutes les statistiques sur la table, ce qui demande plus de travail que nécessaire.
Si les performances ne sont pas un problème, cette méthode est la méthode la plus simple et la plus complète pour garantir que les statistiques sont à jour.
Remarque
Lors de la mise à jour de toutes les statistiques sur une table, un pool SQL dédié effectue une analyse pour échantillonner la table pour chaque objet de statistiques. Si la table est volumineuse et comprend un grand nombre de colonnes et de statistiques, il peut s’avérer plus efficace de mettre à jour les statistiques individuellement, en fonction des besoins.
Pour une implémentation d’une UPDATE STATISTICS procédure, consultez tables temporaires. La méthode d’implémentation est légèrement différente de celle de la procédure CREATE STATISTICS précédente, mais le résultat est le même.
Pour obtenir la syntaxe complète, consultez Mettre à jour les statistiques.
Métadonnées de statistiques
Vous pouvez utiliser plusieurs fonctions et vues système pour rechercher des informations sur des statistiques. Par exemple, vous pouvez voir si un objet de statistiques peut être obsolète à l’aide de la fonction STATS_DATE(). STATS_DATE() vous permet de voir quand des statistiques ont été créées ou mises à jour pour la dernière fois.
Vues de catalogue des statistiques
Ces vues système fournissent des informations sur les statistiques :
| Affichage catalogue | Descriptif |
|---|---|
| sys.columns | Une ligne pour chaque colonne. |
| sys.objects | Une ligne pour chaque objet de la base de données. |
| sys.schemas | Une ligne pour chaque schéma de la base de données. |
| sys.stats | Une ligne pour chaque objet de statistiques. |
| sys.stats_columns | Une ligne pour chaque colonne de l’objet de statistiques. Paramètre lié à l’élément « sys.columns ». |
| sys.tables | Une ligne pour chaque table (inclut des tables externes). |
| sys.table_types | Une ligne pour chaque type de données. |
Fonctions système relatives aux statistiques
Ces fonctions système sont utiles lorsque vous gérez des statistiques :
| Fonction système | Descriptif |
|---|---|
| STATS_DATE | Date de la dernière mise à jour de l’objet de statistiques. |
| DBCC SHOW_STATISTICS | Niveau de résumé et informations détaillées sur la distribution des valeurs, telles que comprises par l’objet de statistiques. |
Combiner des fonctions et des colonnes de statistiques en une seule vue
Cette vue regroupe les colonnes qui concernent les statistiques et les résultats de la fonction STATS_DATE().
CREATE VIEW dbo.vstats_columns
AS
SELECT
sm.[name] AS [schema_name]
, tb.[name] AS [table_name]
, st.[name] AS [stats_name]
, st.[filter_definition] AS [stats_filter_definition]
, st.[has_filter] AS [stats_is_filtered]
, STATS_DATE(st.[object_id],st.[stats_id])
AS [stats_last_updated_date]
, co.[name] AS [stats_column_name]
, ty.[name] AS [column_type]
, co.[max_length] AS [column_max_length]
, co.[precision] AS [column_precision]
, co.[scale] AS [column_scale]
, co.[is_nullable] AS [column_is_nullable]
, co.[collation_name] AS [column_collation_name]
, QUOTENAME(sm.[name])+'.'+QUOTENAME(tb.[name])
AS two_part_name
, QUOTENAME(DB_NAME())+'.'+QUOTENAME(sm.[name])+'.'+QUOTENAME(tb.[name])
AS three_part_name
FROM sys.objects AS ob
JOIN sys.stats AS st ON ob.[object_id] = st.[object_id]
JOIN sys.stats_columns AS sc ON st.[stats_id] = sc.[stats_id]
AND st.[object_id] = sc.[object_id]
JOIN sys.columns AS co ON sc.[column_id] = co.[column_id]
AND sc.[object_id] = co.[object_id]
JOIN sys.types AS ty ON co.[user_type_id] = ty.[user_type_id]
JOIN sys.tables AS tb ON co.[object_id] = tb.[object_id]
JOIN sys.schemas AS sm ON tb.[schema_id] = sm.[schema_id]
WHERE 1=1
AND st.[user_created] = 1
;
Exemples portant sur la fonction DBCC SHOW_STATISTICS()
DBCC SHOW_STATISTICS() affiche les données conservées dans un objet de statistiques. Ces données sont affichées en trois parties :
- En-tête de page
- Vecteur de densité
- Histogramme
L’en-tête est les métadonnées relatives aux statistiques. L’histogramme affiche la distribution des valeurs dans la première colonne de l’objet de statistiques.
Le vecteur de densité mesure la corrélation entre les colonnes. Le pool SQL dédié calcule les évaluations de cardinalité avec certaines données dans l’objet de statistiques.
Afficher l’en-tête, la densité et l’histogramme
Cet exemple simple illustre les trois parties d’un objet de statistiques :
DBCC SHOW_STATISTICS([<schema_name>.<table_name>],<stats_name>)
Par exemple:
DBCC SHOW_STATISTICS ('dbo.table1', 'stats_col1');
Afficher une ou plusieurs parties de la fonction DBCC SHOW_STATISTICS()
Si vous êtes uniquement intéressé par l’affichage de certaines parties, utilisez la clause WITH et spécifiez les parties que vous voulez voir :
DBCC SHOW_STATISTICS([<schema_name>.<table_name>],<stats_name>)
WITH stat_header, histogram, density_vector
Par exemple:
DBCC SHOW_STATISTICS ('dbo.table1', 'stats_col1')
WITH histogram, density_vector
Différences liées à la fonction DBCC SHOW_STATISTICS()
DBCC SHOW_STATISTICS() est implémentée de manière plus stricte dans le pool SQL dédié que dans SQL Server :
- Les fonctionnalités non documentées ne sont pas prises en charge.
- Impossible d’utiliser Stats_stream.
- Impossible de joindre les résultats de sous-ensembles spécifiques de données de statistiques. Par exemple, STAT_HEADER JOIN DENSITY_VECTOR.
- NO_INFOMSGS ne peut pas être défini pour la suppression des messages.
- Vous ne pouvez pas placer de crochets autour des noms de statistiques.
- Vous ne pouvez pas utiliser les noms de colonnes pour identifier les objets de statistiques.
- L’erreur personnalisée 2767 n’est pas prise en charge.
Statistiques dans un pool SQL serverless
Les statistiques sont créées par colonne particulière pour un jeu de données particulier (chemin de stockage).
Remarque
Les statistiques ne peuvent pas être créées pour les colonnes LOB.
Pourquoi utiliser des statistiques ?
Plus le pool SQL serverless connaît vos données, plus il peut exécuter des requêtes dessus. La collecte de statistiques sur vos données est l’une des choses les plus importantes que vous pouvez faire pour optimiser vos requêtes.
L’optimiseur de requête de pool SQL serverless est un optimiseur basé sur les coûts. Il compare le coût de différents plans de requête, puis choisit le plan avec le coût le plus bas. Dans la plupart des cas, il choisit le plan qui s’exécute le plus rapidement.
Par exemple, si l’optimiseur estime que la date de filtrage de votre requête va renvoyer une ligne, il choisira un certain plan. Si elle estime que la date sélectionnée retourne 1 million de lignes, elle choisit un autre plan.
Création automatique de statistiques
Le pool SQL serverless analyse les requêtes utilisateur entrantes pour les statistiques manquantes. Si des statistiques manquent, l’optimiseur de requête crée des statistiques sur des colonnes individuelles dans le prédicat de requête ou la condition de jointure afin d’améliorer les estimations de cardinalité pour le plan de requête.
L'instruction SELECT déclenche la création automatique de statistiques.
Remarque
Pour la création automatique des statistiques, l'échantillonnage est utilisé et dans la plupart des cas, le pourcentage d'échantillonnage sera inférieur à 100%. Ce flux est le même pour chaque format de fichier. N’oubliez pas que lors de la lecture de CSV avec la version 1.0 de l’analyseur, l’échantillonnage n’est pas pris en charge et que la création automatique de statistiques ne se produit pas avec un pourcentage d’échantillonnage inférieur à 100%. Pour les petites tables avec une faible cardinalité estimée (nombre de lignes), la création automatique de statistiques est déclenchée avec un pourcentage d’échantillonnage de 100 %. Cela signifie essentiellement que l’analyse complète est déclenchée et que les statistiques automatiques sont créées même pour csv avec l’analyseur version 1.0.
La création automatique de statistiques est effectuée de façon synchrone, ce qui peut entraîner une légère dégradation des performances de requête si vos colonnes n'ont pas de statistiques. La durée de création de statistiques pour une seule colonne dépend de la taille des fichiers ciblés.
Création manuelle de statistiques
Le pool SQL serverless vous permet de créer manuellement des statistiques. Si vous utilisez l’analyseur version 1.0 avec CSV, vous devrez probablement créer des statistiques manuellement, car cette version de l’analyseur ne prend pas en charge l’échantillonnage. La création automatique de statistiques dans le cas de l’analyseur version 1.0 ne se produit pas, sauf si le pourcentage d’échantillonnage est de 100%.
Consultez les exemples suivants pour obtenir des instructions sur la création manuelle de statistiques.
Mettre à jour les statistiques
Les modifications apportées aux données dans les fichiers, la suppression et l’ajout de fichiers entraînent des modifications de distribution des données et rendent les statistiques obsolètes. Dans ce cas, les statistiques doivent être mises à jour.
Le pool SQL serverless recrée automatiquement des statistiques pour les colonnes OPENROWSET si les données sont modifiées de manière significative. Chaque fois que des statistiques sont créées automatiquement, l’état du jeu de données est enregistré : chemins d’accès aux fichiers, tailles, dates de la dernière modification.
Lorsque les statistiques sont obsolètes, les nouvelles sont créées. L’algorithme passe par les données et les compare à l’état actuel du jeu de données. Si la taille des modifications est supérieure au seuil spécifique, les anciennes statistiques sont supprimées et seront recréées sur le nouveau jeu de données.
Les statistiques manuelles ne sont jamais déclarées obsolètes.
Remarque
Pour la création automatique des statistiques, un échantillonnage est utilisé et, dans la plupart des cas, le pourcentage d'échantillonnage sera inférieur à 100%. Ce flux est le même pour chaque format de fichier. Gardez à l'esprit que lors de la lecture d'un fichier CSV avec la version 1.0 du parser, l'échantillonnage n'est pas pris en charge et la recréation automatique des statistiques ne se produira pas avec un pourcentage d'échantillonnage inférieur à 100%. Dans ce cas, vous devez supprimer et recréer manuellement des statistiques. Consultez les exemples ci-dessous pour savoir comment supprimer et créer des statistiques. Pour les petites tables avec une faible cardinalité estimée (nombre de lignes), la recréation automatique des statistiques sera déclenchée avec un pourcentage d’échantillonnage de 100 %. Cela signifie essentiellement que l’analyse complète est déclenchée et que les statistiques automatiques sont créées même pour csv avec l’analyseur version 1.0.
L’une des premières questions à poser lorsque vous résolvez une requête est la suivante : « Les statistiques sont-elles à jour ? »
Lorsque le nombre de lignes ou la distribution des valeurs change de manière substantielle dans une colonne, il convient alors de mettre à jour les statistiques.
Remarque
S’il existe une modification matérielle de la distribution des valeurs d’une colonne, vous devez mettre à jour les statistiques quelle que soit la dernière fois qu’elles ont été mises à jour.
Implémenter la gestion des statistiques
Vous pouvez étendre votre pipeline de données pour vous assurer que les statistiques sont mises à jour lorsque les données sont considérablement modifiées par l’ajout, la suppression ou la modification de fichiers.
Les principes généraux suivants sont fournis afin de vous aider à mettre à jour vos statistiques :
- Vérifiez que le jeu de données a au moins un objet de statistiques mis à jour. Cette mise à jour met à jour les informations de taille (nombre de lignes et nombre de pages) dans le cadre de la mise à jour des statistiques.
- Concentrez-vous sur les colonnes participant aux clauses WHERE, JOIN, GROUP BY, ORDER BY et DISTINCT.
- Mettez à jour plus fréquemment les colonnes « clés ascendantes », telles que les dates de transaction, car ces valeurs ne seront pas incluses dans l’histogramme des statistiques.
- Mettez à jour les colonnes de distribution statique moins fréquemment.
Pour plus d’informations, consultez Évaluation de la cardinalité.
Exemples : Créer des statistiques pour la colonne dans le chemin OPENROWSET
Les exemples suivants vous montrent comment utiliser différentes options pour créer des statistiques dans des pools SQL serverless Azure Synapse. Les options que vous utilisez pour chaque colonne dépendent des caractéristiques de vos données et de la façon dont la colonne sera utilisée dans les requêtes. Pour plus d’informations sur les procédures stockées utilisées dans ces exemples, passez en revue les sys.sp_create_openrowset_statistics et les sys.sp_drop_openrowset_statistics, qui s’appliquent uniquement aux pools SQL serverless.
Remarque
Vous ne pouvez créer des statistiques à une seule colonne qu’à ce stade.
Les autorisations suivantes sont requises pour exécuter sp_create_openrowset_statistics et sp_drop_openrowset_statistics: ADMINISTRATION DES OPÉRATIONS EN BLOC ou ADMINISTRATION DES OPÉRATIONS EN BLOC DE BASE DE DONNÉES.
La procédure stockée suivante est utilisée pour créer des statistiques :
sys.sp_create_openrowset_statistics [ @stmt = ] N'statement_text'
Arguments : [ @stmt = ] N’statement_text' : spécifie une instruction Transact-SQL qui retourne les valeurs de colonne à utiliser pour les statistiques. Vous pouvez utiliser TABLESAMPLE pour spécifier des exemples de données à utiliser. Si TABLESAMPLE n’est pas spécifié, FULLSCAN est utilisé.
<tablesample_clause> ::= TABLESAMPLE ( sample_number PERCENT )
Remarque
L’échantillonnage CSV ne fonctionne pas si vous utilisez l’analyseur version 1.0, seul FULLSCAN est pris en charge pour CSV avec l’analyseur version 1.0.
Créer des statistiques sur plusieurs colonnes en examinant chaque ligne
Pour créer des statistiques sur une colonne, fournissez une requête qui retourne la colonne pour laquelle vous avez besoin de statistiques.
Par défaut, si vous ne spécifiez pas autrement lors de la création manuelle de statistiques, le pool SQL serverless utilise 100% des données fournies dans le jeu de données pour créer des statistiques.
Par exemple, pour créer des statistiques avec des options par défaut (FULLSCAN) pour une colonne de population du jeu de données basé sur le fichier us_population.csv :
EXEC sys.sp_create_openrowset_statistics N'SELECT
population
FROM OPENROWSET(
BULK ''https://azureopendatastorage.blob.core.windows.net/censusdatacontainer/raw_us_population_county/us_population.csv'',
FORMAT = ''CSV'',
PARSER_VERSION = ''2.0'',
HEADER_ROW = TRUE)
AS [r]'
Créer des statistiques sur une colonne en spécifiant la taille de l’échantillon
Vous pouvez spécifier la taille de l’échantillon sous la forme d’un pourcentage :
/* make sure you have credentials for storage account access created
IF EXISTS (SELECT * FROM sys.credentials WHERE name = 'https://azureopendatastorage.blob.core.windows.net/censusdatacontainer')
DROP CREDENTIAL [https://azureopendatastorage.blob.core.windows.net/censusdatacontainer]
GO
CREATE CREDENTIAL [https://azureopendatastorage.blob.core.windows.net/censusdatacontainer]
WITH IDENTITY='SHARED ACCESS SIGNATURE',
SECRET = ''
GO
*/
EXEC sys.sp_create_openrowset_statistics N'SELECT payment_type
FROM OPENROWSET(
BULK ''https://sqlondemandstorage.blob.core.windows.net/parquet/taxi/year=2018/month=6/*.parquet'',
FORMAT = ''PARQUET''
) AS [nyc]
TABLESAMPLE(5 PERCENT)
'
Exemples : Mettre à jour les statistiques
Pour mettre à jour les statistiques, vous devez supprimer et créer des statistiques. Pour plus d’informations, consultez sys.sp_create_openrowset_statistics et sys.sp_drop_openrowset_statistics.
La procédure stockée sys.sp_drop_openrowset_statistics est utilisée pour supprimer des statistiques :
sys.sp_drop_openrowset_statistics [ @stmt = ] N'statement_text'
Remarque
Les autorisations suivantes sont requises pour exécuter sp_create_openrowset_statistics et sp_drop_openrowset_statistics: ADMINISTRATION DES OPÉRATIONS EN BLOC ou ADMINISTRATION DES OPÉRATIONS EN BLOC DE BASE DE DONNÉES.
Arguments : [ @stmt = ] N’statement_text' : spécifie la même instruction Transact-SQL utilisée lors de la création des statistiques.
Pour mettre à jour les statistiques de la colonne année dans le jeu de données, qui est basée sur le population.csv fichier, vous devez supprimer et créer des statistiques :
EXEC sys.sp_drop_openrowset_statistics N'SELECT payment_type
FROM OPENROWSET(
BULK ''https://sqlondemandstorage.blob.core.windows.net/parquet/taxi/year=2018/month=6/*.parquet'',
FORMAT = ''PARQUET''
) AS [nyc]
TABLESAMPLE(5 PERCENT)
'
GO
/* make sure you have credentials for storage account access created
IF EXISTS (SELECT * FROM sys.credentials WHERE name = 'https://azureopendatastorage.blob.core.windows.net/censusdatacontainer')
DROP CREDENTIAL [https://azureopendatastorage.blob.core.windows.net/censusdatacontainer]
GO
CREATE CREDENTIAL [https://azureopendatastorage.blob.core.windows.net/censusdatacontainer]
WITH IDENTITY='SHARED ACCESS SIGNATURE',
SECRET = ''
GO
*/
EXEC sys.sp_create_openrowset_statistics N'SELECT payment_type
FROM OPENROWSET(
BULK ''https://sqlondemandstorage.blob.core.windows.net/parquet/taxi/year=2018/month=6/*.parquet'',
FORMAT = ''PARQUET''
) AS [nyc]
TABLESAMPLE(5 PERCENT)
'
Exemples : Créer des statistiques pour la colonne de table externe
Les exemples suivants vous montrent comment utiliser différentes options pour créer des statistiques. Les options que vous utilisez pour chaque colonne dépendent des caractéristiques de vos données et de la façon dont la colonne sera utilisée dans les requêtes.
Remarque
Vous ne pouvez créer des statistiques à une seule colonne qu’à ce stade.
Pour créer des statistiques sur une colonne, indiquez le nom de l’objet de statistiques ainsi que celui de la colonne.
CREATE STATISTICS statistics_name
ON { external_table } ( column )
WITH
{ FULLSCAN
| [ SAMPLE number PERCENT ] }
, { NORECOMPUTE }
Arguments : external_table spécifie la table externe sur laquelle les statistiques doivent être créées.
FULLSCAN calcule les statistiques en analysant toutes les lignes. FULLSCAN et SAMPLE 100 PERCENT ont les mêmes résultats. FULLSCAN ne peut pas être utilisé avec l'option SAMPLE.
SAMPLE number PERCENT Indique le pourcentage approximatif ou le nombre de lignes de la table ou de la vue indexée que l'optimiseur de requêtes utilise pour créer des statistiques. Le nombre peut être compris entre 0 et 100.
SAMPLE ne peut pas être utilisé avec l'option FULLSCAN.
Remarque
L’échantillonnage CSV ne fonctionne pas si vous utilisez l’analyseur version 1.0, seul FULLSCAN est pris en charge pour CSV avec l’analyseur version 1.0.
Créer des statistiques sur plusieurs colonnes en examinant chaque ligne
CREATE STATISTICS sState
on census_external_table (STATENAME)
WITH FULLSCAN, NORECOMPUTE
Créer des statistiques sur une colonne en spécifiant la taille de l’échantillon
-- following sample creates statistics with sampling 5%
CREATE STATISTICS sState
on census_external_table (STATENAME)
WITH SAMPLE 5 percent, NORECOMPUTE
Exemples : Mettre à jour les statistiques
Pour mettre à jour les statistiques, vous devez supprimer et créer des statistiques. Tout d’abord, supprimez les statistiques :
DROP STATISTICS census_external_table.sState
Et créez des statistiques :
CREATE STATISTICS sState
on census_external_table (STATENAME)
WITH FULLSCAN, NORECOMPUTE
Métadonnées de statistiques
Vous pouvez utiliser plusieurs fonctions et vues système pour rechercher des informations sur des statistiques. Par exemple, vous pouvez voir si un objet de statistiques peut être obsolète à l’aide de la fonction STATS_DATE(). STATS_DATE() vous permet de voir quand des statistiques ont été créées ou mises à jour pour la dernière fois.
Remarque
Les métadonnées de statistiques sont disponibles uniquement pour les colonnes de table externe. Les métadonnées de statistiques ne sont pas disponibles pour les colonnes OPENROWSET.
Vues de catalogue des statistiques
Ces vues système fournissent des informations sur les statistiques :
| Affichage catalogue | Descriptif |
|---|---|
| sys.columns | Une ligne pour chaque colonne. |
| sys.objects | Une ligne pour chaque objet de la base de données. |
| sys.schemas | Une ligne pour chaque schéma de la base de données. |
| sys.stats | Une ligne pour chaque objet de statistiques. |
| sys.stats_columns | Une ligne pour chaque colonne de l’objet de statistiques. Paramètre lié à l’élément « sys.columns ». |
| sys.tables | Une ligne pour chaque table (inclut des tables externes). |
| sys.table_types | Une ligne pour chaque type de données. |
Fonctions système relatives aux statistiques
Ces fonctions système sont utiles lorsque vous gérez des statistiques :
| Fonction système | Descriptif |
|---|---|
| STATS_DATE | Date de la dernière mise à jour de l’objet de statistiques. |
Combiner des fonctions et des colonnes de statistiques en une seule vue
Cette vue regroupe les colonnes qui concernent les statistiques et les résultats de la fonction STATS_DATE().
CREATE VIEW dbo.vstats_columns
AS
SELECT
sm.[name] AS [schema_name]
, tb.[name] AS [table_name]
, st.[name] AS [stats_name]
, st.[filter_definition] AS [stats_filter_definition]
, st.[has_filter] AS [stats_is_filtered]
, STATS_DATE(st.[object_id],st.[stats_id])
AS [stats_last_updated_date]
, co.[name] AS [stats_column_name]
, ty.[name] AS [column_type]
, co.[max_length] AS [column_max_length]
, co.[precision] AS [column_precision]
, co.[scale] AS [column_scale]
, co.[is_nullable] AS [column_is_nullable]
, co.[collation_name] AS [column_collation_name]
, QUOTENAME(sm.[name])+'.'+QUOTENAME(tb.[name])
AS two_part_name
, QUOTENAME(DB_NAME())+'.'+QUOTENAME(sm.[name])+'.'+QUOTENAME(tb.[name])
AS three_part_name
FROM sys.objects AS ob
JOIN sys.stats AS st ON ob.[object_id] = st.[object_id]
JOIN sys.stats_columns AS sc ON st.[stats_id] = sc.[stats_id]
AND st.[object_id] = sc.[object_id]
JOIN sys.columns AS co ON sc.[column_id] = co.[column_id]
AND sc.[object_id] = co.[object_id]
JOIN sys.types AS ty ON co.[user_type_id] = ty.[user_type_id]
JOIN sys.tables AS tb ON co.[object_id] = tb.[object_id]
JOIN sys.schemas AS sm ON tb.[schema_id] = sm.[schema_id]
WHERE st.[user_created] = 1
;
Étapes suivantes
Pour améliorer davantage les performances des requêtes pour le pool SQL dédié, consultez Surveiller votre charge de travail et les meilleures pratiques pour le pool SQL dédié.
Pour améliorer davantage les performances des requêtes pour le pool SQL serverless, consultez les meilleures pratiques pour le pool SQL serverless.