sp_create_plan_guide (Transact-SQL)

Mis à jour : 15 septembre 2007

Crée un repère de plan permettant d'associer des indicateurs de requête à des requêtes d'une base de données. Pour plus d'informations sur les repères de plan, consultez Optimisation des requêtes dans les applications déployées à l'aide des repères de plan.

Conventions de la syntaxe de 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 ] )' | NULL }

Arguments

• [ @name= ] N'plan_guide_name'
  Spécifie le nom qui identifie le repère de plan. Le nom des repères de plan se limite à la base de données active. Le paramètre plan_guide_name doit se conformer à des règles en matière d'identifiants et ne peut commencer par le signe dièse (#).

• [ @stmt= ] N'statement_text'
  Instruction Transact-SQL permettant de 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 doit apparaître dans le contexte spécifié par les paramètres @type, @module_or_batch et @params.

  statement_text doit être fourni de manière à permettre à SQL Server de le mettre en correspondance avec l'instruction appropriée fournie dans le lot ou le module identifié par les paramètres @module_or_batch et @params. statement_text est converti dans une forme interne standard avant que SQL Server essaie de le mettre en correspondance (les espaces, les commentaires et les mots clés ne sont pas significatifs). Pour plus d'informations, consultez la section Notes. La taille de statement_text est uniquement limitée par la mémoire disponible du serveur.

• [ @type = ] N'{ OBJECT | SQL | TEMPLATE }'
  Type d'entité dans laquelle statement_text apparaît. Indique le contexte de la mise en correspondance de statement_text et plan_guide_name.

  • OBJECT
    Indique que statement_text apparaît dans le contexte d'une procédure stockée Transact-SQL, d'une fonction scalaire, d'une fonction table multi-instructions ou d'un déclencheur DML Transact-SQL dans la base de données active.
  • SQL
    Indique que statement_text apparaît dans le contexte d'une instruction ou d'un lot autonome pouvant être soumis à SQL Server par le biais d'un mécanisme quelconque. Les instructions Transact-SQL soumises par des objets CLR (Common Language Runtime), des procédures stockées étendues ou à l'aide d'EXEC N'sql_string' sont traitées comme des lots sur le serveur et doivent dès lors ê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 qui paramètre selon le 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 Indication du comportement du paramétrage de requêtes grâce aux repères de plan.

• [ @module_or_batch = ] { N'[ schema_name**.** ] object_name**'** | N'batch_text' | NULL }
  Spécifie soit le nom de l'objet dans lequel statement_text apparaît, soit le texte du lot dans lequel statement_text apparaît. Le texte du lot ne peut pas inclure d'instruction USE database.

  Pour qu'un repère de plan corresponde à un lot soumis à partir d'une application, batch_text doit être fourni 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. 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-instructions 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 que @type='SQL', la valeur de @module_or_batch est identique à celle de @stmt. Si @type='TEMPLATE', @module_or_batch doit avoir la valeur NULL.

• [ @params = ] { N'@parameter_name data_type [ ,...n ]' | NULL }
  Spécifie la définition 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 peut avoir la valeur NULL.
  • statement_text est soumis à l'aide de sp_executesql et une valeur est spécifiée pour le paramètre @params ou SQL Server soumet en interne une instruction après l'avoir paramétrée. SQL Server considère la soumission de requêtes paramétrées à partir des API de bases de données (y compris ODBC, OLE DB et ADO.NET) comme des appels à sp_executesql ou aux routines de curseur de serveur API. Par conséquent, elles peuvent également être mises en correspondance par des repères de plan SQL ou TEMPLATE. Pour plus d'informations sur le paramétrage et les repères de plan, consultez Mise en correspondance par SQL Server des repères de plan avec les requêtes.

  @parameter_name data_type doit être fourni exactement dans le même format que celui dans lequel il a été soumis à SQL Server à l'aide de sp_executesql ou dans lequel il a été soumis en interne après le paramétrage. Pour plus d'informations, consultez la section Notes. Si le lot 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 ] )' | NULL }
  Spécifie une clause OPTION à joindre à une requête qui correspond à @stmt. La syntaxe de @hints doit être la même qu'une clause OPTION dans une instruction SELECT. En outre, ce paramètre peut contenir toute séquence valide d'indicateurs de requête. La valeur NULL indique l'absence de clause OPTION. 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é. Lorsque 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 = (etc.) doit 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 de paramètre spécifié correspond au nom du paramètre à l'emplacement où le nom est utilisé.

Un seul repère de plan peut être créé pour une combinaison spécifique @module_or_batch et @stmt.

Vous ne pouvez pas créer des repères 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 qui spécifie que la clause WITH ENCRYPTION est temporaire.

Une erreur se produit si vous tentez de supprimer ou modifier une fonction, une procédure stockée ou un déclencheur DML référencé par un repère de plan actif ou inactif. 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.

Remarque :

Bien que les repères de plan ne puissent être utilisés que dans SQL Server é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 2008. 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.

Spécifications correspondantes des repères de plan

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 lot exactement de la même manière que le compilateur SQL Server l'a reçu. Pour saisir le texte réel du lot et du paramètre, vous pouvez utiliser Générateur de profils SQL Server. 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, @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.

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.

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.

Autorisations

Pour créer un repère de plan de type OBJECT (en spécifiant @type='OBJECT'), vous avez besoin de l'autorisation ALTER pour l'objet référencé. Pour créer un repère de plan de type SQL ou TEMPLATE, il vous faut une autorisation ALTER pour la base de données active.

Exemples

A. 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 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;
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',
    @type = N'OBJECT',
    @module_or_batch = N'Sales.GetSalesOrderByCountry',
    @params = NULL,
    @hints = N'OPTION (OPTIMIZE FOR (@Country = 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 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 lot :

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étrée d'une requête

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 :
Les valeurs littérales constantes du paramètre @stmt transmises à sp_get_query_template pourraient 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.

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.

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 de serveur 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 de serveur API. Pour plus d'informations, consultez Curseurs de serveur pour API. Pour voir l'invocation des routines de curseur de serveur API dans les traces Générateur de profils SQL Server, 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 AdventureWorks.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é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 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.

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.

Voir aussi

Référence

sp_control_plan_guide (Transact-SQL)
sys.plan_guides
Procédures stockées du moteur de base de données (Transact-SQL)
Procédures stockées système (Transact-SQL)

Aide et Informations

Assistance sur SQL Server 2005

Historique des modifications

Version	Historique
5 décembre 2005	Nouveau contenu : Nouvelle mention indiquant que l'indicateur de requête PARAMETERIZATION { FORCED | SIMPLE } ne peut pas être spécifié pour les repères de plan SQL. Dans la section Remarques, les instructions liées à l'harmonisation et à l'ordre des arguments de la syntaxe ont été clarifiées. Contenu modifié : Éclaircissements quant aux repères de plan de type OBJECT qui ne peuvent pas référencer des objets chiffrés ou temporaires.
15 septembre 2007	Contenu mis à jour : Éclaircissements apportés aux spécifications correspondantes des repères de plan pour statement_text lorsque @ type = "SQL" et @ les module_or_batch ont la valeur NULL. Ajout d'informations concernant l'effet de créer des repères de plan sur le cache du plan.