sp_create_plan_guide (Transact-SQL)

Data aggiornamento: 15 settembre 2007

Crea una guida di piano per l'associazione degli hint per la query con le query in un database. Per ulteriori informazioni sulle guide di piano, vedere Ottimizzazione delle query nelle applicazioni distribuite tramite le guide di piano.

Convenzioni della 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 ] )' | NULL }

Argomenti

• [ @name= ] N'plan_guide_name'
  Specifica il nome in base al quale identificare la guida di piano. I nomi delle guide di piano sono definiti in base all'ambito del database corrente. plan_guide_name deve essere conforme alle regole per gli identificatori e non può iniziare con il simbolo di cancelletto (#).

• [ @stmt= ] N'statement_text'
  Istruzione Transact-SQL in cui creare una guida di piano. Quando Query Optimizer di SQL Server rileva una query corrispondente a statement_text, plan_guide_name diventa operativo. Affinché la creazione di una guida di piano abbia esito positivo, è necessario che statement_text sia incluso nel contesto specificato dai parametri @type, @module_or_batch e @params.

  statement_text deve essere specificato in modo tale da consentire a SQL Server di associarlo all'istruzione corrispondente definita all'interno del batch o del modulo identificato dai parametri @module_or_batch e @params. statement_text viene convertito in un formato interno standard prima che SQL Server cerchi tale corrispondenza (gli spazi, i commenti e la sequenza di maiuscole/minuscole nella parola chiave non sono significativi). Per ulteriori informazioni, vedere la sezione Osservazioni. La dimensione di statement_text è limitata solo dalla memoria disponibile del server.

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

  • OBJECT
    Indica che statement_text appare nel contesto di una stored procedure Transact-SQL, una funzione scalare, una funzione a istruzioni multiple valutata a livello di tabella oppure un trigger DML Transact-SQL nel database corrente.
  • SQL
    Indica che statement_text appare nel contesto di un'istruzione autonoma o di un batch che può essere sottomesso a SQL Server tramite qualsiasi meccanismo. Le istruzioni Transact-SQL sottomesse dagli oggetti CLR (Common Language Runtime) o dalle stored procedure estese oppure tramite l'utilizzo di EXEC N'sql_string' vengono elaborate come batch nel server e pertanto devono essere identificate come @type ='SQL'. Se si specifica SQL, non è possibile specificare l'hint per la query PARAMETERIZATION { FORCED | SIMPLE } nel parametro @hints.
  • TEMPLATE
    Indica che la guida di piano viene applicata a qualsiasi query che parametrizza il form indicato in statement_text. Se si specifica TEMPLATE, è possibile specificare solo l'hint per la query PARAMETERIZATION { FORCED | SIMPLE } nel parametro @hints. Per ulteriori informazioni sulle guide di piano di tipo TEMPLATE, vedere Definizione delle funzionalità 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 appare statement_text oppure il testo del batch in cui appare statement_text. Il testo del batch non può includere un'istruzione USE database.

  Affinché una guida di piano corrisponda a un batch sottomesso da un'applicazione, è necessario che batch_text venga specificato nello stesso formato, carattere per carattere, con cui è stato sottomesso a SQL Server. Per semplificare questa corrispondenza, non viene eseguita alcuna conversione interna. Per ulteriori informazioni, vedere la sezione Osservazioni.

  [schema_name.]object_name specifica il nome di una stored procedure Transact-SQL, una funzione scalare, una funzione a istruzioni multiple valutata a livello di tabella oppure un trigger DML Transact-SQL contenente statement_text. Se si omette schema_name , schema_name utilizza lo schema dell'utente corrente. Se si specifica NULL e @type='SQL', il valore di @module_or_batch viene impostato sul valore di @stmt. Se si specifica @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 è valido solo nei casi seguenti:

  • @type='SQL' o 'TEMPLATE'. Se si specifica 'TEMPLATE', @params non può essere NULL.
  • statement_text viene sottomesso tramite sp_executesql e non viene specificato un valore per il parametro @params oppure SQL Server sottomette internamente un'istruzione dopo averla parametrizzata. La sottomissione di query con parametri dalle API del database, incluse ODBC, OLE DB e ADO.NET, vengono considerate da SQL Server come chiamate a sp_executesql o alle routine dei cursori API del server. È pertanto possibile eseguire le corrispondenze in base alle guide di piano di tipo SQL o TEMPLATE. Per ulteriori informazioni sulla parametrizzazione e sulle guide di piano, vedere Come SQL Server fa corrispondere le guide di piano alle query.

  È necessario specificare @parameter_name data_type nello stesso formato con cui è stato sottomesso a SQL Server tramite l'utilizzo di sp_executesql oppure sottomettendolo internamente dopo la parametrizzazione. Per ulteriori informazioni, vedere la sezione Osservazioni. Se il batch non include parametri, è necessario specificare NULL. La dimensione di @params è limitata solo dalla memoria disponibile del server.

• [@hints = ] { **N'**OPTION **(**query_hint [ ,...n ] )' | NULL }
  Specifica una clausola OPTION da associare a una query corrispondente a @stmt. @hints deve essere sintatticamente identica a una clausola OPTION in un'istruzione SELECT e può contenere qualsiasi sequenza valida di hint per la query. NULL indica nessuna clausola OPTION. Per ulteriori informazioni, vedere Clausola OPTION (Transact-SQL).

Osservazioni

Gli argomenti per sp_create_plan_guide devono essere inseriti nell'ordine illustrato. 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 di parametro specificato corrisponda al nome del parametro nella posizione in cui il nome viene utilizzato.

È possibile creare solo una guida di piano per una combinazione specifica di @module_or_batch e @stmt.

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 cerca di eliminare o modificare una funzione, una stored procedure o un trigger DML a cui fa riferimento una guida di piano attivata o disattivata, 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.

[!NOTA] Sebbene sia possibile utilizzare le guide di piano solo in SQL Server Standard, Developer, Evaluation ed Enterprise Edition, la visualizzazione è possibile in tutte le versioni. È inoltre possibile collegare un database che contiene guide di piano a qualsiasi edizione. Quando si ripristina o collega un database a una versione aggiornata di SQL Server 2008, 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

Affinché le guide di piano che specificano @type='SQL' o @type='TEMPLATE' corrispondano esattamente a una query, i valori di batch_text e @parameter_name data_type [,...n ] devono essere specificati esattamente nello stesso formato delle rispettive controparti sottomesse dall'applicazione. Ciò significa che è necessario specificare il testo del batch esattamente come il compilatore di SQL Server lo riceve. Per acquisire il testo effettivo del batch e del parametro, è possibile utilizzare SQL Server Profiler. Per ulteriori informazioni, vedere Utilizzo di SQL Server Profiler per creare e testare guide di piano.

Quando @type = 'SQL' e @module\_or\_batch sono impostati in NULL, il valore di @module\_or\_batch è impostato nel valore di @stmt. Ciò significa che il valore di statement_text deve essere fornito esattamente nello stesso formato inviato a SQL Server, carattere per carattere. Per semplificare questa corrispondenza, non viene eseguita alcuna conversione interna.

Se SQL Server abbina il valore di statement_text a batch_text e @parameter_name data_type [,...n ] oppure, se @type='OBJECT', al testo della query corrispondente all'interno di object_name, gli elementi della 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ò far corrispondere la stringa statement_textN'SELECT * FROM T WHERE a = 10' al seguente batch_text:

N'SELECT *

FROM T

WHERE a=10'

La stessa stringa non verrebbe tuttavia fatta corrispondere al seguente batch_text:

N'SELECT * FROM T WHERE b = 10'

SQL Server ignora i caratteri di ritorno a capo, avanzamento riga e gli spazi 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 forma abbreviata delle parole chiave non è rilevante nella corrispondenza. Ad esempio, le parole chiave EXECUTE, EXEC e execute vengono considerate equivalenti.

Per ulteriori informazioni sulle procedure di corrispondenza tra guide di piano e query, vedere Ottimizzazione delle query nelle applicazioni distribuite tramite le guide di piano.

Effetto delle guide di piano sulla cache dei piani

La creazione di una guida di piano in un modulo rimuove il piano di query per tale 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 che ha lo stesso valore hash. La creazione di una guida di piano di tipo TEMPLATE rimuove tutti i batch a singola istruzione dalla cache dei piani all'interno del database.

Autorizzazioni

Per creare una guida di piano di tipo OBJECT mediante la specifica di @type='OBJECT'), è necessario disporre dell'autorizzazione ALTER per l'oggetto di riferimento. Per creare una guida di piano di tipo SQL o TEMPLATE, è necessario disporre dell'autorizzazione ALTER per il database corrente.

Esempi

A. 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 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;
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',
    @type = N'OBJECT',
    @module_or_batch = N'Sales.GetSalesOrderByCountry',
    @params = NULL,
    @hints = N'OPTION (OPTIMIZE FOR (@Country = 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 corrispondente a una query in un batch sottomesso da un'applicazione che utilizza la stored procedure di sistema sp_executesql.

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 corrispondente a qualsiasi query che parametrizza un formato specifico e impone a 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 AdventureWorks.Sales.SalesOrderHeader AS h
INNER JOIN AdventureWorks.Sales.SalesOrderDetail AS d 
    ON h.SalesOrderID = d.SalesOrderID
WHERE h.SalesOrderID = 45639;

SELECT * FROM AdventureWorks.Sales.SalesOrderHeader AS h
INNER JOIN AdventureWorks.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 AdventureWorks.Sales.SalesOrderHeader AS h
              INNER JOIN AdventureWorks.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 metodo affidabile per ottenere questo valore da utilizzare in sp_create_plan_guide è utilizzare 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 AdventureWorks.Sales.SalesOrderHeader AS h
      INNER JOIN AdventureWorks.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 dei parametri diversi.

Per ulteriori informazioni su come ottenere il formato con parametri di una query da utilizzare in una guida di piano di tipo TEMPLATE, vedere Progettazione di guide di piano per le query parametrizzate.

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

È possibile eseguire la corrispondenza tra le guide di piano e le query sottomesse da routine dei cursori API del server. Queste routine includono sp_cursorprepare, sp_cursorprepexec e sp_cursoropen. Le applicazioni che utilizzano API ADO, OLE DB e ODBC interagiscono frequentemente con SQL Server mediante cursori API del server. Per ulteriori informazioni, vedere Cursori API del server. È possibile visualizzare la chiamata delle routine dei cursori API del server nei file di traccia di SQL Server Profiler mediante la visualizzazione dell'evento di traccia RPC:Starting del 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 h INNER JOIN AdventureWorks.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.

Per ulteriori informazioni su come utilizzare l'hint per la query USE PLAN in una guida di piano per una query sottomessa con un cursore, vedere Utilizzo dell'hint USE PLAN nelle query con cursori.

Vedere anche

Riferimento

sp_control_plan_guide (Transact-SQL)
sys.plan_guides
Stored procedure del Motore di database (Transact-SQL)
Stored procedure di sistema (Transact-SQL)

Guida in linea e informazioni

Assistenza su SQL Server 2005

Cronologia modifiche