sp_create_plan_guide (Transact-SQL)

A következőkre vonatkozik:SQL ServerAzure SQL DatabaseFelügyelt Azure SQL-példánySQL-adatbázis a Microsoft Fabricben

Létrehoz egy terv útmutatót a lekérdezési tippek vagy a tényleges lekérdezéstervek adatbázisbeli lekérdezésekkel való társításához. A terv útmutatóiról további információt a Terv útmutatói című témakörben talál.

Transact-SQL szintaxis konvenciók

Szemantika

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

Érvek

[ @name = ] N'név'

A terv útmutatójának neve. @namea sysname, alapértelmezés nélkül, és legfeljebb 124 karakter hosszúságú. A terv útmutatójának neve az aktuális adatbázisra terjed ki. @name meg kell felelnie az azonosítókra vonatkozó szabályoknak, és nem kezdődhet a számjellel (#).

[ @stmt = ] N'stmt'

Egy Transact-SQL utasítás, amely alapján tervútmutatót hozhat létre. @stmtnvarchar(max), alapértelmezés szerint NULL. Amikor az SQL Server lekérdezésoptimalizáló felismer egy @stmt egyező lekérdezést, @name érvénybe lép. A terv útmutatójának sikeres létrehozásához @stmt a @type, @module_or_batch és @params paraméterek által meghatározott környezetben kell megjelennie.

@stmt úgy kell megadni, hogy a lekérdezésoptimalizáló a @module_or_batch és @params által meghatározott kötegben vagy modulban megadott megfelelő utasítással egyezzen. További információ: Megjegyzések szakasz. A @stmt méretét csak a kiszolgáló rendelkezésre álló memóriája korlátozza.

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

Az entitás típusa, amelyben @stmt jelenik meg. Ez határozza meg a @stmt@name való egyeztetésének kontextusát. @typenvarchar(60), és az alábbi értékek egyike lehet:

• OBJECT

  Azt jelzi , @stmt jelenik meg egy Transact-SQL tárolt eljárás, skaláris függvény, többstatement táblaértékű függvény vagy Transact-SQL DML-eseményindító kontextusában az aktuális adatbázisban.

• SQL

  Azt jelzi , @stmt egy különálló utasítás vagy köteg kontextusában jelenik meg, amely bármilyen mechanizmussal elküldhető az SQL Servernek. Transact-SQL common language runtime (CLR) objektumok vagy kiterjesztett tárolt eljárások által vagy a használatával EXECUTE N'<sql_string>'beküldött utasításokat kötegekként dolgozzák fel a kiszolgálón, ezért azokat a @typeSQLkell azonosítani. Ha SQL meg van adva, a lekérdezési tipp PARAMETERIZATION { FORCED | SIMPLE } nem adható meg a @hints paraméterben.

• TEMPLATE

  Azt jelzi, hogy a terv útmutatója minden olyan lekérdezésre vonatkozik, amely a @stmt megadott űrlapra paraméterez. Ha TEMPLATE meg van adva, csak a lekérdezési PARAMETERIZATION { FORCED | SIMPLE } tipp adható meg a @hints paraméterben. A terv segédvonalakkal kapcsolatos TEMPLATE további információkért lásd: Lekérdezésparaméterezési viselkedés megadása tervsegédek használatával.

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

Megadja annak az objektumnak a nevét, amelyben a @stmt megjelenik, vagy azt a kötegszöveget, amelyben @stmt megjelenik. @module_or_batchnvarchar(max), alapértelmezés szerint NULL. A kötegszöveg nem tartalmazhat utasítást USE <database> .

Ahhoz, hogy egy terv útmutató megfeleljen egy alkalmazásból elküldött kötegnek, a @module_or_batch az SQL Servernek küldött karakteres formátumban kell megadni. Az egyeztetés megkönnyítése érdekében nem történik belső átalakítás. További információ: Megjegyzések szakasz.

[ <schema_name>. ] <object_name> egy Transact-SQL tárolt eljárás, skaláris függvény, többstatement táblaértékű függvény vagy Transact-SQL @stmt tartalmazó DML-eseményindító nevét adja meg. Ha <schema_name> nincs megadva, <schema_name> az aktuális felhasználó sémáját használja. Ha NULL meg van adva, és @type, SQLa @module_or_batch értéke a @stmt értékére van állítva. Ha @type , TEMPLATEakkor @module_or_batch kell lennie NULL.

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

Megadja a @stmt beágyazott összes paraméter definícióját. @paramsnvarchar(max), alapértelmezés szerint NULL. @params csak akkor érvényes, ha az alábbi lehetőségek valamelyike igaz:

• @type vagy SQLTEMPLATE. Ha TEMPLATEa @params nem lehet NULL.

• @stmt a rendszer a használatával sp_executesql küldi el, és meg van adva a @params paraméter értéke, vagy az SQL Server belsőleg küld el egy utasítást a paraméterezés után. Az adatbázis API-kból (beleértve az ODBC-ből, az OLE DB-ből és a ADO.NET) származó paraméteres lekérdezések elküldése az SQL Servernek az API-kiszolgáló kurzorának hívásaként vagy felé irányuló rutinként sp_executesql jelenik meg, ezért a segédvonalakkal is megfeleltethetők SQL vagy TEMPLATE megtervezhetők.

@params pontosan ugyanabban a formátumban kell megadni, mint amelyet az SQL Servernek sp_executesql a paraméterezés után használ vagy belsőleg küld el. További információ: Megjegyzések szakasz. Ha a köteg nem tartalmaz paramétereket, NULL meg kell adni. A @params méretét csak a rendelkezésre álló kiszolgálómemória korlátozza.

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

@hintsnvarchar(max), alapértelmezés szerint NULL.

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

  OPTION A @stmt egyező lekérdezéshez csatolandó záradékot ad meg. @hints szintaktikailag meg kell egyeznie egy OPTION utasítás záradékávalSELECT, és bármilyen érvényes lekérdezési tippsorozatot tartalmazhat.

• <XML_showplan>'

  A lekérdezési terv XML formátumban, emlékeztetőként alkalmazva.

  Javasoljuk, hogy rendelje hozzá az XML-bemutatótervet egy változóhoz. Ellenkező esetben a bemutatótervben szereplő idézőjeleket úgy kell feloldani, hogy azokat egy másik idézőjel előzi meg. Lásd E.

• NULL

  Azt jelzi, hogy a OPTION lekérdezés záradékában megadott meglévő tipp nincs alkalmazva a lekérdezésre. További információ: OPTION záradék.

Megjegyzések

A megjelenítendő sorrendben meg kell adni az argumentumokat sp_create_plan_guide . Ha értékeket ad meg a paraméterekhez sp_create_plan_guide, az összes paraméternevet explicit módon kell megadni, vagy egyáltalán nem. Ha például @name = meg van adva, akkor @stmt =@type =is meg kell adni, és így tovább. Hasonlóképpen, ha @name = nincs megadva, és csak a paraméterérték van megadva, a fennmaradó paraméterneveket is ki kell hagyni, és csak az értékeket kell megadni. Az argumentumnevek csak leíró célokra szolgálnak a szintaxis megértéséhez. Az SQL Server nem ellenőrzi, hogy a megadott paraméternév megegyezik-e a paraméter nevével abban a pozícióban, ahol a nevet használják.

Több vagy terv OBJECTSQL útmutatót is létrehozhat ugyanahhoz a lekérdezéshez, köteghez vagy modulhoz. Egy adott időpontban azonban csak egy terv útmutatója engedélyezhető.

A terv típusú OBJECT segédvonalak nem hozhatók létre olyan @module_or_batch értékhez, amely a záradékot meghatározó WITH ENCRYPTION vagy ideiglenes tárolt eljárásra, függvényre vagy DML-eseményindítóra hivatkozik.

Egy tervútmutató által hivatkozott függvény, tárolt eljárás vagy DML-eseményindító elvetése vagy módosítása hibát okoz. Ha egy olyan táblát próbál elvetni, amely egy olyan eseményindító, amelyet egy terv útmutatója hivatkozik rá, szintén hibát okoz.

A terv-útmutatók nem használhatók az SQL Server minden kiadásában. Az SQL Server kiadásai által támogatott funkciók listáját az SQL Server 2022 Kiadásai és támogatott funkciói című témakörben találja. A terv útmutatói bármely kiadásban láthatók. Olyan adatbázist is csatolhat, amely terv-útmutatókat tartalmaz bármely kiadáshoz. Az SQL Server frissített verziójához való adatbázis visszaállítása vagy csatolása során a tervek útmutatói érintetlenek maradnak. A kiszolgálófrissítés végrehajtása után ellenőriznie kell, hogy a terv-útmutatók nem kívánatosak-e az egyes adatbázisokban.

A terv útmutatója megfeleltetési követelményei

A lekérdezések @type vagy SQL sikeres egyeztetését TEMPLATE meghatározó tervkalauzok esetében a @module_or_batch és a @params [, ... n ] pontosan ugyanolyan formátumban kell megadni, mint a kérelem által benyújtott társaikat. Ez azt jelenti, hogy pontosan úgy kell megadnia a kötegszöveget, ahogy az SQL Server fordítója megkapja. A köteg és a paraméter tényleges szövegének rögzítéséhez használhatja az SQL Server Profilert. További információ: Az SQL Server Profiler használata terv-útmutatók létrehozásához és teszteléséhez.

Ha @type van SQL , és @module_or_batch van beállítva NULL, a @module_or_batch értéke a @stmt értékére van állítva. Ez azt jelenti, hogy a @stmt értékét pontosan ugyanazzal a formátumban kell megadni, karakter for-character formátumban, mint amelyet az SQL Servernek küldött. Az egyeztetés megkönnyítése érdekében nem történik belső átalakítás.

Ha az SQL Server megfelel a @stmt értékének @module_or_batch és @params [, ... n ], vagy ha aOBJECT@type a megfelelő lekérdezés <object_name>szövegéhez tartozik, akkor a rendszer nem veszi figyelembe a következő sztringelemeket:

• Szóközök (lapok, szóközök, kocsivisszajelek vagy vonalcsatornák) a sztringen belül
• Megjegyzések (-- vagy /* */)
• Záró pontosvesszők

Az SQL Server például megfelelhet a @stmt sztringnek N'SELECT * FROM T WHERE a = 10' a következő @module_or_batch:

 N'SELECT *
 FROM T
 WHERE a = 10'

Ugyanez a sztring azonban nem felelne meg a következő @module_or_batch:

N'SELECT * FROM T WHERE b = 10'

Az SQL Server figyelmen kívül hagyja az első lekérdezésben a kocsivisszatért, a vonalcsatornát és a szóköz karaktereket. A második lekérdezésben a szekvenciát a rendszer WHERE b = 10 másképp értelmezi, mint WHERE a = 10a . Az egyezés megkülönbözteti a kis- és nagybetűket (még akkor is, ha az adatbázis rendezése kis- és nagybetűket nem érzékeny), kivéve, ha vannak kulcsszavak, ahol a kis- és nagybetűk érzéketlenek. Az egyezés érzékeny az üres szóközökre. Az egyeztetés érzéketlen a rövidített kulcsszavakhoz. Például a kulcsszavak EXECUTE, EXECés execute egyenértékűnek tekinthetők.

A terv útmutatójának hatása a tervgyorsítótárra

Egy modul tervútmutatójának létrehozása eltávolítja a modul lekérdezési tervét a tervgyorsítótárból. Egy típus- OBJECT vagy SQL kötegterv-útmutató létrehozása eltávolítja egy olyan köteg lekérdezési tervét, amely azonos kivonatérték. A típusterv-útmutató TEMPLATE létrehozása eltávolítja az összes egyutas köteget az adatbázis tervgyorsítótárából.

Engedélyek

A terv típusú OBJECTútmutató létrehozásához engedélyre van szükség ALTER a hivatkozott objektumon. A terv SQL típusú útmutató létrehozásához vagy TEMPLATEaz aktuális adatbázis engedélyére van szükség ALTER .

Példák

Egy. Objektum típusú tervútmutató létrehozása tárolt eljárásban lévő lekérdezéshez

Az alábbi példa létrehoz egy tervútmutatót, amely megfelel egy alkalmazásalapú tárolt eljárás kontextusában végrehajtott lekérdezésnek, és alkalmazza a OPTIMIZE FOR tippet a lekérdezésre.

A tárolt eljárás a következő:

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

A tárolt eljárásban a lekérdezésen létrehozott terv útmutatója:

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. Sql típusú tervútmutató létrehozása önálló lekérdezéshez

Az alábbi példa egy terv útmutatót hoz létre, amely megfelel a rendszer által tárolt eljárást használó sp_executesql alkalmazás által küldött kötegben lévő lekérdezésnek.

A köteg a következő:

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

Ha meg szeretné akadályozni, hogy párhuzamos végrehajtási terv készüljön ezen a lekérdezésen, hozza létre a következő terv útmutatót:

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. Sablon típusú tervútmutató létrehozása a lekérdezés paraméteres formájához

Az alábbi példa létrehoz egy tervútmutatót, amely megfelel a megadott űrlapra paraméterezendő lekérdezéseknek, és az SQL Servert a lekérdezés paraméterezésének kényszerítésére utasítja. A következő két lekérdezés szintaktikailag egyenértékű, de csak az állandó literális értékekben különbözik.

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;

A lekérdezés paraméteres formájának terv útmutatója:

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

Az előző példában a @stmt paraméter értéke a lekérdezés paraméteres formája. Ennek az értéknek sp_create_plan_guide az egyetlen megbízható módja a sp_get_query_template rendszer által tárolt eljárás használata. A következő szkript lekérte a paraméteres lekérdezést, majd létrehoz egy terv útmutatót rajta.

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

Fontos

A @stmt paraméter sp_get_query_template-hez való átadásakor a konstansok értéke befolyásolhatja a konstanst helyettesítő paraméterhez kiválasztott adattípust. Ez hatással lesz a terv útmutatóinak egyeztetésére. Előfordulhat, hogy több terv útmutatót kell létrehoznia a különböző paraméterértéktartományok kezeléséhez.

D. Terv útmutató létrehozása egy API-kurzorkéréssel elküldött lekérdezéshez

A terv segédvonalai megfelelnek az API-kiszolgáló kurzorainak rutinjaiból küldött lekérdezéseknek. Ezek a rutinok a következők: sp_cursorprepare, sp_cursorprepexecés sp_cursoropen. Az ADO, az OLE DB és az ODBC API-kat használó alkalmazások gyakran api-kiszolgálói kurzorokkal használják az SQL Servert. A profiler nyomkövetési eseményének megtekintésével láthatja az API-kiszolgálói kurzor rutinjainak meghívását az RPC:Starting SQL Server Profiler-nyomkövetésekben.

Tegyük fel, hogy a következő adatok jelennek meg egy RPC:Starting profilkészítő nyomkövetési eseményben egy tervútmutatóval hangolni kívánt lekérdezéshez:

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;

Megfigyelheti, hogy a SELECT hívásban sp_cursorprepexec szereplő lekérdezés terve egy egyesítési illesztés, de kivonatos illesztés használatát szeretné használni. A használatával sp_cursorprepexec elküldött lekérdezés paraméteres, beleértve a lekérdezési sztringet és a paramétersztringet is. Az alábbi tervútmutatót úgy módosíthatja, hogy a lekérdezés és a paraméter sztringjei pontosan úgy jelennek meg, ahogyan azok megjelennek, a karakter karaktere a hívásban 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)';

A terv útmutatója a lekérdezés alkalmazás általi későbbi végrehajtását érinti, és kivonat-illesztés használatával dolgozza fel a lekérdezést.

E. Tervútmutató létrehozása az XML-bemutatóterv gyorsítótárazott tervből való lekérésével

Az alábbi példa egy egyszerű ad hoc SQL utasításhoz hoz létre egy terv útmutatót. Az utasítás kívánt lekérdezési tervét a terv útmutatója tartalmazza a lekérdezés XML-bemutatótervének közvetlenül a @hints paraméterben való megadásával. A példa először végrehajtja az SQL utasítást egy terv létrehozásához a terv gyorsítótárában. Ebben a példában feltételezzük, hogy a létrehozott terv a kívánt terv, és nincs szükség további lekérdezéshangolásra. A lekérdezés XML-bemutatótervét a rendszer a sys.dm_exec_query_stats, sys.dm_exec_sql_textés sys.dm_exec_text_query_plan dinamikus felügyeleti nézetek lekérdezésével szerzi be, és a @xml_showplan változóhoz van rendelve. A @xml_showplan változó ezután a paraméterben található sp_create_plan_guide utasításnak @hints lesz átadva. A tervgyorsítótárban lévő lekérdezési tervből is létrehozhat tervkalauzt a sp_create_plan_guide_from_handle tárolt eljárás használatával.

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

Kapcsolódó tartalom

• Terv útmutatók
• sp_control_plan_guide (Transact-SQL)
• sys.plan_guides (Transact-SQL)
• adatbázismotor tárolt eljárásai (Transact-SQL)
• rendszer által tárolt eljárások (Transact-SQL)
• sys.dm_exec_sql_text (Transact-SQL)
• sys.dm_exec_cached_plans (Transact-SQL)
• sys.dm_exec_query_stats (Transact-SQL)
• sp_create_plan_guide_from_handle (Transact-SQL)
• sys.fn_validate_plan_guide (Transact-SQL)
• sp_get_query_template (Transact-SQL)