Statistiques

S’applique à : ouiSQL Server (toutes les versions prises en charge) OuiAzure SQL Database OuiAzure SQL Managed Instance ouiAzure Synapse Analytics OuiParallel Data Warehouse

L'optimiseur de requête utilise des statistiques dans l'optique de créer des plans de requête qui améliorent les performances des requêtes. Pour la plupart des requêtes, l'optimiseur de requête génère déjà les statistiques utiles à un plan de requête de haute qualité ; dans certains cas, vous devez créer des statistiques supplémentaires ou modifier la conception des requêtes pour obtenir des résultats optimaux. Cet article traite des concepts de statistiques et fournit des instructions pour vous permettre d'utiliser efficacement les statistiques d'optimisation de requête.

Composants et concepts

Statistiques

Les statistiques utilisées dans le cadre de l’optimisation des requêtes sont des objets BLOB (Binary Large Object) qui contiennent des informations statistiques sur la distribution des valeurs dans une ou plusieurs colonnes d’une table ou d’une vue indexée. L'optimiseur de requête utilise ces statistiques pour estimer le nombre de lignes, également appelé cardinalité, dans le résultat de la requête. Ces estimations de cardinalité permettent à l’optimiseur de requête de créer un plan de requête de haute qualité. Par exemple, selon vos prédicats, l'optimiseur de requête peut utiliser des estimations de cardinalité pour choisir l'opérateur de recherche d'index plutôt que l'opérateur d'analyse d'index, plus vorace en ressources, contribuant ainsi à améliorer les performances des requêtes.

Chaque objet de statistiques est créé dans une liste constituée d’une ou de plusieurs colonnes de table, et comprend un histogramme présentant la distribution des valeurs dans la première colonne. Les objets de statistiques sur plusieurs colonnes stockent également des informations statistiques sur la corrélation des valeurs entre les colonnes. Ces statistiques de corrélation, également appelées densités, sont dérivées du nombre de lignes distinctes de valeurs de colonne.

Histogramme

Un histogramme mesure la fréquence des occurrences de chaque valeur distincte dans un jeu de données. L’optimiseur de requête calcule un histogramme sur les valeurs de colonnes de la première colonne clé de l’objet de statistiques, en sélectionnant les valeurs de colonnes au moyen d’un échantillonnage statistique des lignes ou d’une analyse complète de toutes les lignes dans la table ou la vue. Si l'histogramme est créé à partir d'un jeu de lignes échantillonnées, les totaux stockés pour le nombre de lignes et le nombre de valeurs distinctes sont des estimations et ne doivent pas nécessairement être des nombres entiers.

Notes

Les histogrammes dans SQL Server sont créés pour une seule colonne, en l’occurrence, la première colonne du jeu de colonnes clés de l’objet de statistiques.

Pour créer l’histogramme, l’optimiseur de requête trie les valeurs de colonnes, calcule le nombre de valeurs qui correspondent à chaque valeur de colonne distincte, puis regroupe les valeurs de colonnes dans 200 étapes d’histogramme contiguës au maximum. Chaque étape de l’histogramme inclut une plage de valeurs de colonnes, suivie d’une valeur de colonne de limite supérieure. La plage comprend toutes les valeurs de colonnes possibles entre des valeurs limites, à l'exception des valeurs limites elles-mêmes. La plus basse des valeurs de colonnes triées est la valeur de limite supérieure pour la première étape d'histogramme.

Plus précisément, SQL Server crée l’histogramme à partir du jeu de valeurs de colonnes trié, en trois étapes :

  • Initialisation de l’histogramme : Dans la première étape, une suite de valeurs situées au début du jeu trié est traitée, et jusqu’à 200 valeurs de range_high_key, equal_rows, range_rows et distinct_range_rows sont collectées (range_rows et distinct_range_rows ont toujours une valeur égale à zéro lors de cette étape). La première étape se termine quand toutes les entrées ont été traitées ou quand 200 valeurs ont été trouvées.
  • Analyse avec fusion des compartiments : Chaque valeur supplémentaire issue de la première colonne de la clé de statistiques est traitée selon l’ordre de tri à la deuxième étape. Chaque valeur suivante est ajoutée à la dernière plage ou une nouvelle plage est créée à la fin (ceci est possible, car les valeurs d’entrée sont triées). Si une plage est créée, une paire de plages voisines existantes est réduite en une plage unique. Cette paire de plages est sélectionnée pour minimiser la perte d’informations. Cette méthode utilise un algorithme de différence maximale pour réduire le nombre d’étapes dans l’histogramme, tout en augmentant la différence entre les valeurs limites. Le nombre d’étapes après réduction des plages reste à 200 pendant toute la durée de cette étape.
  • Consolidation de l’histogramme : Dans la troisième étape, d’autres plages peuvent être réduites s’il n’y a pas de perte importante d’informations. Le nombre d'étapes d'histogramme peut être inférieur au nombre de valeurs distinctes, même pour les colonnes comportant moins de 200 points de limite. Par conséquent, même si la colonne contient plus de 200 valeurs uniques, l’histogramme peut comporter moins de 200 étapes. Pour une colonne comprenant uniquement des valeurs uniques, l’histogramme consolidé comporte un minimum de trois étapes.

Notes

Si l’histogramme a été créé à l’aide de l’option SAMPLE plutôt qu’avec l’option FULLSCAN, les valeurs de equal_rows, range_rows, distinct_range_rows et average_range_rows sont estimées, et n’ont donc pas besoin d’être des entiers.

Le diagramme suivant illustre un histogramme avec six étapes : La zone située à gauche de la première valeur limite supérieure représente la première étape.

Histogramme

Pour chaque étape de l’histogramme ci-dessus :

  • La ligne en gras représente la valeur limite supérieure (range_high_key) et le nombre d’occurrences (equal_rows) correspondant.

  • La zone pleine située à gauche de range_high_key représente la plage de valeurs de colonnes et le nombre moyen d’occurrences de chacune des valeurs de colonnes (average_range_rows). Pour la première étape de l’histogramme, la valeur de average_range_rows est toujours égale à 0.

  • Les lignes pointillées représentent les valeurs échantillonnées utilisées pour estimer le nombre total de valeurs distinctes dans la plage (distinct_range_rows) et le nombre total de valeurs dans la plage (range_rows). L’optimiseur de requête utilise range_rows et distinct_range_rows pour calculer average_range_rows et ne stocke pas les valeurs échantillonnées.

Vecteur de densité

La densité correspond aux informations sur le nombre de doublons d’une colonne donnée ou d’une combinaison de colonnes. Elle est calculée ainsi : 1/(nombre de valeurs distinctes). L’optimiseur de requête utilise des densités afin d’améliorer les estimations de cardinalité pour les requêtes qui retournent plusieurs colonnes à partir de la même table ou vue indexée. Lorsque la densité diminue, la sélectivité d’une valeur augmente. Par exemple, dans une table représentant des voitures, plusieurs voitures proviennent du même constructeur mais chacune a un numéro d'identification unique. Un index sur le numéro d'identification du véhicule est plus sélectif qu'un index sur le constructeur, car le numéro d'identification du véhicule a une plus faible densité que le constructeur.

Notes

La fréquence correspond aux informations sur l’occurrence de chaque valeur distincte dans la première colonne de clé de l’objet de statistiques. Elle est calculée ainsi : nombre de lignes x densité. Les colonnes qui comportent des valeurs uniques ont une fréquence maximale de 1.

Le vecteur de densité contient une densité pour chaque préfixe des colonnes dans l'objet de statistiques. Par exemple, si un objet de statistiques contient les colonnes clés CustomerId, ItemId et Price, la densité est calculée à partir des préfixes de colonnes suivants :

Préfixe de colonne Densité calculée sur
(CustomerId) Lignes avec des valeurs correspondantes pour CustomerID
(CustomerId, ItemId) Lignes avec des valeurs correspondantes pour CustomerId et ItemId
(CustomerId, ItemId, Price) Lignes avec des valeurs correspondantes pour CustomerId, ItemId et Price

Statistiques filtrées

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. Les statistiques filtrées utilisent un prédicat de filtre pour sélectionner le sous-ensemble de données qui est inclus dans les statistiques. Les statistiques filtrées correctement conçues peuvent améliorer le plan d'exécution de requête par rapport aux statistiques de table complète. Pour plus d’informations sur le prédicat de filtre, consultez CREATE STATISTICS (Transact-SQL). Pour plus d’informations sur l’opportunité de créer des statistiques filtrées, consultez la section Quand créer des statistiques dans cet article.

Options de statisques

Il existe trois options qui affectent quand et comment les statistiques sont créées et mises à jour. Ces options sont configurables au niveau de la base de données uniquement.

Option AUTO_CREATE_STATISTICS

Quand l’option de création automatique de statistiques AUTO_CREATE_STATISTICS est activée, l’optimiseur de requête crée les statistiques nécessaires sur les colonnes individuelles du prédicat de requête pour améliorer les estimations de cardinalité pour le plan de requête. Ces statistiques propres à une colonne sont créées sur les colonnes où ne figure pas déjà un histogramme dans un objet de statistiques existant. L'option AUTO_CREATE_STATISTICS ne détermine pas si les statistiques sont créées pour des index. De même, cette option ne génère pas de statistiques filtrées. Elle s'applique exclusivement aux statistiques de colonne unique pour la table entière.

Lorsque l'optimiseur de requête crée des statistiques suite à l'utilisation de l'option AUTO_CREATE_STATISTICS, le nom des statistiques commence par _WA. Vous pouvez utiliser la requête suivante pour déterminer si l'optimiseur de requête a créé des statistiques pour une colonne de prédicat de requête.

SELECT OBJECT_NAME(s.object_id) AS object_name,  
    COL_NAME(sc.object_id, sc.column_id) AS column_name,  
    s.name AS statistics_name  
FROM sys.stats AS s 
INNER JOIN sys.stats_columns AS sc  
    ON s.stats_id = sc.stats_id AND s.object_id = sc.object_id  
WHERE s.name like '_WA%'  
ORDER BY s.name;  

Option AUTO_UPDATE_STATISTICS

Quand l’option de mise à jour automatique des statistiques AUTO_UPDATE_STATISTICS est activée, l’optimiseur de requête détermine si les statistiques sont obsolètes, puis les met à jour le cas échéant quand elles sont utilisées par une requête. Cette action est également connue sous le nom de recompilation des statistiques. Les statistiques deviennent obsolètes après que des modifications des opérations d’insertion, de mise à jour, de suppression ou de fusion ont modifié la distribution des données dans la table ou la vue indexée. L’optimiseur de requête détermine si les statistiques sont obsolètes en comptant les modifications du nombre de lignes depuis la dernière mise à jour des statistiques et en comparant les modifications du nombre de lignes à un seuil. Ce seuil est basé sur la cardinalité de la table, ce qui peut être défini comme le nombre de lignes contenues dans la table ou la vue indexée.

  • Jusqu’à SQL Server 2014 (12.x), Moteur de base de données utilise un seuil de recompilation basé sur le nombre de lignes dans la table ou la vue indexée au moment de l’évaluation des statistiques. Le seuil est différent si une table est temporaire ou permanente.

    Type de la table Cardinalité de table (n) Seuil de recompilation (nombre de modifications)
    Temporaire n < 6 6
    Temporaire 6 <= n <= 500 500
    Permanent n <= 500 500
    Temporaire ou permanent n > 500 500 + (0,20 x n)

    Par exemple, si votre table contient 20 000 lignes, le calcul est 500 + (0.2 * 20,000) = 4,500 et les statistiques sont mises à jour toutes les 4 500 modifications.

  • À compter de SQL Server 2016 (13.x) et avec un niveau de compatibilité de base de données inférieur à 130, Moteur de base de données utilise également un seuil de recompilation des statistiques décroissant et dynamique qui s’ajuste en fonction de la cardinalité de la table et du moment de l’évaluation des statistiques. Du fait de cette modification, les statistiques sur des tables volumineuses sont mises à jour plus fréquemment. Toutefois, si une base de données affiche un niveau de compatibilité inférieur à 130, le seuil SQL Server 2014 (12.x) s’applique.

    Type de la table Cardinalité de table (n) Seuil de recompilation (nombre de modifications)
    Temporaire n < 6 6
    Temporaire 6 <= n <= 500 500
    Permanent n <= 500 500
    Temporaire ou permanent n >= 500 MIN ( 500 + (0.20 * n), SQRT(1,000 * n) )

    Par exemple, si votre table contient 2 millions de lignes, la valeur est le résultat le plus petit de ces deux calculs : 500 + (0.20 * 2,000,000) = 400,500 et SQRT(1,000 * 2,000,000) = 44,721. Cela signifie que les statistiques seront mises à jour toutes les 44 721 modifications.

Important

Dans SQL Server 2008 R2 à SQL Server 2014 (12.x), ou dans SQL Server 2016 (13.x) et versions ultérieures sous le niveau de compatibilité de la base de données 120 et inférieur, activez trace flag 2371 pour que SQL Server utilise un seuil de mise à jour des statistiques décroissant et dynamique.

Bien que recommandée pour tous les scénarios, l’activation de l’indicateur de trace est facultative. Toutefois, vous pouvez utiliser l’aide suivante pour activer l’indicateur de trace 2371 dans votre environnement pré-SQL Server 2016 (13.x) :

  • Si vous utilisez un système SAP, activez cet indicateur de trace. Pour plus d’informations, reportez-vous à ce blog.
  • Si vous exécutez des tâches nocturnes pour mettre à jour les statistiques car la mise à jour automatique actuelle n’est pas déclenchée assez fréquemment, vous pouvez activer l’indicateur de trace 2371 pour ajuster le seuil à la cardinalité de table.

L'optimiseur de requête vérifie s'il existe des statistiques obsolètes avant de compiler une requête et avant d'exécuter un plan de requête mis en cache. Avant de compiler une requête, l'optimiseur de requête utilise les colonnes, les tables et les vues indexées du prédicat de requête pour identifier les statistiques susceptibles d'être obsolètes. Avant d'exécuter un plan de requête mis en cache, le Moteur de base de données vérifie que le plan de requête fait référence à des statistiques à jour.

L’option AUTO_UPDATE_STATISTICS s’applique aux objets de statistiques créés pour les index, aux colonnes uniques contenues dans les prédicats de requête et aux statistiques créées à l’aide de l’instruction CREATE STATISTICS . Cette option s'applique également aux statistiques filtrées.

Vous pouvez utiliser le paramètre sys.dm_db_stats_properties pour suivre avec précision le nombre de lignes modifiées dans une table et déterminer si vous souhaitez mettre à jour les statistiques manuellement.

AUTO_UPDATE_STATISTICS_ASYNC

L’option de mise à jour asynchrone des statistiques AUTO_UPDATE_STATISTICS_ASYNC détermine si l’optimiseur de requête utilise des mises à jour des statistiques synchrones ou asynchrones. Par défaut, l’option de mise à jour asynchrone des statistiques est désactivée, et l’optimiseur de requête met à jour les statistiques de façon synchrone. L’option AUTO_UPDATE_STATISTICS_ASYNC s’applique aux objets de statistiques créés pour les index, aux colonnes uniques contenues dans les prédicats de requête et aux statistiques créées à l’aide de l’instruction CREATE STATISTICS .

Notes

Pour définir une option de mise à jour des statistiques de manière asynchrone dans SQL Server Management Studio, sur la page Options de la fenêtre Propriétés de la base de données, les deux options Mise à jour automatique des statistiques et Mise à jour automatique des statistiques de manière asynchrone doivent être définies sur True.

La mise à jour des statistiques peut être synchrone (par défaut) ou asynchrone.

  • Avec les mises à jour synchrones des statistiques, les requêtes sont toujours compilées et exécutées avec des statistiques à jour. Lorsque les statistiques sont obsolètes, l’optimiseur de requête attend les statistiques mises à jour avant de compiler et d’exécuter la requête.

  • Avec les mises à jour asynchrones des statistiques, les requêtes sont compilées avec les statistiques existantes, même si celles-ci sont obsolètes. L’optimiseur de requête peut choisir un plan de requête non optimal si les statistiques sont obsolètes lors de la compilation de la requête. Les statistiques sont généralement mises à jour peu de temps après. Les requêtes compilées à l’issue des mises à jour des statistiques ont l’avantage d’utiliser les statistiques mises à jour.

Envisagez d'utiliser des statistiques synchrones lorsque vous effectuez des opérations qui modifient la distribution des données, telles que la troncation d'une table ou une mise à jour en bloc d'un fort pourcentage de lignes. Si vous ne mettez pas à jour manuellement les statistiques à l’issue de l’opération, l’utilisation de statistiques synchrones vous garantit que les statistiques sont à jour avant d’exécuter des requêtes sur les données modifiées.

Envisagez d'utiliser des statistiques asynchrones pour obtenir des temps de réponse des requêtes plus prévisibles pour les scénarios suivants :

  • Votre application exécute régulièrement la même requête, des requêtes similaires ou des plans de requête mis en cache similaires. Il se peut que les temps de réponse de vos requêtes soient plus prévisibles avec des mises à jour de statistiques asynchrones qu'avec des mises à jour de statistiques synchrones, car l'optimiseur de requête peut exécuter les requêtes entrantes sans attendre la mise à jour des statistiques. Cela évite de retarder certaines requêtes et pas les autres.

  • Votre application a connu des expirations de délai de demandes clientes causées par une ou plusieurs requêtes en attente de statistiques mises à jour. Dans certains cas, l'attente de statistiques synchrones peut entraîner l'échec des applications dont les délais d'expiration sont agressifs.

Notes

Les statistiques sur les tables temporaires locales sont toujours mises à jour de façon synchrone, quelle que soit l’option AUTO_UPDATE_STATISTICS_ASYNC. Les statistiques sur les tables temporaires globales sont mises à jour de façon synchrone ou asynchrone en fonction de l’option AUTO_UPDATE_STATISTICS_ASYNC définie pour la base de données utilisateur.

La mise à jour asynchrone des statistiques est effectuée par une requête en arrière-plan. Lorsque la requête est prête à écrire des statistiques mises à jour dans la base de données, elle tente d’acquérir un verrou de modification de schéma sur l’objet de métadonnées des statistiques. Si une autre session détient déjà un verrou sur le même objet, la mise à jour asynchrone des statistiques est bloquée jusqu’à ce que le verrou de modification de schéma puisse être acquis. De même, les sessions qui doivent acquérir un verrou de stabilité de schéma (Sch-S) sur l’objet de métadonnées des statistiques pour compiler une requête peuvent être bloquées par la session d’arrière-plan de mise à jour asynchrone des statistiques, qui détient déjà ou attend l’acquisition du verrou de modification de schéma. Par conséquent, pour les charges de travail avec des compilations de requêtes très fréquentes et des mises à jour fréquentes de statistiques, l’utilisation de statistiques asynchrones peut augmenter la probabilité de problèmes d’accès concurrentiel dus à un blocage des verrous.

Dans Azure SQL Database et Azure SQL Managed Instance, vous pouvez éviter les éventuels problèmes d’accès concurrentiel à l’aide de la mise à jour asynchrone des statistiques si vous activez la configuration à l’échelle de la base de données ASYNC_STATS_UPDATE_WAIT_AT_LOW_PRIORITY. Quand cette configuration est activée, la requête en arrière-plan attend d’acquérir le verrou de modification de schéma (Sch-M) et conserve les statistiques mises à jour sur une file d’attente basse priorité distincte, ce qui permet à d’autres requêtes de continuer à compiler des requêtes avec des statistiques existantes. Une fois qu’aucune autre session ne détient un verrou sur l’objet de métadonnées des statistiques, la requête en arrière-plan acquiert le verrou de modification de schéma et met à jour les statistiques. Dans le cas improbable où la requête en arrière-plan ne peut pas acquérir le verrou dans un délai de plusieurs minutes, la mise à jour des statistiques asynchrones est abandonnée et les statistiques ne sont pas mises à jour tant qu’une autre mise à jour automatique des statistiques n’est pas déclenchée, ou jusqu’à ce que les statistiques soient mises à jour manuellement.

Notes

L’option de configuration délimitée aux bases de données ASYNC_STATS_UPDATE_WAIT_AT_LOW_PRIORITY est disponible dans Azure SQL Database et Azure SQL Managed Instance. Il est prévu qu’elle soit disponible dans SQL Server.

INCREMENTAL

Quand l’option INCREMENTAL de CREATE STATISTICS est définie sur ON, les statistiques sont créées pour chaque 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. Ce paramètre remplace la propriété INCREMENTAL de niveau base de données. Pour plus d’informations sur la création de statistiques incrémentielles, consultez CREATE STATISTICS (Transact-SQL). Pour plus d’informations sur la création automatique de statistiques par partition, consultez Propriétés de la base de données (page Options) et Options ALTER DATABASE SET (Transact-SQL).

Lorsque de nouvelles partitions sont ajoutées à une table volumineuse, les statistiques doivent être mises à jour afin d'inclure les nouvelles partitions. Cependant, la durée nécessaire pour analyser la table entière (option FULLSCAN ou SAMPLE) peut être assez longue. En outre, l'analyse la table entière n'est pas nécessaire car seules les statistiques sur les nouvelles partitions peuvent être requises. L'option incrémentielle crée et stocke des statistiques par partition, et une fois la mise à jour terminée, seules sont actualisées les statistiques sur les partitions qui ont besoin de nouvelles statistiques

Si les statistiques par partition ne sont pas prises en charge, l'option est ignorée et un avertissement est généré. Les statistiques incrémentielles ne sont pas prises en charge pour les types de statistiques suivants :

  • statistiques créées avec des index qui ne sont pas alignés sur les partitions 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.

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

Quand créer des statistiques

L'optimiseur de requête crée déjà des statistiques selon les méthodes suivantes :

  1. L'optimiseur de requête crée des statistiques pour les index de tables ou de vues lors de la création des index. Ces statistiques sont créées sur les colonnes de clés de l'index. Si l'index est un index filtré, l'optimiseur de requête crée des statistiques filtrées sur le même sous-ensemble de lignes spécifié pour l'index filtré. Pour plus d’informations sur les index filtrés, consultez Créer des index filtrés et CREATE INDEX (Transact-SQL).

    Notes

    À partir de SQL Server 2014 (12.x), les statistiques ne sont pas créées en analysant toutes les lignes de la table quand un index partitionné est créé ou reconstruit. Au lieu de cela, l’optimiseur de requête utilise l’algorithme d’échantillonnage par défaut pour générer des statistiques. Après la mise à niveau d'une base de données avec des index partitionnés, vous pouvez remarquer une différence dans les données d'histogramme pour ces index. Cette modification du comportement peut ne pas affecter les performances des requêtes. Pour obtenir des statistiques sur les index partitionnés en analysant toutes les lignes de la table, utilisez CREATE STATISTICS ou UPDATE STATISTICS avec la clause FULLSCAN.

  2. L’optimiseur de requête crée des statistiques pour les colonnes individuelles des prédicats de requête quand l’option AUTO_CREATE_STATISTICS est activée.

Pour la plupart des requêtes, ces deux méthodes de création de statistiques sont l’assurance de disposer d’un plan de requête de haute qualité. Dans certains cas, vous pouvez améliorer les plans de requête en créant des statistiques supplémentaires à l’aide de l’instruction CREATE STATISTICS . Ces statistiques supplémentaires peuvent capturer des corrélations statistiques dont l'optimiseur de requête ne tient pas compte lorsqu'il crée des statistiques pour des index ou des colonnes uniques. Il se peut que votre application présente des corrélations statistiques supplémentaires dans les données de table qui, si elles sont calculées dans un objet de statistiques, peuvent permettre à l'optimiseur de requête d'améliorer les plans de requête. Par exemple, les statistiques filtrées sur un sous-ensemble de lignes de données ou les statistiques multicolonnes sur des colonnes de prédicat de requête sont susceptibles d'améliorer le plan de requête.

Dans le cadre de la création de statistiques à l'aide de l'instruction CREATE STATISTICS, nous vous recommandons de maintenir l'option AUTO_CREATE_STATISTICS activée de sorte que l'optimiseur de requête continue de créer de manière régulière des statistiques de colonne unique pour les colonnes de prédicat de requête. Pour plus d’informations sur les prédicats de requête, consultez Condition de recherche (Transact-SQL).

Envisagez de créer des statistiques avec l'instruction CREATE STATISTICS dans l'un des cas suivants :

  • l'Assistant Paramétrage du Moteur de base de données suggère de créer des statistiques ;
  • le prédicat de requête contient plusieurs colonnes corrélées qui ne figurent pas déjà dans le même index ;
  • la requête effectue une sélection dans un sous-ensemble de données ;
  • il manque des statistiques pour la requête.

Notes

Pour plus d'informations sur les tables et les statistiques liées à la fonctionnalité OLTP en mémoire, consultez Statistiques pour les tables optimisées en mémoire.

Le prédicat de requête contient plusieurs colonnes corrélées

Lorsqu'un prédicat de requête contient plusieurs colonnes ayant entre elles des relations et des dépendances, des statistiques sur les différentes colonnes peuvent améliorer le plan de requête. Les statistiques sur plusieurs colonnes contiennent des statistiques de corrélation entre les colonnes appelées densités, qui ne sont pas disponibles dans les statistiques de colonne unique. Les densités peuvent améliorer les estimations de cardinalité lorsque les résultats de requête dépendent des relations de données entre plusieurs colonnes.

Si les colonnes figurent déjà dans le même index, l'objet de statistiques multicolonnes existe déjà et il n'est pas nécessaire de le créer manuellement. Si les colonnes ne figurent pas déjà dans le même index, vous pouvez créer des statistiques multicolonnes en créant un index sur les colonnes ou en utilisant l’instruction CREATE STATISTICS. Un index exige davantage de ressources système qu'un objet de statistiques. Ainsi, si l'application n'a pas besoin de l'index multicolonne, vous pouvez économiser des ressources système en créant l'objet de statistiques sans créer l'index.

Lors de la création de statistiques multicolonnes, l'ordre des colonnes dans la définition de l'objet de statistiques a une incidence sur l'efficacité des densités lorsqu'il s'agit de réaliser des estimations de cardinalité. L'objet de statistiques stocke des densités pour chaque préfixe de colonnes de clés contenu dans la définition de l'objet de statistiques. Pour plus d’informations sur les densités, consultez la section Densité dans cette page.

Pour créer des densités utiles aux estimations de cardinalité, les colonnes du prédicat de requête doivent correspondre à l'un des préfixes de colonnes contenus dans la définition de l'objet de statistiques. L’exemple suivant crée un objet de statistiques multicolonnes sur les colonnes LastName, MiddleName et FirstName.

USE AdventureWorks2012;  
GO  
IF EXISTS (SELECT name FROM sys.stats  
    WHERE name = 'LastFirst'  
    AND object_ID = OBJECT_ID ('Person.Person'))  
DROP STATISTICS Person.Person.LastFirst;  
GO  
CREATE STATISTICS LastFirst ON Person.Person (LastName, MiddleName, FirstName);  
GO  

Dans cet exemple, l'objet de statistiques LastFirst comprend des densités pour les préfixes de colonne suivants: (LastName), (LastName, MiddleName) et (LastName, MiddleName, FirstName). La densité n'est pas disponible pour (LastName, FirstName). Si la requête utilise LastName et FirstName sans utiliser MiddleName, la densité n'est pas disponible pour les estimations de cardinalité.

La requête effectue une sélection dans un sous-ensemble de données

Lorsque l'optimiseur de requête crée des statistiques pour des colonnes et des index uniques, il crée les statistiques pour les valeurs contenues dans toutes les lignes. Lorsque les requêtes effectuent une sélection dans un sous-ensemble de lignes et que ce sous-ensemble de lignes présente une distribution de données unique, des statistiques filtrées peuvent améliorer les plans de requête. Vous pouvez créer des statistiques filtrées en utilisant l’instruction CREATE STATISTICS avec la clause WHERE pour définir l’expression de prédicat du filtre.

Par exemple, avec AdventureWorks2012, chaque produit de la table Production.Product appartient à une des quatre catégories de la table Production.ProductCategory : Bikes, Components, Clothing et Accessories. Chacune de ces catégories présente une distribution de données différente pour le poids (Weight) : le poids des bicyclettes (Bikes) varie de 13,77 à 30,0, le poids des composants (Components) varie de 2,12 à 1 050,00 avec quelques valeurs NULL, le poids des vêtements (Clothing) est toujours NULL et le poids des accessoires (Accessories) est également toujours NULL.

Si l'on prend la catégorie Bikes pour exemple, les statistiques filtrées sur tous les poids de bicyclettes fournissent des statistiques plus précises à l'optimisateur de requête et peuvent améliorer la qualité du plan de requête par rapport aux statistiques de table entière ou aux statistiques inexistantes de la colonne Weight (Poids). Si la colonne où figure le poids des bicyclettes constitue un candidat valable pour les statistiques filtrées, tel n'est pas forcément le cas pour un index filtré si le nombre de recherches de poids est relativement faible. Les gains en performances offerts par un index filtré lors des recherches risquent de ne pas compenser les coûts de maintenance et de stockage supplémentaires liés à l'ajout d'un index filtré à la base de données.

L'instruction suivante crée les statistiques filtrées BikeWeights sur toutes les sous-catégories de bicyclettes (Bikes). L'expression de prédicat filtré définit les bicyclettes en énumérant toutes les sous-catégories de bicyclettes à l'aide de la comparaison Production.ProductSubcategoryID IN (1,2,3). Le prédicat ne peut pas utiliser le nom de catégorie Bikes car il est stocké dans la table Production.ProductCategory, et toutes les colonnes spécifiées dans l'expression de filtre doivent se trouver dans la même table.

USE AdventureWorks2012;
GO
IF EXISTS ( SELECT name FROM sys.stats
    WHERE name = 'BikeWeights'
    AND object_ID = OBJECT_ID ('Production.Product'))
DROP STATISTICS Production.Product.BikeWeights;
GO
CREATE STATISTICS BikeWeights
    ON Production.Product (Weight)
WHERE ProductSubcategoryID IN (1,2,3);
GO

L'optimiseur de requête peut utiliser les statistiques filtrées BikeWeights pour améliorer le plan de requête de la requête suivante qui sélectionne toutes les bicyclettes dont le poids est supérieur à 25.

SELECT P.Weight AS Weight, S.Name AS BikeName  
FROM Production.Product AS P  
    JOIN Production.ProductSubcategory AS S   
    ON P.ProductSubcategoryID = S.ProductSubcategoryID  
WHERE P.ProductSubcategoryID IN (1,2,3) AND P.Weight > 25  
ORDER BY P.Weight;  
GO  

La requête identifie les statistiques manquantes

Si une erreur ou tout autre événement empêche l'optimiseur de requête de créer des statistiques, il crée le plan de requête sans utiliser de statistiques. L'optimiseur de requête indique que les statistiques sont manquantes et tente de les régénérer à la prochaine exécution de la requête.

Les statistiques manquantes sont indiquées comme des avertissements (nom de la table en rouge) lorsque le plan d'exécution d'une requête est affiché sous forme graphique à l'aide de SQL Server Management Studio. D'autre part, le permet de surveiller la classe d'événements Missing Column Statistics SQL Server Profiler et d'être tenu informé lorsque des statistiques manquent. Pour plus d’informations, consultez Catégorie d’événement Erreurs et avertissements (moteur de base de données).

S'il manque des statistiques, procédez comme suit :

  • Vérifiez que les options AUTO_CREATE_STATISTICS et AUTO_UPDATE_STATISTICS sont activées.
  • vérifiez que la base de données n'est pas en lecture seule. Si la base de données est en lecture seule, un nouvel objet de statistiques ne peut pas être enregistré.
  • Créez les statistiques manquantes en utilisant l’instruction CREATE STATISTICS.

Lorsque les statistiques sur une base de données en lecture seule ou un instantané en lecture seule sont absentes ou obsolètes, le Moteur de base de données crée et gère les statistiques temporaires dans tempdb. Lorsque le Moteur de base de données crée des statistiques temporaires, le nom des statistiques est ajouté avec le suffixe _readonly_database_statistic pour différencier les statistiques temporaires des statistiques permanentes. Le suffixe _readonly_database_statistic est réservé aux statistiques générées par SQL Server. Des scripts pour les statistiques temporaires peuvent être créés et reproduits sur une base de données en lecture-écriture. Le cas échéant, Management Studio remplace le suffixe du nom des statistiques _readonly_database_statistic par _readonly_database_statistic_scripted.

Seul SQL Server peut créer et mettre à jour les statistiques temporaires. Toutefois, vous pouvez supprimer des statistiques temporaires et analyser les propriétés des statistiques en utilisant les mêmes outils que ceux que vous utilisez pour les statistiques permanentes :

  • Supprimez les statistiques temporaires en utilisant l’instruction DROP STATISTICS.
  • Surveillez les statistiques en utilisant les vues du catalogue sys.stats et sys.stats_columns . sys_stats inclut la colonne is_temporary pour indiquer les statistiques permanentes et temporaires.

Étant donné que les statistiques temporaires sont stockées dans tempdb, un redémarrage du service SQL Server provoque la disparition de toutes les statistiques temporaires.

Quand mettre à jour des statistiques

L'optimiseur de requête détermine si les statistiques sont obsolètes et les met éventuellement à jour lorsqu'elles sont requises par un plan de requête. Dans certains cas, vous pouvez améliorer le plan de requête et donc les performances des requêtes en mettant à jour les statistiques plus fréquemment que quand l’option AUTO_UPDATE_STATISTICS est activée. Vous pouvez mettre à jour les statistiques à l'aide de l'instruction UPDATE STATISTICS ou de la procédure stockée sp_updatestats.

La mise à jour des statistiques est l'assurance que les requêtes sont compilées avec des statistiques à jour. Toutefois, la mise à jour des statistiques entraîne une recompilation des requêtes. À ce titre, il est déconseillé de mettre à jour les statistiques de façon trop régulière eu égard aux performances. Un compromis doit être trouvé entre le souhait d'améliorer les plans de requête et le temps nécessaire à la recompilation des requêtes. Ce compromis peut varier en fonction de votre application.

Dans le cadre d'une mise à jour des statistiques avec UPDATE STATISTICS ou sp_updatestats, nous vous recommandons de maintenir l'option AUTO_UPDATE_STATISTICS activée (valeur ON) de sorte que l'optimiseur de requête continue de mettre à jour les statistiques de façon régulière. Pour plus d’informations sur la façon de mettre à jour les statistiques sur une colonne, un index, une table ou une vue indexée, consultez UPDATE STATISTICS (Transact-SQL). Pour plus d’informations 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, consultez la procédure stockée sp_updatestats (Transact-SQL).

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

Envisagez de mettre à jour les statistiques dans les cas suivants :

  • lenteur d'exécution des requêtes ;
  • opérations d'insertion appliquées à des colonnes de clés triées par ordre croissant ou décroissant ;
  • opérations de maintenance fraîchement effectuées.

Lenteur d'exécution des requêtes

Si les temps de réponse des requêtes sont longs ou imprévisibles, assurez-vous que les requêtes disposent de statistiques à jour avant d'exécuter d'autres procédures de dépannage.

Opérations d'insertion appliquées à des colonnes de clés triées par ordre croissant ou décroissant

Les statistiques sur les colonnes de clés triées par ordre croissant ou décroissant, telles que les colonnes IDENTITY ou les colonnes de type timestamp en temps réel, peuvent nécessiter des mises à jour plus régulières que celles effectuées par l'optimiseur de requête. Les opérations d'insertion ajoutent de nouvelles valeurs aux colonnes triées par ordre croissant ou décroissant. Le nombre de lignes ajoutées peut s'avérer trop faible pour déclencher une mise à jour des statistiques. Si les statistiques ne sont pas à jour et que les requêtes sélectionnent des lignes récemment ajoutées, les statistiques actuelles ne comporteront pas d'estimations de cardinalité pour ces nouvelles valeurs. Cela peut se traduire par des estimations de cardinalité imprécises et des performances de requêtes en retrait.

Par exemple, une requête qui sélectionne des dates de commande parmi les plus récentes présentera des estimations de cardinalité imprécises si les statistiques ne sont pas mises à jour pour inclure les estimations de cardinalité des dates de commande les plus récentes.

Opérations de maintenance fraîchement effectuées

Envisagez de mettre à jour les statistiques après avoir exécuté des procédures de maintenance qui modifient la distribution des données, telles que la troncation d'une table ou l'exécution d'une insertion en bloc d'un fort pourcentage de lignes. Vous éviterez ainsi des retards dans le traitement ultérieur des requêtes du fait de l'attente des mises à jour automatiques des statistiques.

Les opérations de reconstruction, de défragmentation ou de réorganisation d'index ne modifient pas la distribution des données. Par conséquent, vous n’avez pas besoin de mettre à jour les statistiques après avoir effectué des opérations ALTER INDEX REBUILD, DBCC DBREINDEX, DBCC INDEXDEFRAG ou ALTER INDEX REORGANIZE. Si l'optimiseur de requête met effectivement à jour les statistiques lors de la reconstruction d'un index sur une table ou une vue via ALTER INDEX REBUILD ou DBCC DBREINDEX, cette mise à jour des statistiques est une conséquence de la recréation de l'index. L'optimiseur de requête ne met pas à jour les statistiques suite à des opérations DBCC INDEXDEFRAG ou ALTER INDEX REORGANIZE.

Conseil

À partir de SQL Server 2016 (13.x) SP1 CU4, utilisez l’option PERSIST_SAMPLE_PERCENT de CREATE STATISTICS (Transact-SQL) ou UPDATE STATISTICS (Transact-SQL) pour définir et conserver un pourcentage d’échantillonnage spécifique pour les mises à jour des statistiques suivantes qui ne définissent pas explicitement un pourcentage d’échantillonnage.

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.

Requêtes pour une utilisation efficace des statistiques

Certaines implémentations de requête, telles que les variables locales et les expressions complexes contenues dans le prédicat de requête, peuvent produire des plans de requête non optimaux. Cela peut s'éviter en suivant les recommandations en matière de conception de requêtes pour une utilisation efficace des statistiques. Pour plus d’informations sur les prédicats de requête, consultez Condition de recherche (Transact-SQL).

Vous pouvez améliorer les plans de requête en appliquant les recommandations en matière de conception de requêtes pour une utilisation efficace des statistiques. Les estimations de cardinalité relatives aux expressions, aux variables et aux fonctions utilisées dans les prédicats de requête s'en trouveront améliorées. Lorsque l'optimiseur de requête ne connaît pas la valeur d'une expression, d'une variable ou d'une fonction, il ne sait pas quelle valeur rechercher dans l'histogramme et ne peut donc pas extraire la meilleure estimation de cardinalité de l'histogramme. Au lieu de cela, l'optimiseur de requête base l'estimation de cardinalité sur le nombre moyen de lignes par valeur distincte pour toutes les lignes échantillonnées dans l'histogramme. Il en résulte des estimations de cardinalité non optimales et une altération potentielle des performances des requêtes. Pour plus d’informations sur les histogrammes, consultez la section histogramme de cette page ou sys.dm_db_stats_histogram.

Les recommandations suivantes indiquent comment écrire les requêtes pour améliorer les plans de requête à travers l'amélioration des estimations de cardinalité.

Amélioration des estimations de cardinalité pour les expressions

Pour améliorer les estimations de cardinalité pour les expressions, respectez les principes suivants :

  • Dans la mesure du possible, simplifiez les expressions en y intégrant des constantes. L'optimiseur de requête n'évalue pas toutes les fonctions et les expressions contenant des constantes avant de déterminer les estimations de cardinalité. Par exemple, simplifiez l'expression ABS(-100) en 100.

  • Si l'expression utilise plusieurs variables, songez à créer une colonne calculée pour l'expression, puis créez des statistiques ou un index sur la colonne calculée. Par exemple, le prédicat de requête WHERE PRICE + Tax > 100 bénéficiera peut-être d'une meilleure estimation de cardinalité si vous créez une colonne calculée pour l'expression Price + Tax.

Amélioration des estimations de cardinalité pour les variables et les fonctions

Pour améliorer les estimations de cardinalité pour les variables et les fonctions, respectez les principes suivants :

  • Si le prédicat de requête utilise une variable locale, envisagez de réécrire la requête de sorte qu'elle utilise un paramètre au lieu d'une variable locale. La valeur d'une variable locale n'est pas connue au moment où l'optimiseur de requête crée le plan d'exécution de requête. Lorsqu'une requête utilise un paramètre, l'optimiseur de requête utilise l'estimation de cardinalité pour la première valeur effective du paramètre qui est transmise à la procédure stockée.

  • Envisagez d'utiliser une table standard ou une table temporaire pour consigner les résultats de fonctions table à instructions multiples (mstvf). L'optimiseur de requête ne crée pas de statistiques pour les fonctions table à instructions multiples. Du fait de cette approche, l'optimiseur de requête peut créer des statistiques sur les colonnes de table et s'en servir pour créer un meilleur plan de requête.

  • Envisagez d'utiliser une table standard ou une table temporaire en remplacement de variables de table. L'optimiseur de requête ne crée pas de statistiques pour les variables de table. Du fait de cette approche, l'optimiseur de requête peut créer des statistiques sur les colonnes de table et s'en servir pour créer un meilleur plan de requête. Il convient de peser le pour et le contre au moment d'opter pour une table temporaire ou une variable de table : les variables de table utilisées dans les procédures stockées aboutissent à moins de recompilations de ces dernières que les tables temporaires. Selon l'application, l'utilisation d'une table temporaire à la place d'une variable de table n'améliorera pas forcément les performances.

  • Si une procédure stockée contient une requête qui utilise un paramètre transmis, évitez de modifier la valeur du paramètre dans la procédure stockée avant de l'utiliser dans la requête. Les estimations de cardinalité de la requête sont basées sur la valeur du paramètre transmis et non sur la valeur mise à jour. Pour éviter de modifier la valeur du paramètre, vous pouvez réécrire la requête de sorte qu'elle utilise deux procédures stockées.

    Par exemple, la procédure stockée suivante Sales.GetRecentSales modifie la valeur du paramètre @date lorsque @date a la valeur NULL.

    USE AdventureWorks2012;  
    GO  
    IF OBJECT_ID ( 'Sales.GetRecentSales', 'P') IS NOT NULL  
        DROP PROCEDURE Sales.GetRecentSales;  
    GO  
    CREATE PROCEDURE Sales.GetRecentSales (@date datetime)  
    AS BEGIN  
        IF @date IS NULL  
            SET @date = DATEADD(MONTH, -3, (SELECT MAX(ORDERDATE) FROM Sales.SalesOrderHeader))  
        SELECT * FROM Sales.SalesOrderHeader h, Sales.SalesOrderDetail d  
        WHERE h.SalesOrderID = d.SalesOrderID  
        AND h.OrderDate > @date  
    END  
    GO  
    

    Si le premier appel à la procédure stockée Sales.GetRecentSales transmet une valeur NULL pour le paramètre @date , l'optimiseur de requête compilera la procédure stockée avec l'estimation de cardinalité de @date = NULL même si le prédicat de requête n'est pas appelé avec @date = NULL. Il se peut que cette estimation de cardinalité soit sensiblement différente du nombre de lignes présenté dans le résultat réel de la requête. En conséquence, l'optimiseur de requête risque de choisir un plan de requête non optimisé. Pour éviter cela, vous pouvez réécrire la procédure stockée dans deux procédures comme dans l'exemple suivant :

    USE AdventureWorks2012;  
    GO  
    IF OBJECT_ID ( 'Sales.GetNullRecentSales', 'P') IS NOT NULL  
        DROP PROCEDURE Sales.GetNullRecentSales;  
    GO  
    CREATE PROCEDURE Sales.GetNullRecentSales (@date datetime)  
    AS BEGIN  
        IF @date is NULL  
            SET @date = DATEADD(MONTH, -3, (SELECT MAX(ORDERDATE) FROM Sales.SalesOrderHeader))  
        EXEC Sales.GetNonNullRecentSales @date;  
    END  
    GO  
    IF OBJECT_ID ( 'Sales.GetNonNullRecentSales', 'P') IS NOT NULL  
        DROP PROCEDURE Sales.GetNonNullRecentSales;  
    GO  
    CREATE PROCEDURE Sales.GetNonNullRecentSales (@date datetime)  
    AS BEGIN  
        SELECT * FROM Sales.SalesOrderHeader h, Sales.SalesOrderDetail d  
        WHERE h.SalesOrderID = d.SalesOrderID  
        AND h.OrderDate > @date  
    END  
    GO  
    

Amélioration des estimations de cardinalité au moyen d'indicateurs de requête

Pour améliorer les estimations de cardinalité des variables locales, vous pouvez utiliser les indicateurs de requête OPTIMIZE FOR <value> ou OPTIMIZE FOR UNKNOWN avec RECOMPILE. Pour plus d’informations, consultez Indicateurs de requête (Transact-SQL).

Pour certaines applications, la recompilation de la requête à chacune de ses exécutions peu prendre trop de temps. L'indicateur de requête OPTIMIZE FOR peut s'avérer utile même si vous n'utilisez pas l'option RECOMPILE. Par exemple, vous pouvez ajouter une option OPTIMIZE FOR à la procédure stockée Sales.GetRecentSales pour spécifier une date précise. L'exemple suivant ajoute l'option OPTIMIZE FOR à la procédure Sales.GetRecentSales.

USE AdventureWorks2012;  
GO  
IF OBJECT_ID ( 'Sales.GetRecentSales', 'P') IS NOT NULL  
    DROP PROCEDURE Sales.GetRecentSales;  
GO  
CREATE PROCEDURE Sales.GetRecentSales (@date datetime)  
AS BEGIN  
    IF @date is NULL  
        SET @date = DATEADD(MONTH, -3, (SELECT MAX(ORDERDATE) FROM Sales.SalesOrderHeader))  
    SELECT * FROM Sales.SalesOrderHeader h, Sales.SalesOrderDetail d  
    WHERE h.SalesOrderID = d.SalesOrderID  
    AND h.OrderDate > @date  
    OPTION ( OPTIMIZE FOR ( @date = '2004-05-01 00:00:00.000'))  
END;  
GO  

Amélioration des estimations de cardinalité au moyen de repères de plan

Pour certaines applications, les recommandations en conception de requêtes peuvent ne pas s'appliquer, soit parce que vous ne pouvez pas modifier la requête, soit parce que l’indicateur de requête RECOMPILE risque d’entraîner un nombre trop important de recompilations. Vous pouvez utiliser des repères de plan pour spécifier d'autres indicateurs, tels que USE PLAN, dans le but de contrôler le comportement de la requête, en attendant de trouver une solution avec l'éditeur de l'application. Pour plus d'informations sur les repères de plan, consultez Plan Guides.

Voir aussi

Statistiques pour les tables optimisées en mémoire
CREATE STATISTICS (Transact-SQL)
UPDATE STATISTICS (Transact-SQL)
sp_updatestats (Transact-SQL)
DBCC SHOW_STATISTICS (Transact-SQL)
Options ALTER DATABASE SET (Transact-SQL)
DROP STATISTICS (Transact-SQL)
CREATE INDEX (Transact-SQL)
ALTER INDEX (Transact-SQL)
Créer des index filtrés
Contrôle du comportement des statistiques automatiques (AUTO_UPDATE_STATISTICS) dans SQL Server
STATS_DATE (Transact-SQL)
sys.dm_db_stats_properties (Transact-SQL)
sys.dm_db_stats_histogram (Transact-SQL)
sys.stats
sys.stats_columns (Transact-SQL)
Adaptive Index Defrag