Partager via

Statistics

S’applique à :SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsBase de données SQL dans Microsoft Fabric Preview

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 nécessaires pour un plan de requête de haute qualité ; dans certains cas, vous devez créer des statistiques supplémentaires ou modifier la conception de requête pour obtenir de meilleurs résultats. 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

Statistics

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.

Histogram

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.

Note

Les histogrammes dans SQL Server sont générés uniquement pour une colonne unique, la première colonne de l’ensemble 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 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 parce que 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 si une quantité importante d’informations n’est pas perdue. 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 composée uniquement de valeurs uniques, l’histogramme consolidé comporte au moins trois étapes.

Note

Si l’histogramme a été créé à l’aide d’un échantillon plutôt que d’une analyse complète, les valeurs de equal_rows, range_rows, distinct_range_rows et average_range_rows sont estimées, et par conséquent, elles n’ont pas besoin d’être des nombres 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.

Diagramme illustrant le calcul d’un histogramme à partir de valeurs de colonnes échantillonnées.

Pour chaque étape d’histogramme de l’exemple précédent :

  • 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 échantillonées.

Vecteur de densité

La densité est des informations sur le nombre de doublons dans une colonne donnée ou une combinaison de colonnes et elle est calculée en tant que 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.

Note

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 : row count * density. 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, , PriceItemId) 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. 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 statistiques

Il existe trois options qui impactent le moment où, et la façon dont, 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 les index. Cette option ne génère pas non plus 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

Lorsque l’option de mise à jour automatique des statistiques, AUTO_UPDATE_STATISTICS est ON, l’optimiseur de requête détermine quand les statistiques peuvent être obsolètes, puis les met à jour lorsqu’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.

Le marquage des statistiques comme obsolètes en fonction des modifications de ligne se produit même lorsque l’option AUTO_UPDATE_STATISTICS est désactivée. Lorsque l’option AUTO_UPDATE_STATISTICS est DÉSACTIVÉE, les statistiques ne sont pas mises à jour, même lorsqu’elles sont marquées comme obsolètes. Les plans continuent d’utiliser les objets de statistiques obsolètes. Si AUTO_UPDATE_STATISTICS a la valeur OFF, il peut en résulter des plans de requête non optimisés et une dégradation des performances des requêtes. La définition de l’option AUTO_UPDATE STATISTICS sur ON est recommandée.

  • Jusqu’à SQL Server 2014 (12.x), le 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 table Cardinalité de table (n) Seuil de recompilation (nombre de modifications)
    Temporary n< 6 6
    Temporary 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 mille 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 le niveau de compatibilité de base de données 130, le 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. Avec cette modification, les statistiques sur les 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 de SQL Server 2014 (12.x) s’applique.

    Type de table Cardinalité de table (n) Seuil de recompilation (nombre de modifications)
    Temporary n < 6 6
    Temporary 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 sont mises à jour toutes les 44 721 modifications.

Important

De SQL Server 2008 R2 (10.50.x) à SQL Server 2014 (12.x), ou dans SQL Server 2016 (13.x) et les versions ultérieures avec un niveau de compatibilité de la base de données 120 et les versions inférieures, activez l’indicateur de trace 2371 afin que SQL Server utilise un seuil dynamique décroissant de mise à jour des statistiques.

Bien que recommandée pour tous les scénarios, l’activation de l’indicateur de trace 2371 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 êtes sur un système SAP, activez cette trace. Pour plus d’informations, consultez ce blog sur l’indicateur de trace 2371.
  • 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, tables et vues indexées dans le prédicat de requête pour déterminer quelles statistiques peuvent ê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 aux up-to-date statistiques.

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 est toujours désactivé (OFF) pour les tables optimisées en mémoire.

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 .

Note

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 qui se compilent une fois les mises à jour des statistiques terminées bénéficient de l’utilisation des 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 après avoir terminé l’opération, l’utilisation de statistiques synchrones garantit que les statistiques sont up-to-date avant que les requêtes ne soient exécutées 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.

Note

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, Azure SQL Managed Instance et à compter de SQL Server 2022 (16.x), 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 limitée à la base de données ASYNC_STATS_UPDATE_WAIT_AT_LOW_PRIORITY. Une fois cette configuration activée, la demande en arrière-plan attend d’acquérir le verrou de modification de schéma (Sch-M) et de conserver les statistiques mises à jour sur une file d’attente distincte de faible priorité, ce qui permet à d’autres requêtes de continuer à compiler des requêtes avec des statistiques existantes. Une fois qu’aucune autre session ne contient de verrou sur l’objet de métadonnées de statistiques, la requête en arrière-plan acquiert son verrou de modification de schéma et met à jour les statistiques. Dans le cas peu probable où la demande en arrière-plan ne peut pas acquérir le verrou dans un délai d’expiration de plusieurs minutes, la mise à jour asynchrone des statistiques sera 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.

Note

L’option de configuration limitée à la base de données ASYNC_STATS_UPDATE_WAIT_AT_LOW_PRIORITY est disponible dans Azure SQL Database, Azure SQL Managed Instance et SQL Server à compter de SQL Server 2022 (16.x).

option AUTO_DROP

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

Dans les versions de SQL Server antérieures à SQL Server 2022 (16.x), si des statistiques sont créées manuellement par un utilisateur ou par un outil tiers sur une base de données utilisateur, ces objets de statistiques peuvent bloquer les modifications de schéma que vous pouvez souhaiter ou interférer avec celles-ci.

À compter de SQL Server 2022 (16.x), l’option de suppression automatique 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 ultérieure n’est pas bloquée par l’objet statistique, mais que les statistiques sont supprimées si nécessaire. De cette façon, les statistiques créées manuellement avec la suppression automatique activée se comportent comme des statistiques créées automatiquement.

Dans Azure SQL Database, Azure SQL Managed Instance et SQL Server 2022 (16.x) et versions ultérieures, les statistiques créées automatiquement se comportent toujours comme si les AUTO_DROP ont été définies.

Note

Une tentative de définition ou d’annulation de la définition de la propriété de suppression automatique 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 automatiquement se comportent toujours comme des statistiques avec suppression automatique. Lors de la restauration d’une base de données vers SQL Server 2022 (16.x) à partir d’une version précédente, il est recommandé d’exécuter sp_updatestats sur la base de données, en définissant les métadonnées appropriées pour la fonctionnalité de suppression automatique des statistiques.

Par exemple, pour créer manuellement un objet de statistiques sur la table dbo.DatabaseLog :

CREATE STATISTICS [mystats]
    ON [dbo].[DatabaseLog]([DatabaseLogID], [PostTime], [DatabaseUser])
    WITH AUTO_DROP = ON;

Par exemple, pour mettre à jour un paramètre de suppression automatique d’objet de statistiques sur la table dbo.DatabaseLog :

UPDATE STATISTICS [dbo].[DatabaseLog] ([mystats])
    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;

Pour plus d’informations, consultez AUTO_DROP.

INCREMENTAL

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

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. Pour plus d’informations sur la création automatique des statistiques par partition, consultez Propriétés de Base de Données (Page des Options) et ALTER DATABASE SET options.

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 (options 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 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.

À quel moment faut-il créer les 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.

    Note

    Dans SQL Server 2014 (12.x) et versions ultérieures, les statistiques ne sont pas créées en analysant toutes les lignes de la table lorsqu’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 les index ou les 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, il est recommandé de laisser 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 la condition De recherche.

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 sont pas déjà des clés 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.

Note

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 colonnes, appelées densités, qui ne sont pas disponibles dans les statistiques à 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 se trouvent déjà dans le même index, l’objet de statistiques multicolumn existe déjà et il n’est pas nécessaire de le créer manuellement. Si les colonnes ne se trouvent pas déjà dans le même index, vous pouvez créer des statistiques multicolonnes en créant un index sur les colonnes ou à l’aide de l’instruction CREATE STATISTICS . Un index exige davantage de ressources système qu'un objet de statistiques. Si l’application ne nécessite pas l’index multicolumn, vous pouvez économiser sur les ressources système en créant l’objet de statistiques sans créer l’index.

Lorsque vous créez des statistiques multicolonnes, l’ordre des colonnes dans la définition de l’objet de statistiques affecte l’efficacité des densités pour effectuer 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 AdventureWorks2022;
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 AdventureWorks2022, 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.

En utilisant Bikes comme exemple, les statistiques filtrées sur tous les poids des vélos fournissent des statistiques plus précises au moteur d'optimisation de requêtes et peuvent améliorer la qualité du plan de requête comparé aux statistiques sur table complète ou à l'absence de statistiques sur la colonne 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 BikeWeights statistiques filtrées sur toutes les sous-catégories pour 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 Bikes nom de catégorie, car il est stocké dans la Production.ProductCategory table, et toutes les colonnes de l’expression de filtre doivent se trouver dans la même table.

USE AdventureWorks2022;
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
     INNER 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, la surveillance de la classe d'événements Missing Column Statistics à l'aide de SQL Server Profiler indique si 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. Avec de tels scripts, 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. L’affichage catalogue du système 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.

À quel moment doit-on mettre à jour les statistiques ?

L’optimiseur de requête détermine quand les statistiques peuvent être obsolètes, puis les met à jour quand elles sont nécessaires pour 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. 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 manuellement les statistiques trop fréquemment, car il existe un compromis entre l’amélioration des plans de requête et le temps nécessaire pour recompiler les 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, il est recommandé de laisser l'option AUTO_UPDATE_STATISTICS définie sur 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 mise à jour des statistiques sur une colonne, un index, une table ou une vue indexée, consultez UPDATE STATISTICS.

  • 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.

  • Pour plus d’informations sur les seuils de mise à jour automatique des statistiques, consultez Option AUTO_UPDATE_STATISTICS.

Lorsqu’elle AUTO_UPDATE_STATISTICS est définie sur OFF(Désactivé), la recompilation de plan peut toujours avoir lieu pour diverses autres raisons, mais ne s'effectue pas automatiquement en raison de mises à jour des statistiques obsolètes. Lorsqu’elle AUTO_UPDATE_STATISTICS est définie sur OFF, les mises à jour des statistiques se produisent uniquement via d’autres processus planifiés manuellement, tels que les plans de maintenance. Si AUTO_UPDATE_STATISTICS a la valeur OFF, il peut en résulter des plans de requête non optimisés et une dégradation des performances des requêtes.

Détecter les statistiques obsolètes

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.

Pour obtenir des exemples de mise à jour manuelle des statistiques, consultez UPDATE STATISTICS.

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 up-to-date et que les requêtes sont sélectionnées dans les lignes les plus récemment ajoutées, les statistiques actuelles n’ont 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 commandes les plus récentes a des estimations de cardinalité inexactes si les statistiques ne sont pas mises à jour pour inclure des estimations de cardinalité pour les dates de commandes 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 telles que la reconstruction, la défragmentation ou la réorganisation d’un 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 après DBCC INDEXDEFRAG ou ALTER INDEX REORGANIZE opérations.

Tip

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

Gestion automatique des index et des statistiques

Utilisez des 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 la condition De recherche.

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 récupérer la meilleure estimation de cardinalité à partir 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éliorer les 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 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éliorer les 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 lorsque 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. Grâce à 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. Grâce à 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 AdventureWorks2022;
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 AS h, Sales.SalesOrderDetail AS d
    WHERE h.SalesOrderID = d.SalesOrderID
        AND h.OrderDate > @date;
END
GO

    Si le premier appel à la procédure Sales.GetRecentSales stockée passe un NULL pour le @date paramètre, l’optimiseur de requête compile la procédure stockée avec l’estimation de cardinalité pour @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 AdventureWorks2022;
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));
    EXECUTE 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 AS h, Sales.SalesOrderDetail AS d
    WHERE h.SalesOrderID = d.SalesOrderID
        AND h.OrderDate > @date;
END
GO

Améliorer les 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.

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 AdventureWorks2022;
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 AS h, Sales.SalesOrderDetail AS d
    WHERE h.SalesOrderID = d.SalesOrderID AND h.OrderDate > @date
    OPTION (OPTIMIZE FOR (@date = '2004-05-01 00:00:00.000'));
END
GO

Améliorer les estimations de cardinalité au moyen de repères de plan

Pour certaines applications, les instructions de conception de requête peuvent ne pas s’appliquer, car vous ne pouvez pas modifier la requête ou l’indicateur de RECOMPILE requête peut entraîner un trop grand nombre 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.

Dans Azure SQL Database, envisagez d’utiliser les indicateurs du Magasin des requêtes pour forcer les plans, plutôt que les repères de plan. Pour plus d’informations, consultez Indicateurs du Magasin des requêtes.

Contenu connexe

  • Last updated on