sp_create_plan_guide (Transact-SQL)

2025-06-27

gäller för:SQL Server Azure SQL Database Azure SQL Managed Instance

Skapar en planguide för att associera frågetips eller faktiska frågeplaner med frågor i en databas. Mer information om planguider finns i Planguider.

Transact-SQL syntaxkonventioner

Syntax

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' } ]
[ ; ]

Argumentpunkter

[ @name = ] N'namn'

Namnet på planguiden. @name är sysname, utan standard och en maximal längd på 124 tecken. Planguidenamn är begränsade till den aktuella databasen. @name måste följa reglerna för identifierare och kan inte börja med nummertecknet (#).

[ @stmt = ] N'stmt"

En Transact-SQL-instruktion som du kan använda för att skapa en planguide. @stmt är nvarchar(max), med standardvärdet NULL. När SQL Server-frågeoptimeraren identifierar en fråga som matchar @stmt börjar @name gälla. För att en planguide ska lyckas måste @stmt visas i den kontext som anges av parametrarna @type, @module_or_batch och @params .

@stmt måste tillhandahållas på ett sätt som gör det möjligt för frågeoptimeraren att matcha den med motsvarande instruktion, som tillhandahålls i batchen eller modulen som identifieras av @module_or_batch och @params. Mer information finns i avsnittet Kommentarer. Storleken på @stmt begränsas endast av tillgängligt minne på servern.

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

Den typ av entitet som @stmt visas i. Detta anger kontexten för matchning av @stmt till @name. @type är nvarchar(60) och kan vara ett av följande värden:

OBJECT

Anger @stmt visas i kontexten för en Transact-SQL lagrad procedur, skalär funktion, tabellvärdesfunktion för flera delstater eller Transact-SQL DML-utlösare i den aktuella databasen.
SQL

Anger @stmt visas i kontexten för en fristående instruktion eller batch som kan skickas till SQL Server via valfri mekanism. Transact-SQL-instruktioner som skickas av CLR-objekt (Common Language Runtime) eller utökade lagrade procedurer, eller med hjälp EXECUTE N'<sql_string>'av , bearbetas som batchar på servern och bör därför identifieras som @type av SQL. Om SQL anges kan frågetipset PARAMETERIZATION { FORCED | SIMPLE } inte anges i parametern @hints .
TEMPLATE

Anger att planguiden gäller för alla frågor som parameteriserar formuläret som anges i @stmt. Om TEMPLATE anges kan endast frågetipset PARAMETERIZATION { FORCED | SIMPLE } anges i parametern @hints . Mer information om TEMPLATE planguider finns i Ange beteende för frågeparameterisering med hjälp av planguider.

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

Anger antingen namnet på det objekt där @stmt visas, eller batchtexten där @stmt visas. @module_or_batch är nvarchar(max), med standardvärdet NULL. Batchtexten kan inte innehålla en USE <database> instruktion.

För att en planguide ska matcha en batch som skickas från ett program måste @module_or_batch anges i samma format, tecken för tecken, som den skickas till SQL Server. Ingen intern konvertering utförs för att underlätta den här matchning. Mer information finns i avsnittet Kommentarer.

[ <schema_name>. ] <object_name> anger namnet på en Transact-SQL lagrad procedur, skalär funktion, tabellvärdesfunktion för flera delstater eller Transact-SQL DML-utlösare som innehåller @stmt. Om <schema_name> inte har angetts <schema_name> använder du schemat för den aktuella användaren. Om NULL anges och @type är SQLanges värdet för @module_or_batch till värdet för @stmt. Om @type är TEMPLATEmåste @module_or_batch vara NULL.

[ @params = ] N'@parameter_namedata_type [ ,... n ]'

Anger definitionerna för alla parametrar som är inbäddade i @stmt. @params är nvarchar(max), med standardvärdet NULL. @params gäller endast när något av följande alternativ är sant:

@type är SQL eller TEMPLATE. Om TEMPLATEfår @params inte vara NULL.
@stmt skickas med hjälp sp_executesql av och ett värde för parametern @params anges, eller så skickar SQL Server internt en instruktion efter att ha parameteriserat den. Inlämning av parametriserade frågor från databas-API:er (inklusive ODBC, OLE DB och ADO.NET) visas för SQL Server som anrop till sp_executesql eller till API-servermarkörrutiner. Därför kan de även matchas av SQL guider eller TEMPLATE planguider.

@params måste anges i exakt samma format som det skickas till SQL Server antingen med hjälp sp_executesql av eller skickas internt efter parameteriseringen. Mer information finns i avsnittet Kommentarer. Om batchen inte innehåller parametrar NULL måste anges. Storleken på @params begränsas endast av tillgängligt serverminne.

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

@hints är nvarchar(max), med standardvärdet NULL.

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

Anger en OPTION sats som ska kopplas till en fråga som matchar @stmt. @hints måste vara syntaktiskt samma som en OPTION sats i en SELECT -instruktion och kan innehålla valfri giltig sekvens med frågetips.
<XML_showplan>'

Frågeplanen i XML-format som ska användas som ett tips.

Vi rekommenderar att du tilldelar XML-showplan till en variabel. Annars måste du undvika enkla citattecken i showplan genom att föregå dem med ett annat enkelt citattecken. Se exempel E.
NULL

Anger att ett befintligt tips som anges i OPTION frågans sats inte tillämpas på frågan. Mer information finns i OPTION-satsen.

Anmärkningar

sp_create_plan_guide Argumenten som ska anges i den ordning som visas. När du anger värden för parametrarna sp_create_plan_guidei måste alla parameternamn anges explicit, eller ingen alls. Om du till exempel @name = anger måste du @stmt =också ange , @type =och så vidare. På samma sätt, om @name = utelämnas och endast parametervärdet anges, måste de återstående parameternamnen också utelämnas och endast deras värden anges. Argumentnamn är endast i beskrivande syfte för att förstå syntaxen. SQL Server verifierar inte att det angivna parameternamnet matchar namnet på parametern i den position där namnet används.

Du kan skapa mer än en OBJECT guide eller SQL planguide för samma fråga och batch eller modul. Det går dock bara att aktivera en planguide när som helst.

Planguider av typen OBJECT kan inte skapas för ett @module_or_batch värde som refererar till en lagrad procedur, funktion eller DML-utlösare som anger WITH ENCRYPTION satsen eller som är tillfällig.

Att försöka släppa eller ändra en funktion, lagrad procedur eller DML-utlösare som refereras av en planguide, antingen aktiverad eller inaktiverad, orsakar ett fel. Om du försöker släppa en tabell som är en utlösare som definierats för den och som refereras av en planguide, uppstår också ett fel.

Planguider kan inte användas i varje version av SQL Server. En lista över funktioner som stöds av versionerna av SQL Server finns i Utgåvor och funktioner som stöds i SQL Server 2022. Planguider visas i alla utgåvor. Du kan också bifoga en databas som innehåller planguider till valfri utgåva. Planguiderna förblir intakta när du återställer eller kopplar en databas till en uppgraderad version av SQL Server. Du bör kontrollera att planguiderna är önskvärda i varje databas när du har utfört en serveruppgradering.

Matchningskrav för planguide

För planguider som anger @type av SQL eller TEMPLATE för att matcha en fråga, värdena för @module_or_batch och @params [, ... n ] måste anges i exakt samma format som deras motsvarigheter som lämnats in av programmet. Det innebär att du måste ange batchtexten exakt som SQL Server-kompilatorn tar emot den. Om du vill samla in den faktiska batch- och parametertexten kan du använda SQL Server Profiler. Mer information finns i Använda SQL Server Profiler för att skapa och testa planguider.

När @type är SQL och @module_or_batch är inställt NULLpå anges värdet för @module_or_batch till värdet för @stmt. Det innebär att värdet för @stmt måste anges i exakt samma format, tecken för tecken, som det skickas till SQL Server. Ingen intern konvertering utförs för att underlätta den här matchning.

När SQL Server matchar värdet för @stmt till @module_or_batch och @params [, ... n ], eller om @type är OBJECT, till texten i motsvarande fråga i <object_name>, beaktas inte följande strängelement:

Blankstegstecken (flikar, blanksteg, vagnreturer eller radmatningar) i strängen
Kommentarer (-- eller /* */)
Avslutande semikolon

SQL Server kan till exempel matcha @stmt strängen N'SELECT * FROM T WHERE a = 10' med följande @module_or_batch:

 N'SELECT *
 FROM T
 WHERE a = 10'

Samma sträng skulle dock inte matchas med den här @module_or_batch:

N'SELECT * FROM T WHERE b = 10'

SQL Server ignorerar vagnreturen, radmatningen och blankstegstecken i den första frågan. I den andra frågan tolkas sekvensen WHERE b = 10 på ett annat sätt än WHERE a = 10. Matchning är skiftlägeskänsligt och dekorkänsligt (även när sortering av databasen är skiftlägeskänslig), förutom om det finns nyckelord, där fallet är okänsligt. Matchning är känsligt för tomma blanksteg. Matchning är okänsligt för förkortade former av nyckelord. Nyckelorden EXECUTE, EXECoch execute anses till exempel vara likvärdiga.

Planguidens effekt på plancachen

När du skapar en planguide för en modul tar du bort frågeplanen för modulen från plancachen. Om du skapar en planguide av typen OBJECT eller SQL i en batch, tar du bort frågeplanen för en batch som har samma hashvärde. När du skapar en planguide av typen TEMPLATE tar du bort alla batchar med en sats från plancachen i databasen.

Behörigheter

Om du vill skapa en planguide av typen OBJECTmåste ALTER du ha behörighet för det refererade objektet. Om du vill skapa en planguide av typen SQL eller TEMPLATEmåste du ha ALTER behörighet för den aktuella databasen.

Exempel

A. Skapa en planguide av typen OBJECT för en fråga i en lagrad procedur

I följande exempel skapas en planguide som matchar en fråga som körs i kontexten för en programbaserad lagrad procedur och tillämpar tipset OPTIMIZE FOR på frågan.

Här är den lagrade proceduren:

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

Här är planguiden som skapats för frågan i den lagrade proceduren:

EXECUTE 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. Skapa en planguide av typen SQL för en fristående fråga

I följande exempel skapas en planguide som matchar en fråga i en batch som skickas av ett program som använder den sp_executesql system lagrade proceduren.

Här är batchen:

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

Skapa följande planguide för att förhindra att en parallell körningsplan genereras i den här frågan:

EXECUTE 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. Skapa en planguide av typen TEMPLATE för den parameteriserade formen av en fråga

I följande exempel skapas en planguide som matchar alla frågor som parameteriseras till ett angivet formulär och som dirigerar SQL Server till att framtvinga parameterisering av frågan. Följande två frågor är syntaktiskt likvärdiga, men skiljer sig bara åt i sina konstanta literalvärden.

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;

Här är planguiden för frågans parametriserade form:

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

I föregående exempel är värdet för parametern @stmt den parametriserade formen av frågan. Det enda tillförlitliga sättet att hämta det här värdet för användning i sp_create_plan_guide är att använda den sp_get_query_template system lagrade proceduren. Följande skript hämtar den parametriserade frågan och skapar sedan en planguide för den.

DECLARE @stmt AS NVARCHAR (MAX);
DECLARE @params AS NVARCHAR (MAX);

EXECUTE 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;

EXECUTE sp_create_plan_guide N'TemplateGuide1',
    @stmt, N'TEMPLATE', NULL,
    @params, N'OPTION(PARAMETERIZATION FORCED)';

Viktigt!

Värdet för de konstanta literalerna i parametern @stmt som skickas till sp_get_query_template kan påverka den datatyp som väljs för parametern som ersätter literalen. Detta påverkar matchning av planguiden. Du kan behöva skapa fler än en planguide för att hantera olika parametervärdeintervall.

D. Skapa en planguide för en fråga som skickas med hjälp av en API-markörbegäran

Planguider kan matcha frågor som skickas från API-servermarkörrutiner. Dessa rutiner omfattar sp_cursorprepare, sp_cursorprepexecoch sp_cursoropen. Program som använder API:erna ADO, OLE DB och ODBC interagerar ofta med SQL Server med hjälp av API-servermarkörer. Du kan se anropet av API-servermarkörrutiner i SQL Server Profiler-spårningar genom att visa spårningshändelsen RPC:Starting profiler.

Anta att följande data visas i en RPC:Starting profilerarspårningshändelse för en fråga som du vill justera med en planguide:

DECLARE @p1 AS INT;
SET @p1 = -1;

DECLARE @p2 AS INT;
SET @p2 = 0;

DECLARE @p5 AS INT;
SET @p5 = 4104;

DECLARE @p6 AS INT;
SET @p6 = 8193;

DECLARE @p7 AS INT;
SET @p7 = 0;

EXECUTE 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;

Du märker att planen för SELECT frågan i anropet till sp_cursorprepexec använder en sammanslagningskoppling, men du vill använda en hash-koppling. Frågan som skickas med hjälp sp_cursorprepexec av parameteriseras, inklusive både en frågesträng och en parametersträng. Du kan skapa följande planguide för att ändra val av plan genom att använda fråge- och parametersträngarna exakt som de visas, tecken för tecken, i anropet till sp_cursorprepexec.

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

Efterföljande körningar av den här frågan av programmet påverkas av den här planguiden och en hashkoppling används för att bearbeta frågan.

E. Skapa en planguide genom att hämta XML-showplan från en cachelagrad plan

I följande exempel skapas en planguide för en enkel ad hoc-instruktion SQL . Den önskade frågeplanen för den här instruktionen tillhandahålls i planguiden genom att ange XML-showplan för frågan direkt i parametern @hints . Exemplet kör först -instruktionen SQL för att generera en plan i plancachen. I det här exemplet förutsätts det att den genererade planen är önskad plan och att ingen ytterligare frågejustering krävs. XML-showplan för frågan hämtas genom att köra frågor mot vyerna sys.dm_exec_query_stats, sys.dm_exec_sql_textoch sys.dm_exec_text_query_plan dynamisk hantering och tilldelas till variabeln @xml_showplan . Variabeln @xml_showplan skickas sedan till -instruktionen sp_create_plan_guide i parametern @hints . Eller så kan du skapa en planguide från en frågeplan i plancachen med hjälp av den sp_create_plan_guide_from_handle lagrade proceduren.

USE AdventureWorks2022;
GO

SELECT City,
       StateProvinceID,
       PostalCode
FROM Person.Address
ORDER BY PostalCode DESC;
GO

DECLARE @xml_showplan AS 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;%'
);

EXECUTE 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

Feedback

Var den här sidan till hjälp?