Article
03/31/2012

Utilisation des statistiques pour améliorer les performances des requêtes

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.

Cette rubrique traite des concepts et des recommandations répertoriés ci-dessous pour vous permettre d'utiliser efficacement les statistiques d'optimisation de requête :

Que sont les statistiques d'optimisation de requête ?
Utilisation des options de statistiques à l'échelle de la base de données
Quand créer des statistiques
Quand mettre à jour les statistiques
Conception de requêtes pour une utilisation efficace des statistiques

Pour plus d'informations sur le plan de requête et sur son influence sur les performances des requêtes, consultez Analyse d'une requête.

Que sont les statistiques d'optimisation de requête ?

Les statistiques utilisées dans le cadre de l'optimisation de requête sont des objets 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, 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. Pour plus d'informations sur les objets de statistiques, consultez DBCC SHOW_STATISTICS (Transact-SQL).

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. Pour une étude de cas, consultez l'entrée de blog, Using Filtered Statistics with Partitioned Tables (en anglais) sur le site Web SQLCAT.

Utilisation des options de statistiques à l'échelle de la base de données

Il importe de bien comprendre la fonction des options de statistiques à l'échelle de la base de données décrites ci-dessous et de vérifier qu'elles sont correctement définies :

AUTO_CREATE_STATISTICS
AUTO_UPDATE_STATISTICS
AUTO_UPDATE_STATISTICS_ASYNC

Utilisation de l'option AUTO_CREATE_STATISTICS

Lorsque l'option de création automatique de statistiques AUTO_CREATE_STATISTICS est activée, l'optimiseur de requête crée des statistiques sur les colonnes individuelles du prédicat de requête, le cas échéant, pour améliorer les estimations de cardinalité pour le plan de requête. Ces statistiques de colonne unique sont créées sur les colonnes où ne figure pas déjà un histogramme au niveau d'un objet de statistiques existant.

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. Elle interroge les affichages catalogue sys.stats et sys.stats_columns pour retourner le nom d'objet de base de données, le nom de colonne et le nom des statistiques pour toutes les colonnes qui possèdent des statistiques de colonne unique. Lorsque l'optimiseur de requête crée des statistiques sur des colonnes uniques par suite de l'utilisation de l'option AUTO_CREATE_STATISTICS, le nom des statistiques commence par _WA.

USE AdventureWorks;
GO
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 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;

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.

Utilisation de l'option AUTO_UPDATE_STATISTICS

Lorsque 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 et les met éventuellement à jour lorsqu'elles sont utilisées par une requête. Les statistiques deviennent obsolètes après que 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 le nombre de modifications de données depuis la dernière mise à jour des statistiques et en comparant le nombre de modifications à un seuil. Ce seuil est basé sur le nombre de lignes contenues dans la table ou la vue indexée.

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.

Activation des options AUTO_CREATE_STATISTICS et AUTO_UPDATE_STATISTICS

L'option de création automatique de statistiques AUTO_CREATE_STATISTICS et l'option de mise à jour automatique des statistiques AUTO_UPDATE_STATISTICS sont activées par défaut. À ce propos, nous vous recommandons d'utiliser les paramètres par défaut pour la plupart des bases de données utilisateur. Vous pouvez utiliser l'instruction SELECT suivante pour afficher les valeurs actuelles de ces options pour toutes les bases de données utilisateur :

SELECT name AS "Name", 
    is_auto_create_stats_on AS "Auto Create Stats",
    is_auto_update_stats_on AS "Auto Update Stats",
    is_read_only AS "Read Only" 
FROM sys.databases
WHERE database_ID > 4;
GO

L'exemple suivant active les options AUTO_CREATE_STATISTICS et AUTO_UPDATE_STATISTICS (en leur attribuant la valeur ON) pour la base de données AdventureWorks2008R2 :

USE AdventureWorks2008R2;
GO
ALTER DATABASE AdventureWorks2008R2
    SET AUTO_CREATE_STATISTICS ON;
ALTER DATABASE AdventureWorks2008R2
    SET AUTO_UPDATE_STATISTICS ON;
GO

Pour plus d'informations sur la façon de définir ces options de statistiques, consultez Options SET de ALTER DATABASE (Transact-SQL).

Désactivation et réactivation de l'option AUTO_UPDATE_STATISTICS pour certaines statistiques

Lorsque l'option AUTO_UPDATE_STATISTICS est activée, vous pouvez passer outre le comportement de mise à jour des statistiques à l'échelle de la base de données et désactiver la mise à jour automatique des statistiques pour une table individuelle, un index ou une colonne, en fonction des exigences de votre application. Lorsque l'option AUTO_UPDATE_STATISTICS est activée, vous pouvez désactiver et réactiver la mise à jour automatique des statistiques pour une table, un index ou une colonne en procédant de l'une des façons suivantes :

Utilisez la procédure stockée système sp_autostats. Celle-ci peut désactiver ou réactiver la mise à jour des statistiques pour une table ou un index.
Spécifiez l'option NORECOMPUTE avec l'instruction UPDATE STATISTICS. Pour réactiver la mise à jour des statistiques, réexécutez UPDATE STATISTICS sans l'option NORECOMPUTE.
Spécifiez l'option NORECOMPUTE avec l'instruction CREATE STATISTICS. Pour réactiver la mise à jour des statistiques, supprimez les statistiques à l'aide de DROP STATISTICS, puis exécutez CREATE STATISTICS sans l'option NORECOMPUTE.
Spécifiez l'option STATISTICS_NORECOMPUTE avec l'instruction CREATE INDEX. Pour réactiver la mise à jour des statistiques, vous pouvez exécuter ALTER INDEX en désactivant l'option STATISTICS_NORECOMPUTE (valeur OFF).

Lorsque l'option AUTO_UPDATE_STATISTICS est désactivée, vous ne pouvez pas activer la mise à jour automatique pour une table individuelle, un index ou une colonne. La réactivation de la mise à jour automatique des statistiques rétablit le comportement spécifié par l'option AUTO_UPDATE_STATISTICS. Si l'option AUTO_UPDATE_STATISTICS est désactivée, les statistiques ne sont pas mises à jour.

Quand utiliser la mise à jour synchrone ou asynchrone des statistiques

La mise à jour des statistiques peut être synchrone (par défaut) ou asynchrone. Dans le cas de la mise à jour synchrone des statistiques, les requêtes sont systématiquement compilées et exécutées avec des statistiques à jour ; si les statistiques sont obsolètes, l'optimiseur de requête attend que les statistiques soient mises à jour avant de compiler et d'exécuter les requêtes. Dans le cas de la mise à jour asynchrone 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 très bien choisir un plan de requête non optimal si les statistiques sont obsolètes au moment où les requêtes sont compilées. Les requêtes compilées à l'issue d'une mise à jour asynchrone ont l'avantage d'utiliser des statistiques mises à jour.

L'option de mise à jour asynchrone des statistiques à l'échelle de la base de données AUTO_UPDATE_STATISTICS_ASYNC détermine si l'optimiseur de requête utilise des mises à jour de 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 en mode 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.

Vous pouvez utiliser la commande suivante pour afficher l'option de mise à jour automatique asynchrone pour toutes les bases de données :

SELECT name AS "Name", 
    is_auto_update_stats_async_on AS "Asynchronous Update" 
FROM sys.databases;
GO

Envisagez d'utiliser des statistiques synchrones pour le scénario suivant :

Vous effectuez des opérations qui modifient la distribution des données, telles que la troncation d'une table ou l'exécution d'une mise à jour en bloc d'un fort pourcentage de lignes. Si vous ne mettez pas à jour les statistiques à l'issue de l'opération, l'utilisation de statistiques synchrones vous garantira 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. Pour plus d'informations sur la recherche de requêtes similaires, consultez Recherche et paramétrage de requêtes semblables à l'aide de requête et de hachages de plan de requête.
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.

Quand créer des statistiques

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

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 Règles de conception d'index filtrés et CREATE INDEX (Transact-SQL).
L'optimiseur de requête crée des statistiques pour les colonnes uniques des prédicats de requête lorsque 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.

L'Assistant Paramétrage du moteur de base de données suggère de créer des statistiques

L'Assistant Paramétrage du Moteur de base de données est un outil qui analyse l'impact des charges de travail sur les performances d'une ou de plusieurs bases de données. Il fournit des recommandations pour améliorer les performances (par exemple en suggérant la création d'index) et peut suggérer l'utilisation de CREATE STATISTICS pour créer des statistiques d'optimisation de requête. Vous avez tout intérêt à suivre cette recommandation. Pour plus d'informations sur l'Assistant Paramétrage du Moteur de base de données, consultez Paramétrage du modèle physique de la base de données.

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 DBCC SHOW_STATISTICS (Transact-SQL).

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. Par exemple, le code suivant crée un objet de statistiques multicolonnes sur les colonnes LastName, MiddleName et FirstName.

USE AdventureWorks2008R2;
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 à l'aide de l'instruction CREATE STATISTICS avec la clause WHERE pour définir l'expression de prédicat de filtre.

Par exemple, dans AdventureWorks2008R2, chaque produit de la table Production.Product appartient à l'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 AdventureWorks2008R2;
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

Statistiques manquantes pour la requête

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. Pour plus d'informations, consultez Affichage de plans d'exécution graphique (SQL Server Management Studio). D'autre part, le SQL Server Profiler permet de surveiller la classe d'événements Missing Column Statistics et d'être tenu informé lorsque des statistiques manquent. Pour plus d'informations, consultez Catégorie d'événements 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, l'optimiseur de requête ne peut pas enregistrer les statistiques ;
créez les statistiques manquantes à l'aide de l'instruction CREATE STATISTICS.

Quand mettre à jour les 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 de façon plus régulière que lorsque 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). Par exemple, la commande suivante appelle sp_updatestats pour mettre à jour toutes les statistiques de la base de données.

EXEC sp_updatestats

Pour déterminer la date de la dernière mise à jour des statistiques, utilisez la fonction 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. Pour plus d'informations sur le dépannage des requêtes s'exécutant lentement, consultez Liste de vérification pour l'analyse des requêtes longues à s'exécuter.

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.

Ne mettez pas à jour les statistiques à la suite d'opérations de reconstruction, de défragmentation ou de réorganisation d'index, par exemple. Ces opérations ne modifient pas la distribution des données et n'ont aucun impact sur les statistiques. Notez toutefois que l'optimiseur de requête met à jour les statistiques lors de la reconstruction d'un index sur une table ou une vue via ALTER INDEX REBUILD ou DBCC DBREINDEX. Cela est une conséquence de l'implémentation interne de ces opérations et non d'un besoin de mise à jour des statistiques. L'optimiseur de requête ne met pas à jour les statistiques suite à des opérations de défragmentation ou de réorganisation qui ne suppriment ni ne recréent l'index.

Conception de 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.

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) to 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. 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. Pour plus d'informations sur les fonctions table à instructions multiples, consultez Types de fonctions.
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 is NULL.
```
USE AdventureWorks2008R2;
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 AdventureWorks2008R2;
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 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 OPTIMIZER FOR peut s'avérer utile même si vous n'utilisez pas l'option RECOMPILE. Par exemple, vous pouvez ajouter une option OPTIMIZER FOR à la procédure stockée Sales.GetRecentSales pour spécifier une date précise. L'exemple suivant ajoute l'option OPTIMIZER FOR à la procédure Sales.GetRecentSales.

USE AdventureWorks2008R2;
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 matière de conception de requêtes peuvent ne pas s'appliquer, soit parce que vous ne pouvez pas modifier la requête, soit parce que l'utilisation de l'indicateur de requête RECOMPILE peut 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 Optimisation des requêtes dans les applications déployées à l'aide des repères de plan.

Voir aussi

Partager via