sp_create_plan_guide (Transact-SQL)

S’applique à :SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

Crée un repère de plan permettant d'associer des indicateurs de requête ou des plans de requête réels à des requêtes d'une base de données. Pour plus d'informations sur les repères de plan, consultez Plan Guides.

Conventions de la syntaxe Transact-SQL

Syntaxe

sp_create_plan_guide [ @name = ] N'plan_guide_name'  
    , [ @stmt = ] N'statement_text'  
    , [ @type = ] N'{ OBJECT | SQL | TEMPLATE }'  
    , [ @module_or_batch = ]  
      {   
                    N'[ schema_name. ] object_name'  
        | N'batch_text'  
        | NULL  
      }  
    , [ @params = ] { N'@parameter_name data_type [ ,...n ]' | NULL }   
    , [ @hints = ] { N'OPTION ( query_hint [ ,...n ] )'   
                 | N'XML_showplan'  
                 | NULL }  

Arguments

[ @name = ] N’plan_guide_name'
Nom du repère de plan. Les noms des repères de plan sont limités à la base de données active. plan_guide_name doivent respecter les règles relatives aux identificateurs et ne peuvent pas commencer par le signe numéro (#). La longueur maximale de plan_guide_name est de 124 caractères.

[ @stmt = ] N’statement_text'
Instruction Transact-SQL sur laquelle créer un repère de plan. Lorsque l’optimiseur de requête SQL Server reconnaît une requête qui correspond à statement_text, plan_guide_name prend effet. Pour que la création d’un repère de plan réussisse, statement_text doivent apparaître dans le contexte spécifié par les paramètres @type, @module_or_batch et @params.

statement_text doivent être fournis d’une manière qui permet à l’optimiseur de requête de la mettre en correspondance avec l’instruction correspondante fournie dans le lot ou le module identifié par @module_or_batch et @params. Pour plus d'informations, consultez la section « Notes ». La taille de statement_text est limitée uniquement par la mémoire disponible du serveur.

[@type = ] N'{ OBJECT | SQL | MODÈLE }'
Type d’entité dans laquelle statement_text apparaît. Spécifie le contexte pour la mise en correspondance des statement_text à plan_guide_name.

OBJECT
Indique statement_text apparaît dans le contexte d’une procédure stockée Transact-SQL, d’une fonction scalaire, d’une fonction table multi-états ou d’un déclencheur Transact-SQL DML dans la base de données active.

SQL
Indique statement_text apparaît dans le contexte d’une instruction ou d’un lot autonome qui peut être envoyé à SQL Server via n’importe quel mécanisme. Les instructions Transact-SQL soumises par des objets CLR (Common Language Runtime) ou des procédures stockées étendues, ou à l’aide de EXEC N’sql_string', sont traitées en tant que lots sur le serveur et, par conséquent, doivent être identifiées comme @type = « SQL ». Si SQL est spécifié, l'indicateur de requête PARAMETERIZATION { FORCED | SIMPLE } ne peut pas être spécifié dans le paramètre @hints.

TEMPLATE
Indique que le repère de plan s’applique à toute requête paramétrable au formulaire indiqué dans statement_text. Si le type TEMPLATE est spécifié, seul l'indicateur de requête PARAMETERIZATION { FORCED | SIMPLE } peut être spécifié dans le paramètre @hints. Pour plus d’informations sur les repères de plan TEMPLATE, consultez Spécifier le comportement de paramétrage de requête à l’aide de repères de plan.

[@module_or_batch =] { N'[ schema_name. ] object_name' | N’batch_text' | NULL }
Spécifie le nom de l’objet dans lequel statement_text apparaît, ou le texte de lot dans lequel statement_text apparaît. Le texte du lot ne peut pas inclure une instructionde base de données USE.

Pour qu’un repère de plan corresponde à un lot envoyé à partir d’une application, batch_text doit être fourni dans le même format, caractère par caractère, qu’il est soumis à SQL Server. Aucune conversion interne n'est effectuée pour faciliter cette correspondance. Pour plus d'informations, consultez la section Notes.

[schema_name.] object_name spécifie le nom d’une procédure stockée Transact-SQL, d’une fonction scalaire, d’une fonction table multiétatique ou d’un déclencheur DML Transact-SQL qui contient statement_text. Si schema_name n’est pas spécifié, schema_name utilise le schéma de l’utilisateur actuel. Si NULL est spécifié et @type = 'SQL', la valeur de @module_or_batch est définie sur la valeur de @stmt. Si @type = 'TEMPLATE**'*, @module_or_batch doit avoir la valeur NULL.

[ @params = ] { N'@parameter_name data_type [ ,... n ]' | NULL }
Spécifie les définitions de tous les paramètres incorporés dans statement_text. @params s'applique uniquement lorsque l'une des conditions suivantes est remplie :

• @type = 'SQL' ou 'TEMPLATE'. Si 'TEMPLATE', @params ne doit pas avoir la valeur NULL.

• statement_text est envoyé à l’aide sp_executesql de et une valeur pour le paramètre @params est spécifiée, ou SQL Server envoie en interne une instruction après l’avoir paramétrée. La soumission de requêtes paramétrables à partir d’API de base de données (notamment ODBC, OLE DB et ADO.NET) semblent SQL Server en tant qu’appels vers sp_executesql ou vers des routines de curseur de serveur d’API ; par conséquent, elles peuvent également être mises en correspondance par des repères de plan SQL ou TEMPLATE.

@parameter_name data_type doivent être fournis dans le même format que celui soumis à SQL Server à l’aide sp_executesql de ou en interne après le paramétrage. Pour plus d'informations, consultez la section Notes. Si le traitement ne contient aucun paramètre, la valeur NULL doit être spécifiée. La taille de @params n'est limitée que par la quantité de mémoire disponible sur le serveur.

[@hints = ] { N’OPTION (query_hint [ ,... n ] )' | N’XML_showplan' | NULL }
N’OPTION (query_hint [ ,... n ] )
Spécifie une clause OPTION à attacher à une requête qui correspond à @stmt. @hints doit être syntaxiquement identique à une clause OPTION dans une instruction SELECT et peut contenir n’importe quelle séquence valide d’indicateurs de requête.

N’XML_showplan'
Plan de requête dans le format XML à appliquer comme un indicateur.

Nous vous recommandons d'assigner le plan d'exécution de requêtes XML à une variable ; sinon, vous devez isoler tout guillemet simple dans le plan d'exécution en le faisant précéder par un autre guillemet simple. Voir l'exemple E.

NULL
Indique qu'aucun indicateur existant spécifié dans la clause OPTION de la requête n'est appliqué à la requête. Pour plus d’informations, consultez CLAUSE OPTION (Transact-SQL).

Notes

Les arguments de sp_create_plan_guide doivent être indiqués dans l'ordre affiché. Quand vous fournissez des valeurs pour les paramètres de sp_create_plan_guide, tous les noms de paramètres doivent être spécifiés explicitement, ou aucun nom ne doit être spécifié. Par exemple, si @name = est spécifié, @stmt = , @type =, et ainsi de suite, doivent l’être aussi. De même, si @name = est omis et que seule la valeur du paramètre est indiquée, les noms de paramètres restants doivent également être omis, et seules leurs valeurs doivent être indiquées. Les noms d'arguments sont utilisés à des fins descriptives uniquement, pour une meilleure compréhension de la syntaxe. SQL Server ne vérifie pas que le nom du paramètre spécifié correspond au nom du paramètre à l’emplacement où le nom est utilisé.

Vous pouvez créer plusieurs repères de plan OBJECT ou SQL pour la même requête et le même lot ou module. Toutefois, un seul repère de plan peut être activé à un moment donné.

Vous ne pouvez pas créer de repère de plan de type OBJECT pour une valeur @module_or_batch qui fait référence à une procédure stockée, une fonction ou un déclencheur DML temporaire ou qui spécifie la clause WITH ENCRYPTION.

Si vous tentez de supprimer ou de modifier une fonction, une procédure stockée ou un déclencheur DML référencé par un repère de plan, qu'il soit activé ou désactivé, une erreur se produit. Une erreur se produit également si vous tentez de supprimer une table dont un des déclencheurs est référencé par un repère de plan.

Exigences de correspondance du Guide de plan

Pour les repères de plan qui spécifient @type = 'SQL' ou @type = 'TEMPLATE' pour correspondre correctement à une requête, les valeurs de batch_text et @parameter_name data_type [,... n ] doivent être fournis exactement dans le même format que leurs équivalents soumis par la demande. Cela signifie que vous devez fournir le texte de lot exactement comme le compilateur SQL Server le reçoit. Pour capturer le texte réel du lot et du paramètre, vous pouvez utiliser SQL Server Profiler. Pour plus d’informations, consultez Utiliser SQL Server Profiler pour créer et tester des repères de plan.

Lorsque @type = 'SQL' et @module_or_batch a la valeur NULL, la valeur de @module_or_batch est définie sur la valeur de @stmt. Cela signifie que la valeur de statement_text doit être fournie exactement dans le même format, caractère par caractère, qu’elle est soumise à SQL Server. Aucune conversion interne n'est effectuée pour faciliter cette correspondance.

Quand SQL Server correspond à la valeur de statement_text à batch_text et @parameter_name data_type [,... n ], ou si @type = **'**OBJECT', au texte de la requête correspondante à l’intérieur de 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 ;

• Commentaires (-- ou /* */).

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

Par exemple, SQL Server pouvez faire correspondre la chaîne N'SELECT * FROM T WHERE a = 10'de statement_text aux batch_text suivantes :

N'SELECT *
FROM T
WHERE a = 10' 

Toutefois, la même chaîne ne serait pas associée à cette batch_text :

N'SELECT * FROM T WHERE b = 10'

SQL Server ignore le retour chariot, le flux de ligne et les caractères d’espace à l’intérieur 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 correspondance est sensible aux espaces vides. 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.

Effet du guide 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.

Autorisations

Pour créer un guide de plan de type OBJECT, nécessite ALTER l’autorisation sur l’objet référencé. Pour créer un guide de plan de type SQL ou TEMPLATE, nécessite une ALTER autorisation sur la base de données active.

Exemples

R. Création d'un repère de plan de type OBJECT pour une requête dans une procédure stockée

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''))';  

B. Création d'un repère de plan de type SQL pour une requête autonome

L’exemple suivant crée un guide de plan pour faire correspondre une requête dans un lot envoyé par une application qui utilise la sp_executesql procédure stockée système.

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)';  

C. Création d'un repère de plan de type TEMPLATE pour la forme paramétrable d'une requête

L’exemple suivant crée un guide de plan qui correspond à n’importe quelle requête paramétrable à un formulaire spécifié et dirige SQL Server pour forcer 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 AdventureWorks2022.Sales.SalesOrderHeader AS h  
INNER JOIN AdventureWorks2022.Sales.SalesOrderDetail AS d   
    ON h.SalesOrderID = d.SalesOrderID  
WHERE h.SalesOrderID = 45639;  
  
SELECT * FROM AdventureWorks2022.Sales.SalesOrderHeader AS h  
INNER JOIN AdventureWorks2022.Sales.SalesOrderDetail AS d   
    ON h.SalesOrderID = d.SalesOrderID  
WHERE h.SalesOrderID = 45640;  

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

EXEC sp_create_plan_guide   
    @name = N'TemplateGuide1',  
    @stmt = N'SELECT * FROM AdventureWorks2022.Sales.SalesOrderHeader AS h  
              INNER JOIN AdventureWorks2022.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étrable 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étrable 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 AdventureWorks2022.Sales.SalesOrderHeader AS h  
      INNER JOIN AdventureWorks2022.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)';  

D. Création d'un repère de plan pour une requête soumise à l'aide d'une requête de curseur API

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 à l’aide de curseurs de serveur d’API. Vous pouvez voir l’appel des routines de curseur du serveur d’API dans SQL Server Profiler traces en affichant l’événement de trace RPC:Starting profiler.

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 AS h INNER JOIN Sales.SalesOrderDetail AS d ON h.SalesOrderID = d.SalesOrderID WHERE h.OrderDate BETWEEN @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étrable, 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 BETWEEN @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.

E. Création d'un repère de plan en obtenant le plan d'exécution de requêtes XML à partir d'un plan mis en cache

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 de requêtes XML pour la requête est obtenu en interrogeant les vues de gestion dynamique sys.dm_exec_query_stats, sys.dm_exec_sql_textet 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 AdventureWorks2022;  
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  

Voir aussi

Repères de plan
sp_control_plan_guide (Transact-SQL)
sys.plan_guides (Transact-SQL)
Procédures stockées du moteur de base de données (Transact-SQL)
Procédures stockées système (Transact-SQL)
sys.dm_exec_sql_text (Transact-SQL)
sys.dm_exec_cached_plans (Transact-SQL)
sys.dm_exec_query_stats (Transact-SQL)
sp_create_plan_guide_from_handle (Transact-SQL)
sys.fn_validate_plan_guide (Transact-SQL)
sp_get_query_template (Transact-SQL)