Partager via

  • Article

Description des repères de plan

Cette rubrique décrit les repères de plan et explique comment ils peuvent être utilisés pour optimiser la performance 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 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 ou un plan fixe de requête à celles-ci. Dans le repère de plan, vous spécifiez l'instruction Transact-SQL que vous voulez optimiser et une clause OPTION contenant les indicateurs de requête ou un plan de requête spécifique à utiliser pour optimiser la requête. Lorsque la requête s'exécute, SQL Server fait correspondre l'instruction Transact-SQL au repère de plan et attache la clause OPTION à la requête au moment de l'exécution ou fait appel au plan de requête spécifié.

[!REMARQUE]

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

Mise en correspondance des repères de plan et des requêtes

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é. Il s'emploie pour remplacer l'option de base de données PARAMETERIZATION par une classe de requêtes. Pour plus d'informations, consultez Paramétrage simple et Paramétrage forcé.

Pour plus d'informations, consultez Mise en correspondance par SQL Server des repères de plan avec les requêtes.

Repères de plan OBJECT

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

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

Supposons que cette procédure stockée a été compilée et optimisée pour @Country\_region = N'AU' (Australie). Toutefois, comme il y a relativement peu de commandes client qui proviennent d'Australie, les performances souffrent lorsque la requête s'exécute à l'aide de valeurs de paramètre de pays contenant plus de commandes client. 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\_region = N'US' obtiendrait de meilleures performances pour toutes les valeurs possibles du paramètre @Country\_region.

Vous pouvez résoudre ce problème en modifiant la procédure stockée pour ajouter 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 AS h,
        Sales.Customer AS c,
        Sales.SalesTerritory AS t
        WHERE h.CustomerID = c.CustomerID 
            AND c.TerritoryID = t.TerritoryID
            AND CountryRegionCode = @Country_region',
@type = N'OBJECT',
@module_or_batch = N'Sales.GetSalesOrderByCountry',
@params = NULL,
@hints = N'OPTION (OPTIMIZE FOR (@Country_region = 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'').

Repères de plan SQL

Les repères de plan SQL s'appliquent aux instructions et aux lots Transact-SQL autonomes. Le plus souvent, ces instructions sont soumises par une application à l'aide de la procédure stockée système sp_executesql. Imaginons par exemple le traitement autonome suivant :

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

Pour empêcher la génération d'un plan d'exécution parallèle sur cette requête, créez le repère de plan suivant et affectez à l'indicateur de requête MAXDOP la valeur 1 dans le paramètre @hints.

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

[!REMARQUE]

Le traitement 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 aussi être créés sur des requêtes paramétrées au même format lorsque l'option de base de données PARAMETERIZATION a la valeur 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.

L'exemple suivant crée un repère de plan correspondant à la requête qui paramètre selon une forme donnée, et commande à SQL Server d'imposer le paramétrage de la requête. La syntaxe des deux requêtes suivantes est équivalente, seules leurs valeurs littérales constantes diffèrent.

SELECT * FROM AdventureWorks.Sales.SalesOrderHeader AS h
INNER JOIN AdventureWorks.Sales.SalesOrderDetail AS d 
    ON h.SalesOrderID = d.SalesOrderID
WHERE h.SalesOrderID = 45639;

SELECT * FROM AdventureWorks.Sales.SalesOrderHeader AS h
INNER JOIN AdventureWorks.Sales.SalesOrderDetail AS d 
    ON h.SalesOrderID = d.SalesOrderID
WHERE h.SalesOrderID = 45640;

Voici le repère de plan pour la forme paramétrée de la requête :

EXEC sp_create_plan_guide 
    @name = N'TemplateGuide1',
    @stmt = N'SELECT * FROM AdventureWorks.Sales.SalesOrderHeader AS h
              INNER JOIN AdventureWorks.Sales.SalesOrderDetail AS d 
                  ON h.SalesOrderID = d.SalesOrderID
              WHERE h.SalesOrderID = @0',
    @type = N'TEMPLATE',
    @module_or_batch = NULL,
    @params = N'@0 int',
    @hints = N'OPTION(PARAMETERIZATION FORCED)';

Dans l'exemple précédent, la valeur du paramètre @stmt correspond à la forme paramétrée de la requête. La procédure stockée système sp_get_query_template est la seule méthode fiable pour obtenir cette valeur et pouvoir l'utiliser dans sp_create_plan_guide. Vous pouvez utiliser le script suivant à la fois pour obtenir la requête paramétrée et créer ensuite un repère de plan à partir de celle-ci.

DECLARE @stmt nvarchar(max);
DECLARE @params nvarchar(max);
EXEC sp_get_query_template 
    N'SELECT * FROM AdventureWorks.Sales.SalesOrderHeader AS h
      INNER JOIN AdventureWorks.Sales.SalesOrderDetail AS d 
          ON h.SalesOrderID = d.SalesOrderID
      WHERE h.SalesOrderID = 45639;',
    @stmt OUTPUT, 
    @params OUTPUT
EXEC sp_create_plan_guide N'TemplateGuide1', 
    @stmt, 
    N'TEMPLATE', 
    NULL, 
    @params, 
    N'OPTION(PARAMETERIZATION FORCED)';

Important

La valeur des littéraux constants du paramètre @stmt transmise à sp_get_query_template peut affecter le type de données choisi pour le paramètre qui remplace le littéral. Ceci va également affecter la mise en correspondance du repère de plan. Vous devrez peut-être créer plusieurs repères de plan pour traiter plusieurs plages de valeurs.

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 créer un repère de plan SQL sur la forme paramétrée de cette requête.

Application d'un plan de requête fixe à un repère de plan

Vous pouvez appliquer un plan de requête fixe à un repère de plan de type OBJET ou SQL. 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.

L'exemple suivant crée un repère de plan pour une instruction SQL ad hoc simple. Le plan de requête souhaité pour cette instruction est fourni dans le repère de plan en spécifiant directement le plan d'exécution XML pour la requête dans le paramètre @hints . L'exemple exécute en premier l'instruction SQL pour générer un plan dans le cache de plan. Pour les besoins de cet exemple, il est supposé que le plan généré est le plan souhaité et qu'aucune analyse de requête supplémentaire n'est requise. Le plan d'exécution XML pour la requête s'obtient en interrogeant les vues de gestion dynamique sys.dm_exec_query_stats, sys.dm_exec_sql_text et sys.dm_exec_text_query_plan et est assigné à la variable @xml\_showplan. La variable @xml\_showplan est ensuite transmise à l'instruction sp_create_plan_guide dans le paramètre @hints. Vous pouvez aussi créer un repère de plan à partir d'un plan de requête dans le cache des plans à l'aide de la procédure stockée sp_create_plan_guide_from_handle.

USE AdventureWorks;
GO
SELECT City, StateProvinceID, PostalCode FROM Person.Address ORDER BY PostalCode DESC;
GO
DECLARE @xml_showplan nvarchar(max);
SET @xml_showplan = (SELECT query_plan
    FROM sys.dm_exec_query_stats AS qs 
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
    CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle, DEFAULT, DEFAULT) AS qp
    WHERE st.text LIKE N'SELECT City, StateProvinceID, PostalCode FROM Person.Address ORDER BY PostalCode DESC;%');

EXEC sp_create_plan_guide 
    @name = N'Guide1_from_XML_showplan', 
    @stmt = N'SELECT City, StateProvinceID, PostalCode FROM Person.Address ORDER BY PostalCode DESC;', 
    @type = N'SQL',
    @module_or_batch = NULL, 
    @params = NULL, 
    @hints = @xml_showplan;
GO

Validation des repères de plan après la mise à niveau

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 et peut ne pas être le meilleur choix. Après avoir mis à niveau une base de données vers SQL Server 2008, nous recommandons d'effectuer les tâches suivantes :