sp_create_plan_guide (Transact-SQL)

2025-06-26

Van toepassing op:SQL Server Azure SQL Database Azure SQL Managed Instance

Hiermee maakt u een planhandleiding voor het koppelen van queryhints of werkelijke queryplannen aan query's in een database. Zie Planhandleidingen voor meer informatie over planhandleidingen.

Transact-SQL syntaxis-conventies

Syntaxis

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

Argumenten

[ @name = ] N'naam'

De naam van de planhandleiding. @nameis sysname, zonder standaard en een maximale lengte van 124 tekens. Namen van plangidsen zijn gericht op de huidige database. @name moet voldoen aan de regels voor id's en mag niet beginnen met het nummerteken (#).

[ @stmt = ] N'stmt'

Een Transact-SQL instructie waarmee een planhandleiding moet worden gemaakt. @stmt is nvarchar(max), met een standaardwaarde van NULL. Wanneer de sql Server-queryoptimalisatie een query herkent die overeenkomt met @stmt, wordt @name van kracht. Als u een planhandleiding wilt maken, moet @stmt worden weergegeven in de context die is opgegeven door de parameters @type, @module_or_batch en @params .

@stmt moet zodanig worden opgegeven dat de queryoptimalisatie kan overeenkomen met de bijbehorende instructie, die is opgegeven in de batch of module die is geïdentificeerd door @module_or_batch en @params. Zie de sectie Opmerkingen voor meer informatie. De grootte van @stmt wordt alleen beperkt door het beschikbare geheugen van de server.

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

Het type entiteit waarin @stmt wordt weergegeven. Hiermee geeft u de context op voor het koppelen van @stmt aan @name. @type is nvarchar(60) en kan een van de volgende waarden zijn:

OBJECT

Geeft aan @stmt wordt weergegeven in de context van een Transact-SQL opgeslagen procedure, scalaire functie, tabelwaardefunctie met meerdere waarden of Transact-SQL DML-trigger in de huidige database.
SQL

Geeft aan @stmt wordt weergegeven in de context van een zelfstandige instructie of batch die via elk mechanisme naar SQL Server kan worden verzonden. Transact-SQL instructies die zijn ingediend door CLR-objecten (Common Language Runtime) of uitgebreide opgeslagen procedures, of met behulp van EXECUTE N'<sql_string>', worden verwerkt als batches op de server en moeten daarom worden geïdentificeerd als @type van SQL. Als SQL dit is opgegeven, kan de queryhint PARAMETERIZATION { FORCED | SIMPLE } niet worden opgegeven in de parameter @hints .
TEMPLATE

Hiermee wordt aangegeven dat de planhandleiding van toepassing is op elke query die wordt geparametereerd op het formulier dat wordt aangegeven in @stmt. Als TEMPLATE dit is opgegeven, kan alleen de PARAMETERIZATION { FORCED | SIMPLE } queryhint worden opgegeven in de parameter @hints . Zie Queryparameterisatiegedrag opgeven met behulp van planhandleidingen voor meer informatie over TEMPLATE planhandleidingen.

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

Hiermee geeft u de naam op van het object waarin @stmt wordt weergegeven of de batchtekst waarin @stmt wordt weergegeven. @module_or_batch is nvarchar(max), met een standaardwaarde van NULL. De batchtekst kan geen instructie bevatten USE <database> .

Voor een planhandleiding die overeenkomt met een batch die is verzonden vanuit een toepassing, moet @module_or_batch worden opgegeven in dezelfde indeling, teken-voor-teken, zoals deze wordt verzonden naar SQL Server. Er wordt geen interne conversie uitgevoerd om deze aansluiting te vergemakkelijken. Zie de sectie Opmerkingen voor meer informatie.

[ <schema_name>. ] <object_name> hiermee geeft u de naam van een Transact-SQL opgeslagen procedure, scalaire functie, multistatement tabelwaardefunctie of Transact-SQL DML-trigger die @stmt bevat. Als <schema_name> dit niet is opgegeven, <schema_name> gebruikt u het schema van de huidige gebruiker. Als NULL dit is opgegeven en @type is SQL, wordt de waarde van @module_or_batch ingesteld op de waarde van @stmt. Als @type is TEMPLATE, moet @module_or_batch zijn NULL.

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

Hiermee geeft u de definities op van alle parameters die zijn ingesloten in @stmt. @params is nvarchar(max), met een standaardwaarde van NULL. @params geldt alleen wanneer een van de volgende opties waar is:

@type is SQL of TEMPLATE. Als TEMPLATE, @params mag niet zijn NULL.
@stmt wordt verzonden met behulp sp_executesql van en een waarde voor de parameter @params is opgegeven, of SQL Server verzendt intern een instructie na het parameteriseren ervan. Het verzenden van geparameteriseerde query's van database-API's (inclusief ODBC, OLE DB en ADO.NET) lijkt sql Server te gebruiken als aanroepen naar sp_executesql of naar DE API-servercursorroutines. Ze kunnen daarom ook worden vergeleken met SQL of TEMPLATE planhulplijnen.

@params moet worden opgegeven in exact dezelfde indeling als die wordt verzonden naar SQL Server door gebruik te maken sp_executesql van of intern te hebben ingediend na parameterisatie. Zie de sectie Opmerkingen voor meer informatie. Als de batch geen parameters bevat, NULL moet u deze opgeven. De grootte van @params wordt alleen beperkt door het beschikbare servergeheugen.

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

@hints is nvarchar(max), met een standaardwaarde van NULL.

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

Hiermee geeft u een OPTION component op die moet worden gekoppeld aan een query die overeenkomt met @stmt. @hints moet syntactisch hetzelfde zijn als een OPTION component in een SELECT instructie en kan elke geldige reeks queryhints bevatten.
<XML_showplan>'

Het queryplan in XML-indeling dat moet worden toegepast als hint.

U wordt aangeraden het XML-showplan toe te wijzen aan een variabele. Anders moet u enkele aanhalingstekens in het showplan ontsnappen door deze vooraf te laten gaan met een ander aanhalingsteken. Zie voorbeeld E.
NULL

Geeft aan dat een bestaande hint die is opgegeven in de OPTION component van de query niet wordt toegepast op de query. Zie OPTION-component voor meer informatie.

Opmerkingen

De argumenten die moeten sp_create_plan_guide worden opgegeven in de volgorde die wordt weergegeven. Wanneer u waarden opgeeft voor de parameters van sp_create_plan_guide, moeten alle parameternamen expliciet worden opgegeven of helemaal geen. Als bijvoorbeeld @name = is opgegeven, @stmt =moet , @type =enzovoort, ook worden opgegeven. @name = Als u dit weglaat en alleen de parameterwaarde wordt opgegeven, moeten de resterende parameternamen ook worden weggelaten en alleen de opgegeven waarden. Argumentnamen zijn alleen bedoeld voor beschrijvende doeleinden om inzicht te krijgen in de syntaxis. SQL Server controleert niet of de opgegeven parameternaam overeenkomt met de naam voor de parameter in de positie waar de naam wordt gebruikt.

U kunt meer dan één OBJECT of SQL planhandleiding maken voor dezelfde query en batch of module. Er kan echter maar één planhandleiding op een bepaald moment worden ingeschakeld.

Planhulplijnen van het type OBJECT kunnen niet worden gemaakt voor een @module_or_batch-waarde die verwijst naar een opgeslagen procedure, functie of DML-trigger waarmee de WITH ENCRYPTION component wordt opgegeven of dat tijdelijk is.

Als u een functie, opgeslagen procedure of DML-trigger probeert te verwijderen of wijzigen waarnaar wordt verwezen door een planhandleiding, ingeschakeld of uitgeschakeld, treedt er een fout op. Als u een tabel probeert te verwijderen die een trigger is die erop is gedefinieerd en waarnaar wordt verwezen door een planhandleiding, wordt ook een fout veroorzaakt.

Planhandleidingen kunnen niet worden gebruikt in elke editie van SQL Server. Zie -edities en ondersteunde functies van SQL Server 2022voor een lijst met functies die worden ondersteund door de edities van SQL Server. Planhandleidingen zijn zichtbaar in elke editie. U kunt ook een database met planhandleidingen toevoegen aan elke editie. Planhandleidingen blijven intact wanneer u een database herstelt of koppelt aan een bijgewerkte versie van SQL Server. Controleer de wenselijkheid van de planhandleidingen in elke database na het uitvoeren van een serverupgrade.

Overeenkomende vereisten voor planhandleiding

Voor planhulplijnen die @type van SQL of TEMPLATE overeenkomen met een query, de waarden voor @module_or_batch en @params [, ... n ] moet exact dezelfde indeling hebben als de tegenhangers die door de aanvraag zijn ingediend. Dit betekent dat u de batchtekst precies moet opgeven wanneer de SQL Server-compiler deze ontvangt. Als u de werkelijke batch- en parametertekst wilt vastleggen, kunt u SQL Server Profiler gebruiken. Zie SQL Server Profiler gebruiken voor het maken en testen van planhandleidingen voor meer informatie.

Wanneer @type is SQLingesteld op @module_or_batch, wordt de waarde van @module_or_batch ingesteld NULLop de waarde van @stmt. Dit betekent dat de waarde voor @stmt moet worden opgegeven in exact dezelfde indeling, teken-voor-teken, zoals deze wordt verzonden naar SQL Server. Er wordt geen interne conversie uitgevoerd om deze aansluiting te vergemakkelijken.

Wanneer SQL Server overeenkomt met de waarde van @stmt@module_or_batch en@params [, ... n ], of als @type is OBJECT, worden de volgende tekenreekselementen niet meegenomen in de tekst van de bijbehorende query <object_name>binnen:

Spatietekens (tabs, spaties, regelterugloop of regelfeeds) in de tekenreeks
Opmerkingen (-- of /* */)
Volgkomma's

SQL Server kan bijvoorbeeld overeenkomen met de @stmt tekenreeks N'SELECT * FROM T WHERE a = 10' met de volgende @module_or_batch:

 N'SELECT *
 FROM T
 WHERE a = 10'

Dezelfde tekenreeks zou echter niet overeenkomen met deze @module_or_batch:

N'SELECT * FROM T WHERE b = 10'

SQL Server negeert de regelterugloop, regelinvoer en spatietekens in de eerste query. In de tweede query wordt de volgorde WHERE b = 10 anders geïnterpreteerd dan WHERE a = 10. Matching is hoofdlettergevoelig en accentgevoelig (zelfs wanneer de sortering van de database niet hoofdlettergevoelig is), behalve als er trefwoorden zijn, waarbij hoofdlettergevoelig is. Vergelijken is gevoelig voor lege spaties. Overeenkomende trefwoorden zijn niet gevoelig voor verkorte vormen van trefwoorden. De trefwoorden EXECUTEen EXECexecute worden bijvoorbeeld beschouwd als gelijkwaardig.

Effect van planhandleiding op de plancache

Door een planhandleiding voor een module te maken, verwijdert u het queryplan voor die module uit de plancache. Als u een planhandleiding van het type OBJECT of SQL in een batch maakt, wordt het queryplan verwijderd voor een batch met dezelfde hash-waarde. Als u een planhandleiding van het type TEMPLATE maakt, worden alle batches met één instructie uit de plancache in die database verwijderd.

Machtigingen

Als u een planhandleiding van het type OBJECTwilt maken, moet u toestemming hebben ALTER voor het object waarnaar wordt verwezen. Voor het maken van een planhandleiding van het type SQL of TEMPLATE, is een machtiging vereist ALTER voor de huidige database.

Voorbeelden

Eén. Een planhandleiding voor het type OBJECT maken voor een query in een opgeslagen procedure

In het volgende voorbeeld wordt een planhandleiding gemaakt die overeenkomt met een query die wordt uitgevoerd in de context van een opgeslagen procedure op basis van een toepassing en die de OPTIMIZE FOR hint toepast op de query.

Dit is de opgeslagen procedure:

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

Dit is de planhandleiding die is gemaakt voor de query in de opgeslagen procedure:

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. Een planhandleiding van het type SQL maken voor een zelfstandige query

In het volgende voorbeeld wordt een planhandleiding gemaakt die overeenkomt met een query in een batch die is ingediend door een toepassing die gebruikmaakt van de door het sp_executesql systeem opgeslagen procedure.

Dit is de batch:

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

Als u wilt voorkomen dat een parallel uitvoeringsplan op deze query wordt gegenereerd, maakt u de volgende planhandleiding:

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. Een planhandleiding van het type TEMPLATE maken voor de geparameteriseerde vorm van een query

In het volgende voorbeeld wordt een plangids gemaakt die overeenstemt met elke query die parameterisatie van een gespecificeerd formaat vereist, en wordt SQL Server geïnstrueerd om parameterisatie van de query af te dwingen. De volgende twee query's zijn syntactisch equivalent, maar verschillen alleen in hun constante letterlijke waarden.

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;

Hier volgt de planhandleiding voor de geparameteriseerde vorm van de query:

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

In het vorige voorbeeld is de waarde voor de parameter @stmt de geparameteriseerde vorm van de query. De enige betrouwbare manier om deze waarde te verkrijgen voor gebruik sp_create_plan_guide is het gebruik van de sp_get_query_template systeem opgeslagen procedure. Met het volgende script wordt de geparameteriseerde query verkregen en wordt er vervolgens een planhandleiding voor gemaakt.

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

Belangrijk

De waarde van de constante letterlijke waarden in de @stmt parameter die wordt doorgegeven aan sp_get_query_template kan van invloed zijn op het gegevenstype dat is gekozen voor de parameter die de letterlijke waarde vervangt. Dit is van invloed op het afstemmen van planhandleidingen. Mogelijk moet u meer dan één planhandleiding maken voor het afhandelen van verschillende parameterwaardebereiken.

D. Een planhandleiding maken voor een query die is verzonden met behulp van een API-cursoraanvraag

Planhandleidingen kunnen overeenkomen met query's die worden verzonden vanuit de cursorroutines van de API-server. Deze routines zijn onder andere sp_cursorprepare, sp_cursorprepexecen sp_cursoropen. Toepassingen die gebruikmaken van de ADO-, OLE DB- en ODBC-API's communiceren vaak met SQL Server met behulp van API-servercursors. U kunt de aanroep van cursorroutines van DE API-server in SQL Server Profiler-traceringen zien door de traceringsbeurtenis van profiler RPC:Starting weer te geven.

Stel dat de volgende gegevens worden weergegeven in een RPC:Starting profiler-traceringsevenement voor een query die u wilt afstemmen met een planhandleiding:

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;

U ziet dat het plan voor de SELECT query in de aanroep om sp_cursorprepexec een samenvoegingsdeelname gebruikt, maar u een hash-join wilt gebruiken. De query die wordt verzonden met behulp van een sp_cursorprepexec parameter, wordt geparameteriseerd, inclusief zowel een querytekenreeks als een parametertekenreeks. U kunt de volgende planhandleiding maken om de keuze van het plan te wijzigen met behulp van de query- en parametertekenreeksen precies zoals ze worden weergegeven, teken voor teken, in de aanroep naar 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)';

De volgende uitvoeringen van deze query door de toepassing worden beïnvloed door deze planhandleiding en er wordt een hash-join gebruikt om de query te verwerken.

E. Een planhandleiding maken door het XML-showplan op te halen uit een plan in de cache

In het volgende voorbeeld wordt een planhandleiding gemaakt voor een eenvoudige ad-hoc-instructie SQL . Het gewenste queryplan voor deze instructie wordt opgegeven in de planhandleiding door het XML-showplan voor de query rechtstreeks in de @hints parameter op te geven. In het voorbeeld wordt eerst de SQL instructie uitgevoerd om een plan te genereren in de plancache. In dit voorbeeld wordt ervan uitgegaan dat het gegenereerde plan het gewenste plan is en dat er geen verdere queryafstemming is vereist. Het XML-showplan voor de query wordt verkregen door query's uit te voeren op de sys.dm_exec_query_statsweergaven en sys.dm_exec_sql_textsys.dm_exec_text_query_plan dynamisch beheer en toegewezen aan de @xml_showplan variabele. De @xml_showplan variabele wordt vervolgens doorgegeven aan de sp_create_plan_guide instructie in de @hints parameter. U kunt ook een planhandleiding maken op basis van een queryplan in de plancache met behulp van de sp_create_plan_guide_from_handle opgeslagen procedure.

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

Is deze pagina nuttig?