Conception et implémentation des repères de plan

Mis à jour : 15 septembre 2007

Vous pouvez utiliser des repères de plan pour optimiser les performances de requêtes lorsque vous ne pouvez pas ou ne souhaitez pas modifier directement le texte de la requête. Les repères de plan peuvent être créés de façon à correspondre à des requêtes exécutées dans les contextes suivants :

• Repères de plan OBJECT qui correspondent à des requêtes s'exécutant dans le contexte de procédures stockées Transact-SQL, de fonctions scalaires, de fonctions table à instructions multiples et de déclencheurs DML.
• Repères de plan SQL qui correspondent à des requêtes s'exécutant 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é.
• Repères de plan TEMPLATE qui correspondent à des requêtes autonomes paramétrées au format spécifié. Ces repères de plans sont utilisés pour remplacer l'option SET de la base de données PARAMETERIZATION par une classe de requêtes.

Remarque :

Bien que les repères de plan ne puissent être utilisés que dans SQL Server 2005 éditions Standard, Developer, Evaluation et Enterprise, 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 2005. Vous devez vérifier les avantages des repères de plan dans chaque base de données après avoir réalisé une mise à niveau de serveur.

Pour les repères de plan basés sur SQL ou TEMPLATE qui spécifient @type = N'SQL' ou @type = N'TEMPLATE' dans l'instruction sp_create_plan_guide, SQL Server fait correspondre les valeurs des 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 lot réel. Pour capturer le texte du lot réel, vous pouvez utiliser le Générateur de profils SQL Server. En règle générale, vous devez tester les repères de plan à l'aide de Générateur de profils SQL Server 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 lots à 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.

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

Lorsque @type = 'SQL' et @module\_or\_batch a la valeur NULL, @module\_or\_batch a la valeur @stmt. Cela signifie que la valeur de statement_text doit être fournie dans exactement le même format, au caractère près, que lorsqu'elle soumise à SQL Server. Aucune conversion interne n'est effectuée pour faciliter cette correspondance.

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 AdventureWorks est la base de données active et que la requête suivante est exécutée :

SELECT * FROM Person.Contact

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

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

USE DB1;
GO
SELECT * FROM Person.Contact;

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.

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

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 être utilisés en petite quantité afin de traiter uniquement des requêtes spécifiques, 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. En particulier, les repères de plan qui recourent à l'indicateur de requête USE PLAN appliquent un plan fixe pour la requête ciblée. Par conséquent, 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 la requête USE PLAN, 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.

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.

Effet des repères de plan sur le cache du plan

La création d'un repère de plan sur un module supprime du cache du plan le plan de requête de ce module. La création d'un repère de plan de type OBJECT ou SQL sur un lot supprime le plan de requête d'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 à une seule instruction du cache du plan dans cette base de données.

Pour créer un repère de plan

• sp_create_plan_guide (Transact-SQL)

Pour désactiver, réactiver ou supprimer des repères de plan

• sp_control_plan_guide (Transact-SQL)

Pour obtenir des informations sur les repères de plan de la base de données active

• sys.plan_guides

Voir aussi

Concepts

Optimisation des requêtes dans les applications déployées à l'aide des repères de plan

Autres ressources

Performance des requêtes

Aide et Informations

Assistance sur SQL Server 2005

Historique des modifications