sp_create_plan_guide (Transact-SQL)
Si applica a: SQL Server
database SQL di Azure
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 relative alla sintassi Transact-SQL
Sintassi
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' } ]
[ ; ]
Argomenti
[ @name = ] N'name'
Nome della guida di piano. @name è sysname, senza impostazione predefinita e una lunghezza massima di 124 caratteri. I nomi delle guide di piano vengono definiti a livello dell'ambito del database corrente. @name deve essere conforme alle regole per gli identificatori e non può iniziare con il segno di numero (#
).
[ @stmt = ] N'stmt'
Istruzione Transact-SQL in base alla quale creare una guida di piano. @stmt è nvarchar(max), con il valore predefinito NULL
. Quando Query Optimizer di SQL Server riconosce una query che corrisponde a @stmt, @name diventa effettiva. Affinché la creazione di una guida di piano abbia esito positivo, @stmt deve essere visualizzato nel contesto specificato dai parametri @type, @module_or_batch e @params .
@stmt deve essere fornito in modo che query Optimizer corrisponda all'istruzione corrispondente, fornita all'interno del batch o del modulo identificato da @module_or_batch e @params. Per altre informazioni, vedere la sezione Osservazioni. Le dimensioni di @stmt sono limitate solo dalla memoria disponibile del server.
[ @type = ] { N'OBJECT' | N'SQL' | N'TEMPLATE' }
Tipo di entità in cui viene visualizzato @stmt . Specifica il contesto per la corrispondenza di @stmt a @name. @type è nvarchar(60)e può essere uno di questi valori:
OBJECT
Indica @stmt 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 @stmt 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, di conseguenza, devono essere identificate come @type diSQL
. SeSQL
viene specificato, l'hintPARAMETERIZATION { FORCED | SIMPLE }
per la query 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 @stmt. Se
TEMPLATE
viene specificato, è possibile specificare solo l'hintPARAMETERIZATION { FORCED | SIMPLE }
per la query nel parametro @hints . Per altre informazioni sulle guide diTEMPLATE
piano, vedere Specificare il comportamento di parametrizzazione delle query tramite guide di piano.
[ @module_or_batch = ] { N' [ schema_name. ] object_name' | N'batch_text' }
Specifica il nome dell'oggetto in cui viene visualizzato @stmt o il testo batch in cui viene visualizzato @stmt . @module_or_batch è nvarchar(max), con il valore predefinito NULL
. Il testo del batch non può includere un'istruzione USE <database>
.
Affinché una guida di piano corrisponda a un batch inviato da un'applicazione, è necessario specificare @module_or_batch nello stesso formato, carattere per carattere, come 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, una funzione scalare, una funzione con valori di tabella multistatement o un trigger DML Transact-SQL contenente @stmt. Se <schema_name>
non viene specificato, <schema_name>
usa lo schema dell'utente corrente. Se NULL
viene specificato 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 ]'
Specifica le definizioni di tutti i parametri incorporati in @stmt. @params è nvarchar(max), con il valore predefinito NULL
. @params si applica solo quando una delle opzioni seguenti è vera:
@type è
SQL
oTEMPLATE
. SeTEMPLATE
, @params non deve essereNULL
.@stmt viene inviato usando
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 a o allesp_executesql
routine del cursore del server API, pertanto possono essere abbinate anche alleSQL
guide di piano oTEMPLATE
.
@params 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 contiene parametri, NULL
è necessario specificare . Le dimensioni di @params sono limitate solo dalla memoria del server disponibile.
[ @hints = ] { N'OPTION ( query_hint [ , ... n ] )' | N'XML_showplan' }
@hints è nvarchar(max), con il valore predefinito NULL
.
OPTION ( <query_hint> [ , ...n ] )
Specifica una
OPTION
clausola da associare a una query che corrisponde a @stmt. @hints deve essere sintatticamente uguale a unaOPTION
clausola in un'istruzioneSELECT
e può contenere qualsiasi sequenza valida di hint per la query.<XML_showplan>'
Piano di query in formato XML da applicare come hint.
È consigliabile assegnare lo showplan XML a una variabile. In caso contrario, è necessario eseguire l'escape di tutte le virgolette singole nel showplan precedendole con un'altra virgoletta singola. Vedere l'esempio E.
NULL
Indica che qualsiasi hint esistente specificato nella
OPTION
clausola della query non viene applicato alla query. Per altre informazioni, vedere clausola OPTION.
Osservazioni:
Gli argomenti da specificare sp_create_plan_guide
nell'ordine visualizzato. Quando si specificano 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 OBJECT
di piano o SQL
per la stessa query e lo stesso 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 WITH ENCRYPTION
clausola o che è temporanea.
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. Se si tenta di eliminare una tabella definita da un trigger a cui fa riferimento una guida di piano, viene generato anche un errore.
Le guide di piano non possono essere usate in ogni edizione di SQL Server. Per un elenco delle funzionalità supportate dalle varie edizioni di SQL Server, vedere Edizioni e funzionalità supportate di SQL Server 2017. Le guide di piano sono visibili in qualsiasi edizione. È inoltre possibile collegare un database che contiene guide di piano a qualsiasi edizione. Quando si ripristina o si collega un database a una versione aggiornata di SQL Server, le guide di piano non vengono modificate. Dopo l'esecuzione di un aggiornamento del server è opportuno verificare l'effettiva necessità delle guide di piano di ogni database.
Requisiti di corrispondenza della guida di piano
Per le guide di piano che specificano @type di SQL
o TEMPLATE
per trovare correttamente una corrispondenza con una query, i valori per @module_or_batch e @params [, ... 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 è e @module_or_batch è impostato su NULL
, il valore di @module_or_batch viene impostato sul valore di @stmt. SQL
Ciò significa che il valore per @stmt 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 @stmt a @module_or_batch e @params [, ... n ], o se @type è OBJECT
, al testo della query corrispondente all'interno <object_name>
di , gli elementi stringa seguenti non vengono considerati:
- Spazi vuoti (tabulazioni, spazi, ritorni a capo o avanzamenti 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'
di @stmt alla @module_or_batch seguente:
N'SELECT *
FROM T
WHERE a = 10'
Tuttavia, la stessa stringa non corrisponde a questa @module_or_batch:
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
. La corrispondenza fa distinzione tra maiuscole e minuscole e distinzione tra caratteri accentati (anche quando le regole di confronto del database non fanno distinzione tra maiuscole e minuscole), tranne se sono presenti parole chiave, in cui la distinzione tra maiuscole e minuscole è senza distinzione tra maiuscole e minuscole. 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 sulla 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
in 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 con istruzione singola dalla cache dei piani all'interno di tale 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. Creare 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
.
Ecco 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
Ecco la guida di piano creata nella 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. Creare 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.
Ecco 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. Creare una guida di piano di tipo TEMPLATE per il formato con parametri di una query
Nel seguente esempio viene creata una guida di piano corrispondente a qualsiasi query che parametrizza un formato specifico e forza in SQL Server l'esecuzione della 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;
Ecco 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 utilizzare in sp_create_plan_guide
consiste nell'usare la stored procedure di sistema sp_get_query_template . Lo script seguente ottiene la query con parametri e quindi crea una guida di piano.
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)';
Importante
Il valore letterale costante nel parametro @stmt
passato a sp_get_query_template
potrebbe interessare il tipo di dati scelto per il parametro che sostituisce il valore letterale. Ciò potrebbe avere ripercussioni sulla corrispondenza eseguita in base alla guida di piano. Potrebbe essere necessario creare più guide di piano per gestire intervalli di valori di parametro diversi.
D. Creare una guida di piano su una query inviata usando 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. Queste 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 del RPC:Starting
profiler.
Si supponga che i dati seguenti siano visualizzati in un RPC:Starting
evento di traccia del profiler per una query da 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 sono interessate da questa guida di piano e un hash join viene usato per elaborare la query.
E. Creare 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 ad hoc SQL
. Il piano di query desiderato per questa istruzione viene fornito nella guida di piano specificando lo showplan XML per la query direttamente nel @hints
parametro . L'esempio esegue prima 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 e che non sia necessaria un'ulteriore ottimizzazione delle query. Lo showplan XML per la query viene ottenuto eseguendo una query sulle sys.dm_exec_query_stats
viste a sys.dm_exec_sql_text
gestione dinamica , e sys.dm_exec_text_query_plan
e viene assegnato alla @xml_showplan
variabile . 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
Contenuto correlato
- Guide di piano
- sp_control_plan_guide (Transact-SQL)
- sys.plan_guides (Transact-SQL)
- stored procedure 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)