UPDATE STATISTICS (Transact-SQL)

  • Article

S’applique à :SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)Point de terminaison analytique SQL dans Microsoft FabricEntrepôt dans Microsoft Fabric

Met à jour les statistiques d'optimisation des requêtes d'une table ou d'une vue indexée. Par défaut, l’optimiseur de requête met à jour les statistiques en fonction des besoins afin d’améliorer le plan de requête. Dans certains cas, vous pouvez optimiser les performances des requêtes en utilisant UPDATE STATISTICS ou la procédure stockée sp_updatestats pour mettre à jour les statistiques plus fréquemment qu’avec les mises à jour par défaut.

La mise à jour des statistiques est l'assurance que les requêtes sont compilées avec des statistiques à jour. La mise à jour des statistiques par le biais de n’importe quel processus peut entraîner la recompilation automatique des plans de requête. Nous vous recommandons de ne pas mettre à jour les statistiques de façon trop fréquente pour ne pas dégrader les performances. En effet, vous devez trouver un compromis entre l’amélioration des plans de requête et le temps nécessaire à la recompilation des requêtes. Ce compromis peut varier en fonction de votre application. UPDATE STATISTICS peut utiliser tempdb pour trier l’échantillon de lignes afin de générer des statistiques.

Notes

Pour plus d’informations sur les statistiques dans Microsoft Fabric, consultez Statistiques dans Microsoft Fabric.

Conventions de la syntaxe Transact-SQL

Syntaxe

-- Syntax for SQL Server and Azure SQL Database  
  
UPDATE STATISTICS table_or_indexed_view_name   
    [   
        {   
            { index_or_statistics__name }  
          | ( { index_or_statistics_name } [ ,...n ] )   
                }  
    ]   
    [    WITH   
        [  
            FULLSCAN   
              [ [ , ] PERSIST_SAMPLE_PERCENT = { ON | OFF } ]    
            | SAMPLE number { PERCENT | ROWS }   
              [ [ , ] PERSIST_SAMPLE_PERCENT = { ON | OFF } ]    
            | RESAMPLE   
              [ ON PARTITIONS ( { <partition_number> | <range> } [, ...n] ) ]  
            | <update_stats_stream_option> [ ,...n ]  
        ]   
        [ [ , ] [ ALL | COLUMNS | INDEX ]   
        [ [ , ] NORECOMPUTE ]   
        [ [ , ] INCREMENTAL = { ON | OFF } ] 
        [ [ , ] MAXDOP = max_degree_of_parallelism ]
        [ [ , ] AUTO_DROP = { ON | OFF } ]
    ] ;  
  
<update_stats_stream_option> ::=  
    [ STATS_STREAM = stats_stream ]  
    [ ROWCOUNT = numeric_constant ]  
    [ PAGECOUNT = numeric_contant ]  

-- Syntax for Azure Synapse Analytics and Parallel Data Warehouse 
  
UPDATE STATISTICS [ schema_name . ] table_name   
    [ ( { statistics_name | index_name } ) ]  
    [ WITH   
       {  
              FULLSCAN   
            | SAMPLE number PERCENT   
            | RESAMPLE   
        }  
    ]  
[;]  

-- Syntax for Microsoft Fabric

UPDATE STATISTICS [ schema_name . ] table_name   
    [ ( { statistics_name } ) ]  
    [ WITH   
       {  
              FULLSCAN   
            | SAMPLE number PERCENT   
        }  
    ]  
[;]

Remarque

Pour afficher la syntaxe Transact-SQL pour SQL Server 2014 (12.x) et versions antérieures, consultez la Documentation sur les versions antérieures.

Remarque

Cette syntaxe n’est pas prise en charge par le pool SQL serverless dans Azure Synapse Analytics.

Arguments

table_or_indexed_view_name

Nom de la table ou de la vue indexée qui contient l’objet de statistique.

index_or_statistics_name ou statistics_name | index_name ou statistics_name

Nom de l'index dont les statistiques doivent être mises à jour ou nom des statistiques à mettre à jour. Si index_or_statistics_name ou statistics_name n’est pas spécifié, l’optimiseur de requête met à jour toutes les statistiques de la table ou de la vue indexée. Cela inclut les statistiques créées à l'aide de l'instruction CREATE STATISTICS, les statistiques de colonnes uniques créées lorsque AUTO_CREATE_STATISTICS a la valeur ON, ainsi que les statistiques créées pour les index.

Pour en savoir plus sur l’option AUTO_CREATE_STATISTICS, reportez-vous à Options ALTER DATABASE SET. Pour afficher tous les index d’une table ou d’une vue, vous pouvez utiliser sp_helpindex.

FULLSCAN

Calcule les statistiques en analysant toutes les lignes dans la table ou vue indexée. FULLSCAN et SAMPLE 100 PERCENT ont les mêmes résultats. FULLSCAN ne peut pas être utilisé avec l'option SAMPLE.

SAMPLE number { PERCENT | ROWS }

Spécifie le pourcentage ou nombre de lignes approximatif dans la table ou vue indexée à utiliser par l'optimiseur de requête lors de la mise à jour des statistiques. Pour PERCENT, number peut être compris entre 0 et 100, et pour ROWS, number 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 dans les cas particuliers où le plan de requête, basé sur l’échantillonnage par défaut, n’est pas optimal. Dans la plupart des situations, il n’est pas nécessaire de spécifier SAMPLE, car l’optimiseur de requête utilise l’échantillonnage et détermine la taille d’échantillon statistiquement significative par défaut, ce qui permet de créer des plans de requête de haute qualité.

Remarque

Dans SQL Server 2016 (13.x) lors de l’utilisation du niveau de compatibilité de base de données 130, l’échantillonnage des données pour générer des statistiques est effectué en parallèle pour améliorer les performances de la collecte de statistiques. L’optimiseur de requête utilise des exemples de statistiques parallèles chaque fois qu’une taille de table dépasse un certain seuil. À compter de SQL Server 2017 (14.x), quel que soit le niveau de compatibilité de la base de données, le comportement a été remplacé par l’utilisation d’une analyse série afin d’éviter les éventuels problèmes de performances avec des attentes excessives de LATCH. Le reste du plan de requête lors de la mise à jour des statistiques conserve l’exécution parallèle si elle est qualifiée.

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

Il est déconseillé de spécifier 0 PERCENT ou 0 ROWS. Quand 0 PERCENT ou ROWS est spécifié, l’objet de statistiques est mis à jour mais ne contient pas de données de statistiques.

Pour la plupart des charges de travail, une analyse complète n’est pas nécessaire. L’échantillonnage par défaut est suffisant. Toutefois, certaines charges de travail qui sont sensibles aux distributions de données à grande variation peuvent nécessiter une taille d’échantillon plus élevée, voire une analyse complète. Bien que les estimations soient plus précises avec une analyse complète qu'avec une analyse par échantillonnage, les régimes complexes peuvent ne pas en bénéficier de manière significative.

Pour en savoir plus, reportez-vous à Composants et concepts des statistiques.

RESAMPLE

Met à jour chaque statistique à l'aide de son taux d'échantillonnage le plus récent.

L'utilisation de RESAMPLE peut entraîner une analyse complète de la table. Par exemple, les statistiques relatives aux index utilisent une analyse de table complète pour leur taux d'échantillonnage. Si aucune option d'échantillonnage (SAMPLE, FULLSCAN, RESAMPLE) n'est spécifiée, l'optimiseur de requête échantillonne les données et calcule la taille d'échantillon par défaut.

Dans l’entrepôt dans Microsoft Fabric, RESAMPLE n’est pas pris en charge.

PERSIST_SAMPLE_PERCENT = { ON | OFF }

S’applique à : SQL Server 2016 (13.x) Service Pack 1 CU4, SQL Server 2017 (14.x) Service Pack 1, ou SQL Server 2019 (15.x) et versions ultérieures, Azure SQL Database, Azure SQL Managed Instance.

Quand vous spécifiez ON, les statistiques conservent le pourcentage d’échantillonnage défini pour les mises à jour suivantes qui ne spécifient pas explicitement de pourcentage d’échantillonnage. Quand vous spécifiez OFF, le pourcentage d’échantillonnage des statistiques est réinitialisé en fonction de l’échantillonnage par défaut dans les mises à jour suivantes qui ne spécifient pas explicitement de pourcentage d’échantillonnage. La valeur par défaut est OFF.

DBCC SHOW_STATISTICS et sys.dm_db_stats_properties exposent la valeur de pourcentage d’échantillonnage persistante pour la statistique sélectionnée.

Si l’option AUTO_UPDATE_STATISTICS est exécutée, elle utilise le pourcentage d’échantillonnage persistant s’il est disponible, ou le pourcentage d’échantillonnage par défaut dans le cas contraire. Le comportement de RESAMPLE n’est pas affecté par cette option.

Si la table est tronquée, toutes les statistiques générées sur le segment de mémoire (HoBT) tronqué sont rétablies à l’aide du pourcentage d’échantillonnage par défaut.

Remarque

Dans SQL Server, lors de la reconstruction d’un index qui contenait précédemment des statistiques mises à jour avec PERSIST_SAMPLE_PERCENT, le pourcentage d’échantillonnage persistant est réinitialisé à la valeur par défaut. À partir de SQL Server 2016 (13.x) SP2 CU17, SQL Server 2017 (14.x) CU26 et SQL Server 2019 (15.x) CU10, le pourcentage d'échantillon persistant est conservé même lors de la reconstruction d'un index.

ON PARTITIONS ( { <partition_number> | <range> } [, ...n] ) ]

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

Impose le recalcul des statistiques au niveau feuille couvrant les partitions spécifiées dans la clause ON PARTITIONS, puis leur fusion afin de générer des statistiques globales. WITH RESAMPLE est nécessaire, car les statistiques de partition créées avec différents taux d’échantillonnage ne peuvent pas être fusionnées ensemble.

ALL | COLUMNS | INDEX

Met à jour toutes les statistiques existantes, les statistiques créées sur une ou plusieurs colonnes, ou les statistiques créées pour les index. Si aucune option n'est spécifiée, l'instruction UPDATE STATISTICS met à jour toutes les statistiques de la table ou vue indexée.

NORECOMPUTE

Désactive l'option de mise à jour automatique des statistiques, AUTO_UPDATE_STATISTICS, pour les statistiques spécifiées. Si cette option est spécifiée, l'optimiseur de requête effectue cette mise à jour des statistiques et désactive les mises à jour ultérieures.

Pour réactiver le comportement de l’option AUTO_UPDATE_STATISTICS, réexécutez UPDATE STATISTICS sans l’option NORECOMPUTE ou exécutez sp_autostats.

Avertissement

L'utilisation de cette option peut produire des plans de requête non optimaux. Nous recommandons d'utiliser cette option avec parcimonie et uniquement par un administrateur système qualifié.

Pour en savoir plus sur l’option AUTO_UPDATE_STATISTICS, reportez-vous à Options ALTER DATABASE SET.

INCREMENTAL = { ON | OFF }

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

Quand la valeur ON est définie, les statistiques sont recréées par partition. Si la valeur est OFF, l’arborescence des statistiques est supprimée et SQL Server recalcule les statistiques. La valeur par défaut est 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 (à compter de SQL Server 2016 (13.x) SP2 et SQL Server 2017 (14.x) CU3).

Remplace l’option de configuration max degree of parallelism pendant la durée de l’opération statistique. Pour plus d’informations, consultez Configurer l’option de configuration du serveur max degree of parallelism. Utilisez MAXDOP pour 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 création de plans parallèles.

>1 Limite le nombre maximal de processeurs utilisés dans une opération statistique parallèle au nombre spécifié ou à un nombre inférieur en fonction de la charge de travail actuelle du système.

0 (par défaut)
Utilise le nombre réel de processeurs ou un nombre de processeurs inférieur en fonction de la charge de travail actuelle du système.

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 plus récentes.

Actuellement, si des statistiques sont créées par un outil tiers sur une base de données client, ces objets de statistiques peuvent bloquer les modifications de schéma souhaitées par le client, ou interférer avec celles-ci.

(SQL Server 2022 (16.x) et versions ultérieures)| Cette fonctionnalité permet de créer des objets de statistiques de façon à ce que les statistiques ne bloquent pas les modifications de schéma, mais qu’elles soient supprimées à la place. De cette façon, les statistiques d’annulation automatique se comportent comme des statistiques créées automatiquement.

Notes

La tentative de définition ou d’annulation de la propriété AUTO_DROP sur des statistiques créées automatiquement peut déclencher des erreurs : les statistiques créées automatiquement utilisent toujours la suppression automatique. Certaines sauvegardes, lorsqu’elles sont restaurées, peuvent présenter une définition incorrecte de cette propriété jusqu’à la prochaine mise à jour (manuelle ou automatique) de l’objet de statistiques. Cependant, les statistiques créées automatique se comportent toujours comme des statistiques annulées automatiquement.

Notes

Quand METTRE À JOUR DES STATISTIQUES

Pour en savoir plus sur les cas où utiliser UPDATE STATISTICS, reportez-vous à Quand mettre à jour les statistiques.

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.
  • L’option MAXDOP n’est pas compatible avec les options STATS_STREAM, ROWCOUNT et PAGECOUNT.
  • 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é.

Mettre à jour toutes les statistiques avec sp_updatestats

Pour en savoir plus sur la mise à jour des statistiques pour toutes les tables définies par l’utilisateur et les tables internes de la base de données, reportez-vous à la procédure stockée sp_updatestats. Par exemple, la commande suivante appelle sp_updatestats pour mettre à jour toutes les statistiques de la base de données.

EXEC sp_updatestats;

Gestion automatique des index et des statistiques

Tirez parti de solutions comme Adaptive Index Defrag pour gérer automatiquement la défragmentation des index et les mises à jour des statistiques pour une ou plusieurs bases de données. Cette procédure choisit automatiquement s’il faut reconstruire ou réorganiser un index en fonction de son niveau de fragmentation, entre autres, et mettre à jour les statistiques avec un seuil linéaire.

Détermination de la dernière mise à jour des statistiques

Pour déterminer la date de la dernière mise à jour des statistiques, utilisez la fonction STATS_DATE .

PDW/Azure Synapse Analytics

La syntaxe suivante n’est pas prise en charge par Analytics Platform System (PDW) / Azure Synapse Analytics :

UPDATE STATISTICS t1 (a,b);   

UPDATE STATISTICS t1 (a) WITH SAMPLE 10 ROWS;  

UPDATE STATISTICS t1 (a) WITH NORECOMPUTE;  

UPDATE STATISTICS t1 (a) WITH INCREMENTAL = ON;  

UPDATE STATISTICS t1 (a) WITH stats_stream = 0x01;

Autorisations

Nécessite l’autorisation ALTER sur la table ou la vue.

Exemples

R. Mettre à jour toutes les statistiques d'une table

L’exemple suivant met à jour toutes les statistiques de la table SalesOrderDetail.

USE AdventureWorks2022;  
GO  
UPDATE STATISTICS Sales.SalesOrderDetail;  
GO

B. Mettre à jour les statistiques d'un index

L'exemple suivant illustre la mise à jour des statistiques pour l'index AK_SalesOrderDetail_rowguid de la table SalesOrderDetail.

USE AdventureWorks2022;  
GO  
UPDATE STATISTICS Sales.SalesOrderDetail AK_SalesOrderDetail_rowguid;  
GO

C. Mettre à jour des statistiques avec un échantillonnage de 50 pour cent

L'exemple suivant crée, puis met à jour les statistiques des colonnes Name et ProductNumber de la table Product.

USE AdventureWorks2022;
GO  
CREATE STATISTICS Products
    ON Production.Product ([Name], ProductNumber)
    WITH SAMPLE 50 PERCENT
-- Time passes. The UPDATE STATISTICS statement is then executed.
UPDATE STATISTICS Production.Product(Products)
    WITH SAMPLE 50 PERCENT;

D. Mettre à jour des statistiques avec FULLSCAN et NORECOMPUTE

L'exemple suivant met à jour les statistiques de Products dans la table Product, force l'analyse complète de toutes les lignes de la table Product et désactive la mise à jour automatique des statistiques pour les statistiques de Products.

USE AdventureWorks2022;  
GO  
UPDATE STATISTICS Production.Product(Products)  
    WITH FULLSCAN, NORECOMPUTE;  
GO

Exemples : Azure Synapse Analytics et Analytics Platform System (PDW)

E. Mettre à jour les statistiques d’une table

L’exemple suivant met à jour les statistiques CustomerStats1 de la table Customer.

UPDATE STATISTICS Customer (CustomerStats1);

F. Mettre à jour des statistiques à l’aide d’une analyse complète

L’exemple suivant met à jour les statistiques CustomerStats1, en fonction de l’analyse de toutes les lignes dans la table Customer.

UPDATE STATISTICS Customer (CustomerStats1) WITH FULLSCAN;

G. Mettre à jour toutes les statistiques d'une table

L’exemple suivant met à jour toutes les statistiques de la table Customer.

UPDATE STATISTICS Customer;

H. Utilisation de 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.

UPDATE STATISTICS Customer (CustomerStats1) WITH AUTO_DROP = ON

Contenu connexe