sp_create_plan_guide (Transact-SQL)

Si applica a:SQL ServerDatabase SQL diAzure Istanza gestita di SQL di Azure

Crea una guida di piano per associare gli hint per le query o gli effettivi piani di query con le query in un database. Per altre informazioni sulle guide di piano, vedere Guide di piano.

Convenzioni di sintassi Transact-SQL

Sintassi

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 }  

Argomenti

[ @name = ] N'plan_guide_name'
Nome della guida di piano. I nomi delle guide di piano vengono definiti a livello dell'ambito del database corrente. plan_guide_name deve essere conforme alle regole per gli identificatori e non può iniziare con il segno di numero (#). La lunghezza massima di plan_guide_name è di 124 caratteri.

[ @stmt = ] N'statement_text'
Istruzione Transact-SQL in base alla quale creare una guida di piano. Quando Query Optimizer di SQL Server riconosce una query che corrisponde a statement_text, plan_guide_name diventa effettiva. Affinché la creazione di una guida di piano abbia esito positivo, statement_text deve essere visualizzato nel contesto specificato dai parametri @type, @module_or_batch e @params.

statement_text deve essere fornito in modo da consentire a Query Optimizer di associarlo all'istruzione corrispondente fornita all'interno del batch o del modulo identificato da @module_or_batch e @params. Per ulteriori informazioni, vedere la sezione "Osservazioni". Le dimensioni di statement_text sono limitate solo dalla memoria disponibile del server.

[@type = ] N'{ OBJECT | SQL | TEMPLATE }'
Tipo di entità in cui viene visualizzata statement_text . Specifica il contesto per la corrispondenza di statement_text a plan_guide_name.

OBJECT
Indica statement_text viene visualizzato nel contesto di una stored procedure Transact-SQL, una funzione scalare, una funzione con valori di tabella multistatement o un trigger DML Transact-SQL nel database corrente.

SQL
Indica che statement_text viene visualizzato nel contesto di un'istruzione o di un batch autonomo che può essere inviato a SQL Server tramite qualsiasi meccanismo. Le istruzioni Transact-SQL inviate da oggetti CLR (Common Language Runtime) o stored procedure estese o tramite EXEC N'sql_string' vengono elaborate come batch nel server e pertanto devono essere identificate come @type = 'SQL'. Se SI specifica SQL, l'hint della query PARAMETERIZATION { FORCED | SIMPLE } non può essere specificato nel parametro @hints.

TEMPLATE
Indica che la guida di piano si applica a qualsiasi query che parametrizza al modulo indicato in statement_text. Se si specifica TEMPLATE, solo PARAMETERIZATION { FORCED | L'hint per la query SIMPLE } può essere specificato nel parametro @hints. Per altre informazioni sulle guide di piano MODELLO, vedere Specificare il comportamento di parametrizzazione delle query tramite guide di piano.

[@module_or_batch =] { N'[ schema_name. ] object_name' | N'batch_text' | NULL }
Specifica il nome dell'oggetto in cui viene visualizzato statement_text o il testo batch in cui viene visualizzato statement_text . Il testo del batch non può includere un'istruzione USEdatabase .

Affinché una guida di piano corrisponda a un batch inviato da un'applicazione, batch_text deve essere fornita nello stesso formato, carattere per carattere, come viene inviato a SQL Server. Per semplificare questa corrispondenza, non viene eseguita alcuna conversione interna. Per altre informazioni, vedere la sezione Osservazioni.

[schema_name.] object_name specifica il nome di una stored procedure Transact-SQL, di una funzione scalare, di una funzione con valori di tabella multistatement o di un trigger DML Transact-SQL contenente statement_text. Se schema_name non viene specificato, schema_name usa lo schema dell'utente corrente. Se viene specificato NULL e @type = 'SQL', il valore di @module_or_batch viene impostato sul valore di @stmt. Se @type = 'TEMPLATE**'**, @module_or_batch deve essere NULL.

[ @params = ] { N'@parameter_name data_type [ ,... n ]' | NULL }
Specifica le definizioni di tutti i parametri incorporati in statement_text. @params si applica solo quando una delle condizioni seguenti è vera:

• @type = 'SQL' o 'TEMPLATE'. Se 'TEMPLATE', @params non deve essere NULL.

• statement_text viene inviato tramite sp_executesql e viene specificato un valore per il parametro @params oppure SQL Server invia internamente un'istruzione dopo la parametrizzazione. L'invio di query con parametri dalle API di database (inclusi ODBC, OLE DB e ADO.NET) viene visualizzato in SQL Server come chiamate alle sp_executesql routine del cursore del server API o alle routine del cursore del server API, pertanto possono essere abbinate anche alle guide di piano SQL o TEMPLATE.

@parameter_name data_type deve essere fornito nello stesso formato inviato a SQL Server usando sp_executesql o inviato internamente dopo la parametrizzazione. Per altre informazioni, vedere la sezione Osservazioni. Se il batch non include parametri, è necessario specificare NULL. Le dimensioni di @params sono limitate solo dalla memoria del server disponibile.

[@hints = ] { N'OPTION (query_hint [ ,... n ] )' | N'XML_showplan' | NULL }
N'OPTION (query_hint [ ,... n ] )
Specifica una clausola OPTION da associare a una query che corrisponde a @stmt. @hints deve essere sintatticamente uguale a una clausola OPTION in un'istruzione SELECT e può contenere qualsiasi sequenza valida di hint per la query.

N'XML_showplan'
Il piano di query in formato XML da applicare come hint.

Si consiglia di assegnare Showplan XML a una variabile; in caso contrario, è necessario utilizzare caratteri di escape per ogni virgoletta singola nello Showplan facendola precedere da un'altra virgoletta singola. Vedere l'esempio E.

NULL
Indica che tutti gli hint esistenti specificati nella clausola OPTION della query non vengono applicati alla query. Per altre informazioni, vedere Clausola OPTION (Transact-SQL).

Osservazioni:

Gli argomenti per sp_create_plan_guide devono essere inseriti nell'ordine illustrato. Quando si forniscono valori per i parametri di sp_create_plan_guide, è necessario specificare in modo esplicito tutti i nomi dei parametri oppure nessuno. Se ad esempio si specifica @name =, è necessario specificare anche @stmt =, @type = e così via. Analogamente, se @name = viene omesso e viene specificato soltanto il valore del parametro, è necessario omettere anche i nomi dei parametri restanti e specificarne solo il valore. I nomi degli argomenti hanno scopo esclusivamente descrittivo, per facilitare la comprensione della sintassi. SQL Server non verifica che il nome del parametro specificato corrisponda al nome del parametro nella posizione in cui viene usato il nome.

È possibile creare più guide di piano OBJECT o SQL per la stessa query e batch o modulo. Tuttavia è possibile abilitare una sola guida di piano alla volta.

Non è possibile creare guide di piano di tipo OBJECT per un valore @module_or_batch che fa riferimento a una stored procedure, una funzione o un trigger DML che specifica la clausola WITH ENCRYPTION o è temporaneo.

Se si tenta di eliminare o modificare una funzione, una stored procedure o un trigger DML a cui viene fatto riferimento in una guida di piano abilitata o disabilitata, viene generato un errore. Viene generato un errore anche se si cerca di eliminare una tabella per la quale è stato definito un trigger a cui una guida di piano fa riferimento.

Requisiti di corrispondenza della Guida di piano

Per le guide di piano che specificano @type = 'SQL' o @type = 'TEMPLATE' in modo che corrispondano correttamente a una query, i valori per batch_text e @parameter_name data_type [,... n ] deve essere fornito esattamente nello stesso formato delle controparti inviate dall'applicazione. Ciò significa che è necessario specificare il testo batch esattamente come lo riceve il compilatore di SQL Server. Per acquisire il testo effettivo del batch e del parametro, è possibile usare SQL Server Profiler. Per altre informazioni, vedere Usare SQL Server Profiler per creare e testare le guide di piano.

Quando @type = 'SQL' e @module_or_batch è impostato su NULL, il valore di @module_or_batch viene impostato sul valore di @stmt. Ciò significa che il valore per statement_text deve essere fornito esattamente nello stesso formato, carattere per carattere, come viene inviato a SQL Server. Per semplificare questa corrispondenza, non viene eseguita alcuna conversione interna.

Quando SQL Server corrisponde al valore di statement_text a batch_text e @parameter_name data_type [,... n ], o se @type = **'**OBJECT', al testo della query corrispondente all'interno di object_name, gli elementi stringa seguenti non vengono considerati:

• Spazi vuoti (tabulazioni, spazi, ritorni a capo o avanzamenti di riga) all'interno della stringa.

• Commenti (-- o /* */).

• Punto e virgola finale.

Ad esempio, SQL Server può corrispondere alla stringa N'SELECT * FROM T WHERE a = 10' statement_text alla batch_text seguente:

N'SELECT *
FROM T
WHERE a = 10' 

Tuttavia, la stessa stringa non corrisponde a questa batch_text:

N'SELECT * FROM T WHERE b = 10'

SQL Server ignora i caratteri ritorno a capo, avanzamento riga e spazio all'interno della prima query. Nella seconda query la sequenza WHERE b = 10 viene interpretata in modo diverso da WHERE a = 10. Nella corrispondenza viene sempre fatta distinzione tra maiuscole e minuscole e tra caratteri accentati e non accentati, anche quando la distinzione tra maiuscole e minuscole non è significativa per le regole di confronto del database, tranne il caso delle parole chiave, dove la distinzione tra maiuscole e minuscole non è significativa. La corrispondenza è sensibile agli spazi vuoti. La forma abbreviata delle parole chiave non è rilevante nella corrispondenza. Ad esempio, le parole chiave EXECUTE, EXEC e execute vengono considerate equivalenti.

Effetto della guida di piano nella cache dei piani

La creazione di una guida di piano su un modulo rimuove il piano di query per il dato modulo dalla cache dei piani. La creazione di una guida di piano di tipo OBJECT o SQL su un batch rimuove il piano di query per un batch con lo stesso valore hash. La creazione di una guida di piano di tipo TEMPLATE rimuove tutti i batch a istruzione singola dalla cache dei piani all'interno del database.

Autorizzazioni

Per creare una guida di piano di tipo OBJECT, è necessaria ALTER l'autorizzazione per l'oggetto a cui si fa riferimento. Per creare una guida di piano di tipo SQL o TEMPLATE, è necessaria ALTER l'autorizzazione per il database corrente.

Esempi

R. Creazione di una guida di piano di tipo OBJECT per una query in una stored procedure

Nell'esempio seguente viene creata una guida di piano corrispondente a una query eseguita nel contesto di una stored procedure basata sull'applicazione e alla query viene applicato l'hint OPTIMIZE FOR.

Di seguito è riportata la stored 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  

Di seguito è riportata la guida di piano creata in base alla query nella stored procedure:

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. Creazione di una guida di piano di tipo SQL per una query autonoma

Nell'esempio seguente viene creata una guida di piano per trovare una corrispondenza con una query in un batch inviato da un'applicazione che usa la sp_executesql stored procedure di sistema.

Di seguito è riportato il batch:

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

Per evitare la generazione di un piano di esecuzione parallela in base a questa query, creare la guida di piano seguente:

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. Creazione di una guida di piano di tipo TEMPLATE per il formato con parametri di una query

Nell'esempio seguente viene creata una guida di piano che corrisponde a qualsiasi query che parametrizza in un modulo specificato e indirizza SQL Server a forzare la parametrizzazione della query. Le due query seguenti sono equivalenti a livello sintattico. L'unica differenza risiede nei relativi valori letterali costanti.

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;  

Di seguito è riportata la guida di piano nel formato con parametri della query:

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

Nell'esempio precedente il valore del parametro @stmt corrisponde al formato con parametri della query. L'unico modo affidabile per ottenere questo valore da usare in sp_create_plan_guide è usare la stored procedure di sistema sp_get_query_template . Lo script seguente può essere utilizzato sia per ottenere la query con parametri che per creare una guida di piano in base a essa.

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

D. Creazione di una guida di piano in una query inviata utilizzando una richiesta di cursore API

È possibile eseguire la corrispondenza tra le guide di piano e le query inviate da routine dei cursori API del server. Le routine includono sp_cursorprepare, sp_cursorprepexec e sp_cursoropen. Le applicazioni che usano le API ADO, OLE DB e ODBC interagiscono spesso con SQL Server tramite cursori server API. È possibile visualizzare la chiamata delle routine del cursore del server API nelle tracce di SQL Server Profiler visualizzando l'evento di traccia RPC:Starting profiler.

Si supponga che in un evento di traccia RPC:Starting del profiler vengano visualizzati i seguenti dati per una query che si desidera ottimizzare con una guida di piano:

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;  

Si noti che il piano per la query SELECT nella chiamata a sp_cursorprepexec utilizza un merge join. Si desidera utilizzare invece un hash join. La query sottomessa tramite sp_cursorprepexec viene parametrizzata e include una stringa query e una stringa parametro. È possibile creare la guida di piano seguente per modificare la scelta del piano utilizzando le stringhe query e parametro esattamente come sono, carattere per carattere, nella chiamata a 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)';  

Le esecuzioni successive di questa query da parte dell'applicazione saranno interessate da questa guida di piano e per l'elaborazione della query verrà utilizzato un hash join.

E. Creazione di una guida di piano ottenendo lo Showplan XML da un piano memorizzato nella cache

Nell'esempio seguente viene creata una guida di piano per una semplice istruzione SQL ad hoc. Il piano di query desiderato per questa istruzione è fornito nella guida di piano specificando lo Showplan XML per la query direttamente nel parametro @hints . Viene innanzitutto eseguita l'istruzione SQL per generare un piano nella cache dei piani. Ai fini di questo esempio, si presuppone che il piano generato sia il piano desiderato, senza che sia richiesta alcuna ottimizzazione aggiuntiva della query. Lo Showplan XML per la query si ottiene eseguendo una query sulle viste a gestione dinamica sys.dm_exec_query_stats, sys.dm_exec_sql_texte sys.dm_exec_text_query_plan e assegnandolo alla variabile @xml_showplan . La variabile @xml_showplan passa quindi all'istruzione sp_create_plan_guide nel parametro @hints . In alternativa, è possibile creare una guida di piano da un piano di query nella cache dei piani usando la stored procedure 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  

Vedi anche

Guide di piano
sp_control_plan_guide (Transact-SQL)
sys.plan_guides (Transact-SQL)
Stored procedure del motore di database (Transact-SQL)
Stored procedure di sistema (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)