Partager via

sp_create_plan_guide (Transact-SQL)

  • Article

S’applique à : SQL Server Azure SQL Database Azure 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'name'
    [ , [ @stmt = ] N'stmt' ]
    , [ @type = ] { N'OBJECT' | N'SQL' | N'TEMPLATE' }
    [ , [ @module_or_batch = ] { N' [ schema_name. ] object_name' | N'batch_text' } ]
    [ , [ @params = ] N'@parameter_name data_type [ ,... n ]' ]
    [ , [ @hints = ] { N'OPTION ( query_hint [ , ...n ] )' | N'XML_showplan' } ]
[ ; ]

Arguments

[ @name = ] N’name'

Nom du repère de plan. @name est sysname, sans valeur par défaut, et une longueur maximale de 124 caractères. Les noms des repères de plan sont limités à la base de données active. @name devez respecter les règles relatives aux identificateurs et ne peut pas commencer par le signe numérique (#).

[ @stmt = ] N’stmt'

Instruction Transact-SQL sur laquelle créer un repère de plan. @stmt est nvarchar(max), avec la valeur par défaut NULL. Lorsque l’optimiseur de requête SQL Server reconnaît une requête qui correspond à @stmt, @name prend effet. Pour que la création d’un repère de plan réussisse, @stmt doit apparaître dans le contexte spécifié par les paramètres @type, @module_or_batch et @params .

@stmt doit être fourni 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 Remarques. La taille de @stmt est limitée uniquement par la mémoire disponible du serveur.

[ @type = ] { N’OBJECT' | N’SQL' | N’TEMPLATE' }

Type d’entité dans laquelle @stmt apparaît. Cela spécifie le contexte de correspondance @stmt à @name. @type est nvarchar(60) et peut être l’une des valeurs suivantes :

  • OBJECT

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

  • SQL

    Indique @stmt apparaît dans le contexte d’une instruction autonome ou d’un lot pouvant être soumis à 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 en utilisant EXEC N'<sql_string>', sont traitées en tant que lots sur le serveur et, par conséquent, doivent être identifiées comme @type de SQL. Si SQL elle est spécifiée, l’indicateur PARAMETERIZATION { FORCED | SIMPLE } de requête 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 @stmt. Si TEMPLATE elle est spécifiée, seul l’indicateur PARAMETERIZATION { FORCED | SIMPLE } de requête peut être spécifié dans le paramètre @hints . Pour plus d’informations sur les repères de TEMPLATE plan, consultez Spécifier le comportement de paramétrage des requêtes à l’aide des repères de plan.

[ @module_or_batch = ] { N'[ schema_name. ] object_name' | N’batch_text' }

Spécifie le nom de l’objet dans lequel @stmt apparaît, ou le texte de lot dans lequel @stmt apparaît. @module_or_batch est nvarchar(max), avec la valeur par défaut NULL. Le texte de lot ne peut pas inclure d’instruction USE <database> .

Pour qu’un guide de plan corresponde à un lot envoyé à partir d’une application, @module_or_batch doit être fourni dans le même format, caractère pour 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 Remarques.

[ <schema_name>. ] <object_name> spécifie le nom d’une procédure stockée Transact-SQL, d’une fonction scalaire, d’une fonction table à états multiples ou d’un déclencheur DML Transact-SQL qui contient @stmt. Si <schema_name> ce n’est pas spécifié, <schema_name> utilise le schéma de l’utilisateur actuel. Si NULL elle est spécifiée et @type est SQL, la valeur de @module_or_batch est définie sur la valeur de @stmt. Si @type est TEMPLATE, @module_or_batch doit être NULL.

[ @params = ] N'@parameter_name data_type [ ,... n ]'

Spécifie les définitions de tous les paramètres incorporés dans @stmt. @params est nvarchar(max), avec la valeur par défaut NULL. @params s’applique uniquement lorsque l’une des options suivantes est true :

  • @type est SQL ou TEMPLATE. Si TEMPLATE, @params ne doit pas être NULL.

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

@params doit être fourni dans le même format que celui soumis à SQL Server à l’aide sp_executesql ou soumis en interne après le paramétrage. Pour plus d’informations, consultez la section Remarques. Si le lot ne contient pas de paramètres, NULL doit être spécifié. La taille de @params est limitée uniquement par la mémoire du serveur disponible.

[ @hints = ] { N’OPTION ( query_hint [ , ... n ] )' | N’XML_showplan' }

@hints est nvarchar(max), avec la valeur par défaut NULL.

  • OPTION ( <query_hint> [ , ...n ] )

    Spécifie une OPTION clause à attacher à une requête qui correspond à @stmt. @hints doit être syntactiquement identique à une OPTION clause d’une SELECT instruction et peut contenir n’importe quelle séquence valide d’indicateurs de requête.

  • <XML_showplan>'

    Plan de requête au format XML à appliquer sous forme d’indicateur.

    Nous vous recommandons d’affecter le plan d’affichage XML à une variable. Dans le cas contraire, vous devez échapper à des guillemets simples dans le plan d’affichage en les précèdent par un autre guillemet unique. Voir l’exemple E.

  • NULL

    Indique que tout indicateur existant spécifié dans la OPTION clause de la requête n’est pas appliqué à la requête. Pour plus d’informations, consultez la clause OPTION.

Notes

Les arguments à sp_create_plan_guide fournir dans l’ordre indiqué. 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 dans la position où le nom est utilisé.

Vous pouvez créer plusieurs OBJECT repères ou SQL de plans 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é.

Les repères de plan de type OBJECT ne peuvent pas être créés 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 la WITH ENCRYPTION clause ou qui est temporaire.

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. La tentative de suppression d’une table définie sur un déclencheur référencé par un repère de plan provoque également une erreur.

Les repères de plan ne peuvent pas être utilisés dans chaque édition de SQL Server. Pour obtenir la liste des fonctionnalités prises en charge par les éditions de SQL Server, consultez Éditions et fonctionnalités prises en charge de SQL Server 2022. Les repères de plan 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 restent intacts lorsque vous restaurez ou attachez une base de données à une version mise à niveau de SQL Server. 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.

Exigences de correspondance du repère de plan

Pour les repères de plan qui spécifient @type d’une SQL requête ou TEMPLATE pour qu’ils correspondent correctement à une requête, les valeurs de @module_or_batch et de @params [, ... n ] doit être fourni dans le même format que leurs équivalents soumis par l’application. Cela signifie que vous devez fournir le texte de lot exactement au fur et à mesure que 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 est et @module_or_batch est défini NULLsur , la valeur de @module_or_batch est définie sur la valeur de @stmt. SQL Cela signifie que la valeur de @stmt doit être fournie dans le même format, caractère pour caractère, qu’elle est envoyée à SQL Server. Aucune conversion interne n'est effectuée pour faciliter cette correspondance.

Quand SQL Server correspond à la valeur de @stmt à @module_or_batch et @params [, ... n ], ou si @type est OBJECT, au texte de la requête correspondante à l’intérieur <object_name>, les éléments de chaîne suivants ne sont pas pris en compte :

  • Espaces blancs (onglets, espaces, retours chariots ou flux de ligne) à l’intérieur de la chaîne
  • Commentaires (-- ou /* */)
  • les points-virgules situés à la fin.

Par exemple, SQL Server peut correspondre à la chaîne N'SELECT * FROM T WHERE a = 10' @stmt à la @module_or_batch suivante :

 N'SELECT *
 FROM T
 WHERE a = 10'

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

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 correspondance respecte la casse et respecte les accents (même lorsque le classement de la base de données ne respecte pas la casse), sauf s’il existe des mots clés, où la casse ne respecte pas la casse. 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 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 OBJECT ou SQL sur un lot supprime le plan de requête d’un lot qui est la même valeur de hachage. La création d’un repère de plan de type TEMPLATE supprime tous les lots à instruction unique du cache de plan au sein de cette base de données.

autorisations

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

Exemples

R. Créer 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

Voici le guide de plan créé sur 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éer un repère de plan de type SQL pour une requête autonome

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

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éer un repère de plan de type TEMPLATE pour la forme paramétrable 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 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 guide de plan sur 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 seule façon fiable d’obtenir cette valeur pour sp_create_plan_guide une utilisation consiste à utiliser la procédure stockée système sp_get_query_template . Le script suivant obtient la requête paramétrable, puis crée un repère de plan sur celui-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)';

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 gérer différentes plages de valeurs de paramètre.

D. Créer un guide de plan sur une requête envoyée à l’aide d’une requête de curseur d’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_cursorprepexecet 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 les traces sql Server Profiler en consultant l’événement de trace du RPC:Starting profileur.

Supposons que les données suivantes apparaissent dans un événement de RPC:Starting trace profileur pour une requête que vous souhaitez paramétrer avec 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 suivantes de cette requête par l’application sont affectées par ce guide de plan et une jointure de hachage est utilisée pour traiter la requête.

E. Créer un guide de plan en obtenant le plan d’affichage XML à partir d’un plan mis en cache

L’exemple suivant crée un repère de plan pour une instruction ad hoc SQL simple. Le plan de requête souhaité pour cette instruction est fourni dans le guide du plan en spécifiant le plan d’exécution XML pour la requête directement dans le @hints paramètre. L’exemple exécute d’abord l’instruction SQL pour générer un plan dans le cache du plan. Dans le cadre de cet exemple, il est supposé que le plan généré est le plan souhaité et qu’aucun réglage de requête supplémentaire n’est nécessaire. Le plan d’affichage XML de la requête est obtenu en interrogeant les vues de sys.dm_exec_sql_textgestion dynamique et sys.dm_exec_text_query_plan en interrogeant la @xml_showplan sys.dm_exec_query_statsvariable. 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

Contenu connexe