sp_create_plan_guide (Transact-SQL)
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 Description des repères de plan.
.gif "Icône Lien de rubrique")
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. 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 (#). Le paramètre plan_guide_name a une longueur maximale de 124 caractères.[ @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 réussir à créer un repère de plan, 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 à l'optimiseur de requête de le faire correspondre à l'instruction associée fournie dans le lot ou le module identifié par les paramètres @ module_or_batch et @ params. Pour plus d'informations, consultez la section « Remarques ». 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 de 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 traitement dans lequel statement_text apparaît. Le texte du traitement ne peut pas inclure d'instruction USEdatabase.Pour qu'un repère de plan corresponde à un traitement 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 doit 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 côté serveur d'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 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 à 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.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 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é. 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é.
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.
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]
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. 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.
Paramétrage de la mise en correspondance du repère 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 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.
Effet du repère 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 repère de plan de type OBJECT, il vous faut une autorisation ALTER sur 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_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 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)';
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 peuvent 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 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 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.
E. Création d'un repère de plan en obtenant le plan d'exécution 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 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 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
Voir aussi