Description des repères de plan

SQL Server 2005 introduit la procédure stockée système sp_create_plan_guide pour la création de repères de plan afin d'optimiser les performances des requêtes. Celle-ci peut être utilisée lorsque vous ne pouvez ou ne souhaitez pas modifier directement le texte de la requête. Les repères de plan peuvent être utiles si un petit sous-ensemble de requêtes d'une application de base de données déployée provenant d'un fournisseur tiers ne vous assure pas les performances escomptées. Les repères de plan influencent l'optimisation des requêtes en attachant des indicateurs de requête à ceux-ci. Dans l'instruction sp_create_plan_guide, vous spécifiez la requête que vous voulez optimiser et la clause OPTION contenant les indicateurs de requête à utiliser pour optimiser la requête. Quand cette dernière s'exécute, SQL Server la met en correspondance avec le repère de plan et attache la clause OPTION à la requête au moment de l'exécution.

Remarque :
Les repères de plan peuvent être créés et utilisés uniquement dans les éditions Standard, Developer, Evaluation et Enterprise de SQL Server 2005. Ils peuvent être supprimés dans toutes les éditions.

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.

L'indicateur de requête USE PLAN est un autre indicateur couramment utilisé avec les repères de plan. Il s'emploie si vous savez déjà qu'un plan d'exécution existant peut être substitué à celui sélectionné par l'optimiseur pour une requête particulière en raison de ses performances supérieures. USE PLAN force SQL Server à utiliser un plan de requête particulier, spécifié explicitement dans la syntaxe de l'indicateur, lors de l'exécution de la requête. Un repère de plan s'appliquant à l'indicateur de requête USE PLAN est particulièrement utile quand il est plus pratique d'obtenir un plan d'exécution convenable pour une requête en réécrivant la requête de façon à forcer un ordre de jointure, à utiliser des indicateurs de jointure ou des indicateurs d'index. Pour plus d'informations, consultez Exemple d'application forcée d'un plan : Création d'un repère de plan pour forcer l'application d'un plan obtenu par la réécriture d'une requête.

Pour plus d'informations sur RECOMPILE, OPTIMIZE FOR, USE PLAN et d'autres indicateurs de requête, consultez Indicateur de requête (Transact-SQL).

Attention :
Les repères de plan utilisant 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.

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é. Il s'emploie pour remplacer l'option de base de données PARAMETERIZATION par une classe de requêtes.

Repères de plan OBJECT

Supposons que la procédure stockée suivante, qui prend le paramètre @Country, figure dans une application de base de données déployée dans la base de données AdventureWorks :

CREATE PROCEDURE Sales.GetSalesOrderByCountry (@Country nvarchar(60))
AS
BEGIN
    SELECT *
    FROM Sales.SalesOrderHeader h, Sales.Customer c, 
        Sales.SalesTerritory t
    WHERE h.CustomerID = c.CustomerID
        AND c.TerritoryID = t.TerritoryID
        AND CountryRegionCode = @Country
END

Vous pouvez constater que cette procédure a été compilée et optimisée pour @Country = N'AU' (Australie). Toutefois, peu de commandes émanent de l'Australie. Les performances se dégradent lorsque la requête s'exécute avec des valeurs de paramètre pour des pays générant un plus grand nombre de commandes. Dans la mesure où les États-Unis constituent le pays qui arrive en première position en termes de commandes remportées, un plan de requête généré pour @Country=N'US' donnerait de meilleures performances pour toutes les valeurs possibles du paramètre @Country.

Vous pouvez résoudre ce problème en modifiant la procédure stockée et en ajoutant l'indicateur de requête OPTIMIZE FOR à la requête. Toutefois, étant donné que la procédure stockée se trouve dans une application déployée, vous ne pouvez pas modifier directement le code de cette dernière. Vous pouvez en revanche créer le repère de plan suivant dans la base de données AdventureWorks.

sp_create_plan_guide 
@name = N'Guide1',
@stmt = N'SELECT * FROM Sales.SalesOrderHeader h,
        Sales.Customer c,
        Sales.SalesTerritory t
        WHERE h.CustomerID = c.CustomerID 
            AND c.TerritoryID = t.TerritoryID
            AND CountryRegionCode = @Country',
@type = N'OBJECT',
@module_or_batch = N'Sales.GetSalesOrderByCountry',
@params = NULL,
@hints = N'OPTION (OPTIMIZE FOR (@Country = N''US''))'

Lorsque la requête spécifiée dans l'instruction sp_create_plan_guide s'exécute, elle est modifiée avant l'optimisation de façon à inclure la clause OPTIMIZE FOR (@Country = N''US''), elle aussi spécifiée.

Repères de plan SQL

Les repères de plan SQL s'appliquent aux instructions et aux lots d'instructions fréquemment soumis par une application à l'aide de la procédure stockée système sp_executesql. Imaginons par exemple le lot autonome suivant :

SELECT TOP 1 * FROM Sales.SalesOrderHeader h ORDER BY OrderDate DESC

Pour empêcher la génération du plan d'exécution parallèle sur cette requête, créez le repère de plan suivant :

sp_create_plan_guide 
@name = N'Guide1', 
@stmt = N'SELECT TOP 1 * FROM Sales.SalesOrderHeader h ORDER BY OrderDate DESC',  
@type = N'SQL',
@module_or_batch = NULL, 
@params = NULL, 
@hints = N'OPTION (MAXDOP 1)'

Remarque :
Le lot contenant l'instruction sur laquelle créer un repère de plan ne peut pas contenir d'instruction USE database.

Important :
Les valeurs fournies pour les arguments @module_or_batch et @params de l'instruction sp_create_plan guide doivent concorder avec le texte correspondant tel qu'il a été soumis dans la requête effective. Pour plus d'informations, consultez sp_create_plan_guide (Transact-SQL) et Utilisation du Générateur de profils SQL Server pour créer et tester des repères de plan.

Les repères de plan SQL peuvent également être créés sur des requêtes paramétrées au même format lorsque l'option de base de données PARAMETERIZATION est définie sur FORCED ou lorsqu'un repère de plan TEMPLATE est créé pour spécifier qu'une classe de requête est paramétrée. Pour plus d'informations, consultez Conception de repères de plan pour les requêtes paramétrées.

Repères de plan TEMPLATE

Les repères de plan TEMPLATE sont employés pour remplacer le comportement de paramétrage pour des formats de requête spécifiques. Vous pouvez créer un repère de plan TEMPLATE dans l'une des circonstances suivantes :

• lorsque l'option de base de données PARAMETERIZATION est définie à FORCED, mais qu'il y a des requêtes que vous voulez compiler selon les règles du paramétrage simple ;
• lorsque l'option de base de données PARAMETERIZATION est définie à SIMPLE (l'option par défaut), mais que vous voulez appliquer une tentative de paramétrage forcé à une classe de requêtes.

Pour plus d'informations, consultez Indication du comportement du paramétrage de requêtes grâce aux repères de plan.

Vous pouvez combiner les repères de plan TEMPLATE et les repères de plan SQL. Ainsi, vous pouvez créer un repère de plan TEMPLATE pour vous assurer qu'une classe de requêtes est paramétrée, et ensuite un repère de plan SQL sur la forme paramétrée de cette requête.

Voir aussi

Concepts

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

Autres ressources

Performance des requêtes
sp_create_plan_guide (Transact-SQL)
sp_control_plan_guide (Transact-SQL)
sys.plan_guides

Aide et Informations

Assistance sur SQL Server 2005