sp_create_plan_guide (Transact-SQL)

Pour que les repères de plan qui spécifient @type = 'SQL' ou @type = 'TEMPLATE correspondent à une requête, les valeurs de batch_text et @parameter_name data_type [,...n ] doivent être fournies exactement dans le même format que leurs homologues soumis par l'application. Par conséquent, vous devez indiquer le texte du traitement exactement de la même manière que le compilateur SQL Server l'a reçu. Pour saisir le texte réel du traitement et du paramètre, vous pouvez utiliser SQL Server Profiler. Pour plus d'informations, consultez Utilisation du Générateur de profils SQL Server pour créer et tester des repères de plan.

Lorsque @type = 'SQL' et @module_or_batch ont 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.

Lorsque SQL Server fait correspondre la valeur de statement_text à batch_text et @parameter_name data_type [,...n ], ou si @type = **'**OBJECT', au texte de la requête correspondante dans object_name, les éléments de chaîne suivants ne sont pas pris en compte :

• les espaces (tabulations, espaces, retours chariot ou sauts de ligne) à l'intérieur de la chaîne ;

• les commentaires (-- ou /* */) ;

• les points-virgules situés à la fin.

Par exemple, SQL Server peut faire correspondre la chaîne statement_textN'SELECT * FROM T WHERE a = 10' au batch_text suivant :

N'SELECT *

FROM T

WHERE a=10'

En revanche, la même chaîne ne serait pas mise en correspondance avec ce batch_text :

N'SELECT * FROM T WHERE b = 10'

SQL Server ignore les retours chariots, les sauts de lignes et les espaces au sein de la première requête. Dans la seconde, la séquence WHERE b = 10 est interprétée différemment de WHERE a = 10. La mise en correspondance respecte la casse et les accents (même lorsque le classement de la base de données ne respecte pas la casse), sauf pour les mots clés, pour lesquels la casse n'est pas respectée. La mise en correspondance n'est en outre pas sensible aux formes abrégées des mots clés. Par exemple, les mots clés EXECUTE, EXEC et execute sont considérés comme équivalents.

Pour plus d'informations sur la mise en correspondance des repères de plan et des requêtes, consultez Optimisation des requêtes dans les applications déployées à l'aide des repères de 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.

L'exemple ci-après crée un repère de plan qui correspond à une requête exécutée dans le contexte d'une procédure stockée basée sur une application et applique l'indicateur OPTIMIZE FOR à la requête.

Voici la procédure stockée :

IF OBJECT_ID(N'Sales.GetSalesOrderByCountry', N'P') IS NOT NULL
    DROP PROCEDURE Sales.GetSalesOrderByCountry;
GO
CREATE PROCEDURE Sales.GetSalesOrderByCountry 
    (@Country_region nvarchar(60))
AS
BEGIN
    SELECT *
    FROM Sales.SalesOrderHeader AS h 
    INNER JOIN Sales.Customer AS c ON h.CustomerID = c.CustomerID
    INNER JOIN Sales.SalesTerritory AS t 
        ON c.TerritoryID = t.TerritoryID
    WHERE t.CountryRegionCode = @Country_region;
END
GO

Et voici le repère de plan créé pour la requête dans la procédure stockée :

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

L'exemple suivant crée un repère de plan à mettre en correspondance avec une requête dans un lot soumis par une application qui utilise la procédure stockée système sp_executesql.

Voici le traitement :

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

Pour éviter la génération d'une exécution parallèle pour cette requête, créez le repère de plan suivant :

EXEC sp_create_plan_guide 
    @name = N'Guide1', 
    @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)';

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 AdventureWorks2008R2.Sales.SalesOrderHeader AS h
INNER JOIN AdventureWorks2008R2.Sales.SalesOrderDetail AS d 
    ON h.SalesOrderID = d.SalesOrderID
WHERE h.SalesOrderID = 45639;

SELECT * FROM AdventureWorks2008R2.Sales.SalesOrderHeader AS h
INNER JOIN AdventureWorks2008R2.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 AdventureWorks2008R2.Sales.SalesOrderHeader AS h
              INNER JOIN AdventureWorks2008R2.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 AdventureWorks2008R2.Sales.SalesOrderHeader AS h
      INNER JOIN AdventureWorks2008R2.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)';

Pour plus d'informations sur la façon d'obtenir la forme paramétrée d'une requête à utiliser dans un repère de plan basé sur TEMPLATE, consultez Conception de repères de plan pour les requêtes paramétrées.

Les repères de plan peuvent correspondre à des requêtes soumises à partir de routines de curseur côté serveur d'API. Ces routines incluent sp_cursorprepare, sp_cursorprepexec et sp_cursoropen. Les applications qui utilisent les API ADO, OLE DB et ODBC interagissent fréquemment avec SQL Server grâce à l'utilisation de curseurs côté serveur d'API. Pour plus d'informations, consultez Curseurs de serveur pour API. Pour voir l'invocation des routines de curseur côté serveur d'API dans les traces SQL Server Profiler, il vous suffit de voir l'événement de trace du générateur de profils RPC:Starting.

Supposez que les données suivantes apparaissent dans un événement de trace du générateur de profils RPC:Starting pour une requête que vous souhaitez ajuster au moyen d'un repère de plan :

DECLARE @p1 int;
SET @p1=-1;
DECLARE @p2 int;
SET @p2=0;
DECLARE @p5 int;
SET @p5=4104;
DECLARE @p6 int;
SET @p6=8193;
DECLARE @p7 int;
SET @p7=0;
EXEC sp_cursorprepexec @p1 output,@p2 output,N'@P1 varchar(255),@P2 varchar(255)',N'SELECT * FROM Sales.SalesOrderHeader h INNER JOIN AdventureWorks2008R2.Sales.SalesOrderDetail AS d ON h.SalesOrderID = d.SalesOrderID WHERE h.OrderDate >= @P1 AND <= @P2',@p5 OUTPUT,@p6 OUTPUT,@p7 OUTPUT,'20040101','20050101'
SELECT @p1, @p2, @p5, @p6, @p7;

Vous constatez que le plan de la requête SELECT dans l'appel à sp_cursorprepexec utilise une jointure de fusion mais vous souhaitez utiliser une jointure de hachage. La requête soumise à l'aide de sp_cursorprepexec est paramétrée, y compris une chaîne de requête et une chaîne de paramètre. Vous pouvez créer le repère de plan suivant pour choisir un autre plan en utilisant les chaînes de requête et de paramètre exactement comme elles apparaissent, au caractère près, dans l'appel à sp_cursorprepexec.

EXEC sp_create_plan_guide 
    @name = N'APICursorGuide',
    @stmt = N'SELECT * FROM Sales.SalesOrderHeader AS h 
              INNER JOIN Sales.SalesOrderDetail AS d 
                ON h.SalesOrderID = d.SalesOrderID 
              WHERE h.OrderDate >= @P1 AND <= @P2',
    @type = N'SQL',
    @module_or_batch = NULL,
    @params = N'@P1 varchar(255),@P2 varchar(255)',
    @hints = N'OPTION(HASH JOIN)';

Les exécutions ultérieures de cette requête seront affectées par ce repère de plan et une jointure de hachage sera utilisée pour traiter la requête.

Pour plus d'informations sur l'utilisation de l'indicateur de requête USE PLAN dans un repère de plan pour une requête soumise à l'aide d'un curseur, consultez Utilisation de l'indicateur de requête USE PLAN sur les requêtes avec curseurs.

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 du 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 est obtenu 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 AdventureWorks2008R2;
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