sp_create_plan_guide (Transact-SQL)

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 inviate 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.

Se @type = 'SQL' e @module\_or\_batch vengono impostate su NULL, il valore di @module\_or\_batch viene impostato al valore di @stmt. Di conseguenza, il valore per statement_text deve essere specificato nello stesso formato, carattere per carattere, così come viene inviato a SQL Server. 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.

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 dato database.

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

Nell'esempio seguente viene creata una guida di piano corrispondente a una query in un batch inviato 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)';

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.

È possibile eseguire la corrispondenza tra le guide di piano e le query sottomesse da routine dei cursori API del server. Le 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.

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 utilizzando la stored procedure sp_create_plan_guide_from_handle.

USE AdventureWorks;
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