Informazioni sulle guide di piano

In questo argomento vengono descritte le guide di piano e il loro utilizzo per l'ottimizzazione delle prestazioni delle query quando non è possibile o non si desidera modificare direttamente il testo della query. Le guide di piano possono risultare utili quando un piccolo subset delle query in un'applicazione di database distribuita da un altro fornitore non corrispondono alle aspettative. Le guide di piano influiscono sull'ottimizzazione delle query mediante l'aggiunta di hint o di un piano di query fisso. Nel piano di guida viene specificata l'istruzione Transact-SQL da ottimizzare e la clausola OPTION che contiene gli hint che si desidera utilizzare per l'ottimizzazione della query o un piano di query specifico che si desidera utilizzare per ottimizzare la query. Quando viene eseguita la query, SQL Server fa corrispondere l'istruzione Transact-SQL alla guida di piano e in fase di esecuzione associa la clausola OPTION alla query oppure utilizza il piano di query specificato.

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.

Corrispondenza tra guide di piano e query

È possibile creare guide di piano che corrispondono a query eseguite nei contesti seguenti:

• Una guida di piano di tipo OBJECT corrisponde alle query eseguite nel contesto di stored procedure Transact-SQL, funzioni scalari definite dall'utente, funzioni con valori di tabella definite dall'utente con istruzioni multiple e trigger DML.

• Una guida di piano di tipo SQL corrisponde alle query eseguite nel contesto di batch e istruzioni Transact-SQL autonome che non fanno parte di un oggetto di database. Le guide di piano basate su SQL possono inoltre essere utilizzate per query con parametrizzazioni specifiche.

• Una guida di piano di tipo TEMPLATE corrisponde alle query autonome con parametrizzazioni specifiche. Tali guide di piano vengono utilizzate per sostituire l'opzione SET di database PARAMETERIZATION di un database per una classe di query. Per ulteriori informazioni, vedere Parametrizzazione semplice e Parametrizzazione forzata.

Per ulteriori informazioni, vedere Come SQL Server fa corrispondere le guide di piano alle query.

Guide di piano di tipo OBJECT

Si supponga che la stored procedure seguente, che accetta il parametro @Country_region, esista in un'applicazione di database distribuita sul database AdventureWorks2008R2:

CREATE PROCEDURE Sales.GetSalesOrderByCountry (@Country_region nvarchar(60))
AS
BEGIN
    SELECT *
    FROM Sales.SalesOrderHeader AS h, Sales.Customer AS c, 
        Sales.SalesTerritory AS t
    WHERE h.CustomerID = c.CustomerID
        AND c.TerritoryID = t.TerritoryID
        AND CountryRegionCode = @Country_region
END;

Questa stored procedure è stata compilata e ottimizzata per @Country_region = N'AU' (Australia). Tuttavia, poiché sono presenti relativamente pochi ordini di vendita con origine in Australia, le prestazioni risultano lente quando viene eseguita la query utilizzando i valori del parametro dei paesi con più ordini di vendita. Poiché il paese da cui proviene la maggior parte degli ordini di vendita sono gli Stati Uniti, un piano di query generato per @Country_region = N'US' offrirebbe probabilmente prestazioni migliori per tutti i possibili valori del parametro @Country_region.

Per risolvere il problema è possibile modificare la stored procedure aggiungendo alla query l'hint OPTIMIZE FOR. Poiché la stored procedure si trova in un'applicazione distribuita, non è possibile modificare direttamente il codice dell'applicazione. È invece possibile creare la guida di piano seguente nel database AdventureWorks2008R2.

sp_create_plan_guide 
@name = N'Guide1',
@stmt = N'SELECT *FROM Sales.SalesOrderHeader AS h,
        Sales.Customer AS c,
        Sales.SalesTerritory AS t
        WHERE h.CustomerID = c.CustomerID 
            AND c.TerritoryID = t.TerritoryID
            AND CountryRegionCode = @Country_region',
@type = N'OBJECT',
@module_or_batch = N'Sales.GetSalesOrderByCountry',
@params = NULL,
@hints = N'OPTION (OPTIMIZE FOR (@Country_region = N''US''))'

Al momento dell'esecuzione della query specificata nell'istruzione sp_create_plan_guide, la query viene modificata prima dell'ottimizzazione per includere la clausola OPTIMIZE FOR (@Country = N''US'').

Guide di piano di tipo SQL

Le guide di piano di tipo SQL vengono applicate a istruzioni Transact-SQL autonome e batch. Spesso tali istruzioni vengono inoltrate da un'applicazione utilizzando la stored procedure di sistema sp_executesql. Ad esempio, si consideri il batch autonomo seguente:

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

Per impedire la generazione di un piano di esecuzione parallelo su questa query, creare la guida di piano seguente e impostare l'hint della query MAXDOP su 1 nel parametro @hints.

sp_create_plan_guide 
@name = N'Guide2', 
@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)';

Nota

Il batch che contiene l'istruzione sulla quale si desidera creare una guida di piano non può contenere un'istruzione USE database.

Importante
I valori specificati per gli argomenti @module_or_batch e @params dell'istruzione sp_create_plan guide devono corrispondere al testo specificato nella query effettiva. Per ulteriori informazioni, vedere sp_create_plan_guide (Transact-SQL) e Utilizzo di SQL Server Profiler per creare e testare guide di piano.

È possibile creare guide di piano SQL anche per le query con parametrizzazione forzata quando l'opzione di database PARAMETERIZATION è impostata su FORCED, oppure quando si crea una guida di piano di tipo TEMPLATE per specificare la parametrizzazione di una classe di query. Per ulteriori informazioni, vedere Progettazione di guide di piano per le query parametrizzate.

Guide di piano di tipo TEMPLATE

Le guide di piano di tipo TEMPLATE vengono utilizzate per sovrascrivere il comportamento di parametrizzazione per query specifiche. È possibile creare una guida di piano di tipo TEMPLATE nelle situazioni seguenti:

• L'opzione di database PARAMETERIZATION è impostata su FORCED, ma si desidera compilare alcune query in base alle regole della parametrizzazione semplice.

• L'opzione di database PARAMETERIZATION è impostata su SIMPLE (impostazione predefinita), ma si desidera che una classe di query venga sottoposta a parametrizzazione forzata.

Per ulteriori informazioni, vedere Definizione delle funzionalità di parametrizzazione delle query tramite guide di piano.

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 AdventureWorks2008R2.Sales.SalesOrderHeader AS h
INNER JOIN AdventureWorks2008R2.Sales.SalesOrderDetail AS d 
    ON h.SalesOrderID = d.SalesOrderID
WHERE h.SalesOrderID = 45639;

SELECT * FROM AdventureWorks2008R2.Sales.SalesOrderHeader AS h
INNER JOIN AdventureWorks2008R2.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 AdventureWorks2008R2.Sales.SalesOrderHeader AS h
              INNER JOIN AdventureWorks2008R2.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 AdventureWorks2008R2.Sales.SalesOrderHeader AS h
      INNER JOIN AdventureWorks2008R2.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.

Le guide di piano di tipo TEMPLATE possono essere utilizzate con le guide di piano di tipo SQL. Ad esempio, è possibile creare una guida di piano di tipo TEMPLATE per assicurarsi che una classe di query venga sottoposta a parametrizzazione e creare una guida di piano di tipo SQL sulla query con parametri.

Applicazione di un piano di query fisso a una guida di piano

È possibile applicare un piano di query fisso a una guida di piano di tipo OBJECT o SQL. Le guide di piano che applicano un piano di query fisso risultano utili quando per una specifica query esiste un piano di esecuzione che offre prestazioni migliori rispetto a quello selezionato da Query Optimizer.

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 AdventureWorks2008R2;
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

Convalida delle guide di piano dopo l'aggiornamento

È consigliabile valutare nuovamente e testare le definizioni delle guide di piano quando si aggiorna l'applicazione a una nuova versione di SQL Server. I requisiti di ottimizzazione delle prestazioni e la funzionalità di individuazione delle corrispondenze delle guide di piano possono cambiare. Anche se una guida di piano non valida non farà in modo che una query non riesca, il piano è compilato senza utilizzare la guida di piano e potrebbe non essere la migliore scelta. Dopo aver aggiornato un database a SQL Server 2008, si consiglia di eseguire le seguenti attività:

• Eseguire la convalida delle guide di piano esistenti utilizzando la funzione sys.fn_validate_plan_guide.

• Utilizzando SQL Server Profiler, eseguire il monitoraggio alla ricerca di piani fuorviati per alcuni periodi di tempo utilizzando l'evento Plan Guide Unsuccessful.

Vedere anche

Riferimento

sp_create_plan_guide (Transact-SQL)

sp_create_plan_guide_from_handle (Transact-SQL)

sp_control_plan_guide (Transact-SQL)

sys.plan_guides (Transact-SQL)

sys.fn_validate_plan_guide (Transact-SQL)

Hint per la query (Transact-SQL)

Concetti

Progettazione e implementazione di guide di piano

Parametrizzazione forzata

Parametrizzazione semplice

Altre risorse

Ottimizzazione delle query nelle applicazioni distribuite tramite le guide di piano