Article
03/31/2012

Conception et implémentation des repères de plan

Vous pouvez utiliser les repères de plan pour optimiser la performance des requêtes lorsque vous ne pouvez pas ou ne souhaitez pas modifier directement le texte de la requête. Les repères de plan influencent l'optimisation des requêtes en attachant des indicateurs de requête ou un plan fixe de requête à celles-ci. Les repères de plan peuvent être créés de façon à correspondre à des requêtes exécutées dans les contextes suivants :

Un repère de plan OBJECT correspond à des requêtes qui s'exécutent dans le contexte de procédures stockées Transact-SQL, de fonctions scalaires définies par l'utilisateur, de fonctions table à instructions multiples définies par l'utilisateur et de déclencheurs DML.
Un repère de plan SQL correspond à des requêtes qui s'exécutent dans le contexte de lots et d'instructions Transact-SQL autonomes ne faisant pas partie d'un objet de base de données. Les repères de plan SQL peuvent également être employés pour les requêtes paramétrées au format spécifié.
Un repère de plan TEMPLATE correspond à des requêtes autonomes paramétrées au format spécifié. Ces repères de plan s'emploient pour substituer l'option actuelle SET de base de données PARAMETERIZATION d'une base de données par une classe de requêtes.

Pour plus d'informations, consultez Description des repères de plan.

Le nombre total de repères de plan que vous pouvez créer est uniquement tributaire des ressources système disponibles. Toutefois, les repères de plan doivent se limiter au traitement des requêtes critiques ciblées à des fins d'amélioration ou de stabilisation des performances. Les repères de plan ne doivent pas influencer la majeure partie de la charge de requête d'une application déployée.

Il est recommandé de réévaluer et de tester les définitions des repères de plan lorsque vous mettez à niveau votre application vers une nouvelle version de SQL Server. Les contraintes liées au paramétrage des performances et le comportement de la mise en correspondance des repères de plan peuvent changer. Même si un repère de plan non valide n'entraîne pas l'échec d'une requête, le plan est compilé sans utiliser le repère de plan. Après avoir mis à niveau une base de données, nous recommandons d'effectuer les tâches suivantes pour valider des repères de plan existants à l'aide de la fonction sys.fn_validate_plan_guide. Vous pouvez aussi surveiller les repères de plan non valides en utilisant l'événement Plan Guide Unsuccessful dans SQL Server Profiler.

Notes

Les repères de plan sont réservés uniquement aux éditions Standard, Developer, Evaluation et Enterprise de SQL Server ; en revanche, ils sont visibles dans n'importe quelle édition. En outre, vous pouvez attacher une base de données qui contient des repères de plan à n'importe quelle édition. Les repères de plan demeurent intacts lorsque vous restaurez ou attachez une base de données à une version mise à niveau de SQL Server.

Attacher des indicateurs de requête à un repère de plan

Toute combinaison d'indicateurs de requête valides peut être utilisée dans un repère de plan. Lorsqu'un repère de plan correspond à une requête, la clause OPTION spécifiée dans la clause des indicateurs d'un repère de plan est ajoutée à la requête avant qu'elle ne soit compilée et optimisée. Si une requête mise en correspondance avec un repère de plan possède déjà une clause OPTION, les indicateurs de requête spécifiés dans le repère de plan remplacent ceux existant dans la requête. Toutefois, pour qu'un repère de plan corresponde à une requête possédant déjà une clause OPTION, vous devez inclure la clause OPTION de la requête lorsque vous spécifiez le texte de la requête avec laquelle établir la correspondance dans l'instruction sp_create_plan_guide. Si vous souhaitez que les indicateurs spécifiés dans le repère de plan soient ajoutés aux indicateurs qui existent déjà sur la requête, au lieu de les remplacer, vous devez spécifier les indicateurs d'origine et les indicateurs supplémentaires dans la clause OPTION du repère de plan.

Attention
Les repères de plan qui utilisent les indicateurs de requête à mauvais escient peuvent entraîner des problèmes de compilation, d'exécution ou de performances. Les repères de plan doivent être utilisés uniquement par des administrateurs de base de données et des développeurs expérimentés.

Indicateurs de requêtes courants utilisés dans les repères de plan

Les requêtes qui peuvent tirer parti des repères de plan sont généralement les requêtes paramétrées et celles qui peuvent présenter des performances médiocres parce qu'elles utilisent des plans de requête en cache dont les valeurs de paramètre ne représentent pas un scénario pessimiste ou le plus représentatif. Les indicateurs de requête OPTIMIZE FOR et RECOMPILE peuvent être employés pour résoudre ce problème. OPTIMIZE FOR prescrit à SQL Server d'utiliser une valeur particulière pour un paramètre quand la requête est optimisée. RECOMPILE indique au serveur d'ignorer le plan de requête après exécution, forçant l'optimiseur de requête à recompiler un nouveau plan de requête lors de l'exécution suivante de la même requête. Pour obtenir un exemple, consultez Description des repères de plan.

Vous pouvez aussi spécifier les indicateurs de table INDEX et FORCESEEK en tant qu'indicateurs de requête. Lorsqu'ils sont spécifiés en tant qu'indicateurs de requête, ces indicateurs se comportent comme une table inline ou une astuce de vue. L'indicateur INDEX force l'optimiseur de requête à utiliser uniquement les index spécifiés pour accéder aux données dans la table ou la vue référencée. L'indicateur FORCESEEK force l'optimiseur à utiliser uniquement une opération de recherche d'index pour accéder aux données dans la table ou la vue référencée. Ces indicateurs fournissent des fonctionnalités de repère de plan supplémentaires et vous permettent de mieux contrôler l'optimisation des requêtes qui utilisent le repère de plan. Pour obtenir un exemple, consultez Utilisation des indicateurs de requête INDEX et FORCESEEK dans des repères de plan.

Attacher un plan de requête à un repère de plan

Les repères de plan qui appliquent un plan de requête fixe sont utiles lorsque vous avez connaissance d'un plan d'exécution existant plus performant que celui sélectionné par l'optimiseur pour une requête particulière. Notez que l'application d'un plan fixe à une requête signifie que l'optimiseur de requête ne peut plus adapter le plan de la requête aux modifications apportées aux statistiques et aux index. Si vous envisagez de recourir à des repères de plan qui utilisent des plans de requête fixes, veillez à mettre en balance les avantages de l'application d'un plan fixe et l'impossibilité d'adapter le plan automatiquement à mesure qu'évoluent la distribution des données et les index disponibles.

Vous pouvez attacher un plan de requête spécifique à un repère de plan en spécifiant le plan d'exécution XML du plan dans le paramètre xml_showplan dans l'instruction sp_create_plan_guide ou en spécifiant le descripteur de plan d'un plan mis en cache dans l'instruction sp_create_plan_guide_from_handle. Ces deux méthodes appliquent le plan de requête fixe à la requête ciblée.

Paramétrage de la mise en correspondance du repère de plan

Les repères de plan sont limités à la base de données dans laquelle ils sont créés. Par conséquent, seuls les repères de plan existant dans la base de données qui est active lors de l'exécution d'une requête peuvent être mis en correspondance avec cette requête. Supposons que AdventureWorks2008R2 est la base de données active et que la requête suivante est exécutée :

SELECT FirstName, LastName FROM Person.Person ;

Seuls les repères de plan de la base de données AdventureWorks2008R2 peuvent être mis en correspondance avec cette requête.

Supposons maintenant que AdventureWorks2008R2 est la base de données active et que les instructions suivantes sont exécutées :

USE DB1 ;

SELECT FirstName, LastName FROM Person.Person ;

Seuls les repères de plan de DB1 peuvent être mis en correspondance avec la requête car celle-ci s'exécute dans le contexte de DB1.

Pour les repères de plan basés sur SQL ou TEMPLATE, SQL Server fait correspondre les valeurs pour les arguments @module_or_batch et @params avec une requête en comparant les deux valeurs caractère par caractère. Cela signifie que vous devez fournir le texte exactement tel que SQL Server le reçoit dans le traitement réel.

Lorsque @type = 'SQL' et @module_or_batch a la valeur NULL, la valeur de @module_or_batch a la valeur de @stmt. Autrement dit, la valeur de statement_text doit être fournie exactement dans le même format, au caractère près, que lors de sa soumission à SQL Server. Aucune conversion interne n'est effectuée pour faciliter cette correspondance.

En règle générale, vous devez tester les repères de plan à l'aide de SQL Server Profiler pour vérifier que la requête correspond au repère de plan. Le fait de tester des repères de plan basés sur SQL ou TEMPLATE en exécutant des traitements à partir de SQL Server Management Studio peut donner des résultats inattendus. Pour plus d'informations, consultez Utilisation du Générateur de profils SQL Server pour créer et tester des repères de plan.

Notes

Le traitement qui contient l'instruction sur laquelle vous souhaitez créer un repère de plan ne peut pas contenir une instruction USE database.

Effet du repère de plan sur le cache du plan

La création d'un repère de plan sur un module supprime le plan de requête pour ce module du cache du plan. La création d'un repère de plan de type OBJET ou SQL sur un lot supprime le plan de requête pour un lot qui a la même valeur de hachage. La création d'un repère de plan de type TEMPLATE supprime tous les lots comprenant une instruction unique du cache du plan dans cette base de données.