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

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.

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.

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.

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

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.

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.

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.

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

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

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.

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.

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.

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.

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.

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
  

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

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.