CREATE STATISTICS (Transact-SQL)

S’applique à : SQL Server base de données Azure SQL Azure SQL Managed Instance Azure Synapse Analytics Point de terminaison analytique SQL dans Microsoft Fabric Entrepôt dans Microsoft Fabric

Crée des statistiques d’optimisation de requête sur une ou plusieurs colonnes d’une table, d’une vue indexée ou d’une table externe. Pour la plupart des requêtes, l’optimiseur de requête génère déjà les statistiques nécessaires pour un plan de requête de haute qualité ; dans quelques cas, vous devez créer des statistiques supplémentaires avec CREATE STATISTICS ou modifier la conception de requête pour améliorer les performances des requêtes.

Pour plus d’informations, consultez Statistiques.

Conventions de la syntaxe Transact-SQL

Syntaxe

Syntaxe pour SQL Server, Azure SQL Database et Azure SQL Managed Instance.

-- Create statistics on an external table
CREATE STATISTICS statistics_name
ON { table_or_indexed_view_name } ( column [ , ...n ] )
    [ WITH FULLSCAN ] ;

-- Create statistics on a regular table or indexed view
CREATE STATISTICS statistics_name
ON { table_or_indexed_view_name } ( column [ , ...n ] )
    [ WHERE <filter_predicate> ]
    [ WITH
        [ FULLSCAN
            [ [ , ] PERSIST_SAMPLE_PERCENT = { ON | OFF } ]
          | SAMPLE number { PERCENT | ROWS }
            [ [ , ] PERSIST_SAMPLE_PERCENT = { ON | OFF } ]
          | <update_stats_stream_option> [ , ...n ]
        [ [ , ] NORECOMPUTE ]
        [ [ , ] INCREMENTAL = { ON | OFF } ]
        [ [ , ] MAXDOP = max_degree_of_parallelism ]
        [ [ , ] AUTO_DROP = { ON | OFF } ]
        ]
    ];

<filter_predicate> ::=
    <conjunct> [ AND <conjunct> ]

<conjunct> ::=
    <disjunct> | <comparison>

<disjunct> ::=
        column_name IN (constant , ...)

<comparison> ::=
        column_name <comparison_op> constant

<comparison_op> ::=
    IS | IS NOT | = | <> | != | > | >= | !> | < | <= | !<

<update_stats_stream_option> ::=
    [ STATS_STREAM = stats_stream ]
    [ ROWCOUNT = numeric_constant ]
    [ PAGECOUNT = numeric_constant ]

Syntaxe pour Azure Synapse Analytics and Analytics Platform System (PDW).

CREATE STATISTICS statistics_name
    ON { database_name.schema_name.table_name | schema_name.table_name | table_name }
    ( column_name  [ , ...n ] )
    [ WHERE <filter_predicate> ]
    [ WITH {
           FULLSCAN
           | SAMPLE number PERCENT
      }
    ]
[ ; ]

<filter_predicate> ::=
    <conjunct> [ AND <conjunct> ]

<conjunct> ::=
    <disjunct> | <comparison>

<disjunct> ::=
        column_name IN (constant , ...)

<comparison> ::=
        column_name <comparison_op> constant

<comparison_op> ::=
    IS | IS NOT | = | <> | != | > | >= | !> | < | <= | !<

Syntaxe de Microsoft Fabric.

CREATE STATISTICS statistics_name
    ON { database_name.schema_name.table_name | schema_name.table_name | table_name }
    ( column_name )
    [ WITH {
           FULLSCAN
           | SAMPLE number PERCENT
      }
    ]
[ ; ]

Arguments

statistics_name

Nom des statistiques à créer.

table_or_indexed_view_name

Nom de la table, de la vue indexée ou de la table externe sur laquelle créer les statistiques. Pour créer des statistiques sur une autre base de données, spécifiez un nom de table qualifié.

column [ ,...n]

Une ou plusieurs colonnes à inclure dans les statistiques. Les colonnes doivent être spécifiées par ordre de priorité de gauche à droite. Seule la première colonne est utilisée pour la création de l’histogramme. Toutes les colonnes sont utilisées pour les statistiques de corrélation entre les colonnes appelées densités.

Vous pouvez indiquer comme base de calcul des statistiques toute colonne pouvant être spécifiée en tant que colonne de clé d'index, sauf pour les exceptions suivantes :

• Les colonnes xml, de texte intégral et FILESTREAM ne peuvent pas être spécifiées.

• Les colonnes calculées ne peuvent être spécifiées que si les ARITHABORT paramètres de base de données sont QUOTED_IDENTIFIER ON.

• Toute colonne de type CLR définie par l'utilisateur peut être spécifiée si son type prend en charge l'ordre de tri binaire. Les colonnes calculées définies en tant qu'appels à des méthodes d'une colonne de type défini par l'utilisateur peuvent être précisées si les méthodes en question sont marquées comme étant déterministes.

WHERE <filter_predicate>

Spécifie une expression permettant de sélectionner un sous-ensemble des lignes à inclure lors de la création de l'objet de statistiques. Les statistiques créées avec un prédicat de filtre sont appelées des statistiques filtrées. Le prédicat de filtre utilise une logique de comparaison simple et ne peut pas référencer une colonne calculée, une colonne UDT, une colonne de type de données spatiales ou une colonne de type de données hierarchyID . Les comparaisons utilisant NULL des littéraux ne sont pas autorisées avec les opérateurs de comparaison. L’utilisation des opérateurs IS NULL et IS NOT NULL est préférable.

Voici quelques exemples de prédicats de filtre pour la table Production.BillOfMaterials :

• WHERE StartDate > '20000101' AND EndDate <= '20000630'
• WHERE ComponentID IN (533, 324, 753)
• WHERE StartDate IN ('20000404', '20000905') AND EndDate IS NOT NULL

Pour plus d’informations sur les prédicats de filtre, consultez Créer des index filtrés.

FULLSCAN

S’applique à : SQL Server 2016 (13.x) SP 1 CU 4, SQL Server 2017 (14.x) CU 1 et versions ultérieures

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 .

Si elle est omise, SQL Server utilise l’échantillonnage pour créer les statistiques, et détermine la taille d’échantillon nécessaire pour créer un plan de requête de haute qualité.

Dans Warehouse dans Microsoft Fabric, seules les statistiques à colonne FULLSCAN unique et à colonne unique SAMPLEsont prises en charge. Quand aucune option n’est incluse, SAMPLE les statistiques sont créées.

SAMPLE number { PERCENT | ROWS }

Spécifie le pourcentage approximatif, ou le nombre de lignes, dans la table ou l’affichage indexé de l’optimiseur de requête à utiliser lorsqu’il crée des statistiques. Pour PERCENT, le nombre peut être compris entre 0 et 100 et pour ROWS, le nombre peut être compris entre 0 et le nombre total de lignes. Le pourcentage ou nombre de lignes réel échantillonné par l'optimiseur de requête peut ne pas correspondre au pourcentage ou nombre spécifié. Par exemple, l'optimiseur de requête analyse toutes les lignes d'une page de données.

SAMPLE est utile pour les cas spéciaux dans lesquels le plan de requête, basé sur l’échantillonnage par défaut, n’est pas optimal. Dans la plupart des cas, il n’est pas nécessaire de spécifier SAMPLE , car l’optimiseur de requête utilise déjà l’échantillonnage et détermine la taille d’échantillon statistiquement significative par défaut, comme nécessaire pour créer des plans de requête de haute qualité.

SAMPLE ne peut pas être utilisé avec l’option FULLSCAN. Quand SAMPLE ou FULLSCAN n’est pas spécifié, l’optimiseur de requête utilise des données échantillonné et calcule par défaut la taille de l’échantillon.

Nous recommandons de ne pas spécifier 0 PERCENT ou 0 ROWS. Quand 0 PERCENT ou 0 ROWS est spécifié, l’objet de statistiques est créé, mais ne contient pas de données de statistiques.

Dans Warehouse dans Microsoft Fabric, seules les statistiques à colonne FULLSCAN unique et à colonne unique SAMPLEsont prises en charge. Quand aucune option n’est incluse, FULLSCAN les statistiques sont créées.

PERSIST_SAMPLE_PERCENT = { ON | OFF }

Lorsque ON, les statistiques conservent le pourcentage d’échantillonnage de création pour les mises à jour suivantes qui ne spécifient pas explicitement un pourcentage d’échantillonnage. Quand OFF, le pourcentage d’échantillonnage des statistiques est réinitialisé à l’échantillonnage par défaut dans les mises à jour suivantes qui ne spécifient pas explicitement un pourcentage d’échantillonnage. Par défaut, il s’agit de OFF.

STATS_STREAM = flux_statistiques

Identifié à titre d'information uniquement. Non pris en charge. La compatibilité future n'est pas garantie.

NORECOMPUTE

Désactivez l’option de mise à jour automatique des statistiques, AUTO_STATISTICS_UPDATEpour statistics_name. Si cette option est spécifiée, l’optimiseur de requête effectue les mises à jour des statistiques en cours d’exécution pour statistics_name et désactive les mises à jour ultérieures.

Pour réactiver les mises à jour des statistiques, supprimez les statistiques avec DROP STATISTICS , puis exécutez CREATE STATISTICS sans l’option NORECOMPUTE .

Pour plus d’informations sur l’option AUTO_STATISTICS_UPDATE , consultez les options ALTER DATABASE SET. Pour plus d’informations sur la désactivation et la réactivation des mises à jour des statistiques, consultez Statistiques.

INCREMENTAL = { ON | OFF }

S’applique à : SQL Server 2014 (12.x) et ultérieur

Quand ON, les statistiques créées sont par statistiques de partition. Quand OFF, les statistiques sont combinées pour toutes les partitions. Par défaut, il s’agit de OFF.

Si les statistiques par partition ne sont pas prises en charge, une erreur est générée. Les statistiques incrémentielles ne sont pas prises en charge pour les types de statistique suivants :

• Statistiques créées à partir d’index qui n’ont pas d’alignement de partition avec la table de base.
• statistiques créées sur les bases de données secondaires lisibles Always On ;
• statistiques créées sur les bases de données en lecture seule ;
• statistiques créées sur les index filtrés ;
• statistiques créées sur les vues ;
• statistiques créées sur les tables internes ;
• Statistiques créées avec les index spatiaux ou les index XML.

MAXDOP = max_degree_of_parallelism

S’applique à : SQL Server 2016 (13.x) SP 2, SQL Server 2017 (14.x) CU 3 et versions ultérieures

Remplace l’option de configuration max degree of parallelism pendant l’opération statistique. Pour plus d’informations, consultez Configurer le degré maximal de parallélisme (option de configuration de serveur). Permet MAXDOP de limiter le nombre de processeurs utilisés dans une exécution de plan parallèle. Le nombre maximal de processeurs est égal à 64.

max_degree_of_parallelism peut avoir la valeur :

• 1: supprime la génération de plan parallèle.
• >1: limite le nombre maximal de processeurs utilisés dans une opération d’index parallèle au nombre spécifié.
• 0 (par défaut) : utilise le nombre réel de processeurs ou moins en fonction de la charge de travail système actuelle.

update_stats_stream_option

Identifié à titre d'information uniquement. Non pris en charge. La compatibilité future n'est pas garantie.

AUTO_DROP = { ON | OFF }

S’applique à : SQL Server 2022 (16.x) et versions ultérieures, et Azure SQL Database, Azure SQL Managed Instance

Avant SQL Server 2022 (16.x), si les statistiques sont créées manuellement par un utilisateur ou un outil tiers sur une base de données utilisateur, ces objets de statistiques peuvent bloquer ou interférer avec les modifications de schéma souhaitées par le client.

À compter de SQL Server 2022 (16.x), l’option AUTO_DROP est activée par défaut sur toutes les bases de données nouvelles et migrées. La AUTO_DROP propriété permet la création d’objets de statistiques dans un mode de sorte qu’une modification de schéma suivante n’est pas bloquée par l’objet statistique, mais que les statistiques sont supprimées si nécessaire. De cette façon, créez manuellement des statistiques avec activé, AUTO_DROP comme les statistiques autocréées.

Pour plus d’informations, consultez Option AUTO_DROP.

Autorisations

Nécessite l’une de ces autorisations :

• ALTER TABLE
• L’utilisateur est le propriétaire de la table
• L’appartenance au rôle de base de données fixe db_ddladmin

Remarques

SQL Server peut utiliser tempdb pour trier les lignes échantillonnées avant de générer des statistiques.

Statistiques pour les tables externes

Lors de la création de statistiques de table externe, SQL Server importe la table externe dans une table SQL Server temporaire, puis crée les statistiques. Pour les échantillons de statistiques, seules les lignes échantillonnées sont importées. Si vous avez une table externe volumineuse, il est plus rapide d’utiliser l’échantillonnage par défaut au lieu de l’option d’analyse complète.

Lorsque la table externe utilise DELIMITEDTEXT, CSV, PARQUET ou DELTA en tant que types de données, les tables externes prennent uniquement en charge les statistiques d’une colonne par commande CREATE STATISTICS.

Statistiques avec une condition filtrée

Les statistiques filtrées peuvent améliorer les performances des requêtes qui effectuent des sélections dans des sous-ensembles bien définis de données. Elles utilisent un prédicat de filtre dans la clause WHERE pour sélectionner le sous-ensemble de données qui est inclus dans les statistiques.

Quand utiliser CREATE STATISTICS

Pour plus d’informations sur les cas où utiliser CREATE STATISTICS, consultez Statistiques.

Référencer les dépendances pour les statistiques filtrées

La vue de catalogue sys.sql_expression_dependencies suit chaque colonne du prédicat de statistiques filtrées en tant que dépendance de référence. Tenez compte des opérations que vous effectuez sur les colonnes de table avant de créer des statistiques filtrées. Vous ne pouvez pas supprimer, renommer ou modifier la définition d’une colonne de table définie dans un prédicat de statistiques filtrés.

Limites

• La mise à jour des statistiques n’est pas prise en charge sur les tables externes. Pour mettre à jour des statistiques sur une table externe, supprimez et recréez les statistiques.
• Vous pouvez afficher jusqu’à 64 colonnes par objet de statistiques.
• L’option MAXDOP n’est pas compatible avec STATS_STREAM, ROWCOUNTet PAGECOUNT les options.
• L’option MAXDOP est limitée par le paramètre MAX_DOP du groupe de charge de travail de Resource Governor, s’il est utilisé.
• CREATE et DROP STATISTICS sur les tables externes ne sont pas prises en charge dans Azure SQL Database.

Exemples

Les exemples de code Transact-SQL de cet article sont fondés sur l’échantillon de base de données AdventureWorks2022 ou AdventureWorksDW2022 fourni, que vous pouvez télécharger à partir de la page d’accueil Échantillons et projets communautaires Microsoft SQL Server.

R : Utiliser CREATE STATISTICS avec SAMPLE nombre PERCENT

L’exemple suivant crée les statistiques ContactMail1, à l’aide d’un exemple aléatoire de 5 % des colonnes BusinessEntityID et EmailPromotion de la table Person de la base de données AdventureWorks2022.

CREATE STATISTICS ContactMail1
    ON Person.Person (BusinessEntityID, EmailPromotion)
    WITH SAMPLE 5 PERCENT;

B. Utiliser CREATE STATISTICS avec FULLSCAN et NORECOMPUTE

L'exemple suivant crée les statistiques NamePurchase pour toutes les lignes des colonnes BusinessEntityID et EmailPromotion de la table Person et désactive le recalcul automatique des statistiques.

CREATE STATISTICS NamePurchase
    ON AdventureWorks2022.Person.Person (BusinessEntityID, EmailPromotion)
    WITH FULLSCAN, NORECOMPUTE;

C. Utiliser CREATE STATISTICS pour créer des statistiques filtrées

L'exemple suivant crée les statistiques filtrées ContactPromotion1. Le Moteur de base de données échantillonne 50 % des données, puis sélectionne toutes les lignes pour lesquelles EmailPromotion est égal à 2.

CREATE STATISTICS ContactPromotion1
    ON Person.Person (BusinessEntityID, LastName, EmailPromotion)
WHERE EmailPromotion = 2
WITH SAMPLE 50 PERCENT;
GO

D. Créer des statistiques sur une table externe

La seule décision que vous devez prendre quand vous créez des statistiques sur une table externe, en plus de fournir la liste des colonnes, est de savoir si vous allez créer les statistiques en échantillonnant les lignes ou en analysant toutes les lignes. CREATE et DROP STATISTICS sur les tables externes ne sont pas prises en charge dans Azure SQL Database.

Étant donné que SQL Server importe les données de la table externe vers une table temporaire pour créer les statistiques, l’option d’analyse complète prend beaucoup plus de temps. Pour une table volumineuse, la méthode d’échantillonnage par défaut est généralement suffisante.

--Create statistics on an external table and use default sampling.
CREATE STATISTICS CustomerStats1 ON DimCustomer (CustomerKey, EmailAddress);

--Create statistics on an external table and scan all the rows
CREATE STATISTICS CustomerStats1 ON DimCustomer (CustomerKey, EmailAddress) WITH FULLSCAN;

E. Utiliser CREATE STATISTICS avec FULLSCAN et PERSIST_SAMPLE_PERCENT

L’exemple suivant crée les NamePurchase statistiques de toutes les lignes de la BusinessEntityID Person table et EmailPromotion définit un pourcentage d’échantillonnage de 100 % pour toutes les mises à jour suivantes qui ne spécifient pas explicitement un pourcentage d’échantillonnage.

CREATE STATISTICS NamePurchase
    ON AdventureWorks2022.Person.Person (BusinessEntityID, EmailPromotion)
    WITH FULLSCAN, PERSIST_SAMPLE_PERCENT = ON;

Exemples avec la base de données AdventureWorksDW

F. Créer des statistiques sur deux colonnes

L’exemple suivant crée les statistiques CustomerStats1, en fonction des colonnes CustomerKey et EmailAddress de la table DimCustomer. Les statistiques sont créées d’après un échantillon statistiquement significatif des lignes dans la table Customer.

CREATE STATISTICS CustomerStats1 ON DimCustomer (CustomerKey, EmailAddress);

G. Créer des statistiques à l’aide d’une analyse complète

L’exemple suivant crée les statistiques CustomerStatsFullScan, en fonction de l’analyse de toutes les lignes dans la table DimCustomer.

CREATE STATISTICS CustomerStatsFullScan
ON DimCustomer (CustomerKey, EmailAddress) WITH FULLSCAN;

H. Créer des statistiques en spécifiant le pourcentage d’échantillonnage

L’exemple suivant crée les statistiques CustomerStatsSampleScan, en fonction de l’analyse de 50 pour cent des lignes dans la table DimCustomer.

CREATE STATISTICS CustomerStatsSampleScan
ON DimCustomer (CustomerKey, EmailAddress) WITH SAMPLE 50 PERCENT;

I. Utiliser CREATE STATISTICS avec AUTO_DROP

Pour utiliser des statistiques avec suppression automatique, ajoutez simplement le code suivant à la clause « WITH » de la création ou de la mise à jour des statistiques.

CREATE STATISTICS CustomerStats1 ON DimCustomer (CustomerKey, EmailAddress) WITH AUTO_DROP = ON

Pour évaluer le paramètre de suppression automatique sur les statistiques existantes, utilisez la colonne auto_drop de sys.stats :

SELECT object_id, [name], auto_drop
FROM sys.stats;

Contenu connexe

• Statistiques
• Statistiques dans l’entreposage de données Fabric
• UPDATE STATISTICS (Transact-SQL)
• sp_updatestats (Transact-SQL)
• DBCC SHOW_STATISTICS (Transact-SQL)
• DROP STATISTICS (Transact-SQL)
• sys.stats (Transact-SQL)
• sys.stats_columns (Transact-SQL)