Informazioni sulle guide di piano

In SQL Server 2005 è stata introdotta la stored procedure di sistema sp_create_plan_guide per la creazione di guide di piano per l'ottimizzazione delle prestazioni delle query. È possibile utilizzare questa procedura quando non si può 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. Nell'istruzione sp_create_plan_guide si specificano la query da ottimizzare e la clausola OPTION che contiene gli hint che si desidera utilizzare per l'ottimizzazione della query. Quando la query viene eseguita, SQL Server fa corrispondere la query alla guida di piano e aggiunge la clausola OPTION alla query in fase di esecuzione.

[!NOTA] È possibile creare e utilizzare le guide di piano solo in SQL Server 2005 Standard, Developer, Evaluation ed Enterprise Edition. L'eliminazione delle guide di piano è consentita in tutte le edizioni.

Le query che possono trarre vantaggio dall'utilizzo di guide di piano sono in genere basate su parametri ed è possibile che le prestazioni siano insufficienti a causa dell'utilizzo di piani di query memorizzati nella cache nei quali i valori dei parametri non rappresentano il caso peggiore o lo scenario più rappresentativo. Per la risoluzione di questo problema è possibile utilizzare gli hint per query OPTIMIZE FOR e RECOMPILE. OPTIMIZE FOR indica a SQL Server di utilizzare un valore particolare per un parametro quando la query viene ottimizzata. RECOMPILE indica al server di eliminare un piano di query dopo l'esecuzione, forzando Query Optimizer alla compilazione di un nuovo piano di query alla successiva esecuzione della stessa query.

Un altro hint per query comune da utilizzare con le guide di piano è USE PLAN. Questo hint risulta utile quando per una specifica query esiste un piano di esecuzione che offre prestazioni migliori rispetto a quello selezionato da Query Optimizer ed è opportuna la sostituzione. USE PLAN forza SQL Server all'utilizzo di un particolare piano di query, specificato esplicitamente nella sintassi dell'hint, durante l'esecuzione della query. Una guida di piano che applica l'hint per query USE PLAN è particolarmente utile quando per ottenere un buon piano di esecuzione per una query risulta più conveniente riscrivere la query stessa per forzare un ordine di join, oppure l'utilizzo di hint di join o hint per l'indice. Per ulteriori informazioni, vedere Scenario di utilizzo forzato del piano: Creazione di una guida di piano per forzare l'utilizzo di un piano ottenuto da una query riformulata.

Per ulteriori informazioni su RECOMPILE, OPTIMIZE FOR, USE PLAN e altri hint per query, vedere query_hint (Transact-SQL).

Attenzione:
L'utilizzo non corretto degli hint per query da parte delle guide di piano può provocare problemi di compilazione, esecuzione o prestazioni. È consigliabile che le guide di piano vengano utilizzate solo da sviluppatori e amministratori di database esperti.

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

• Le guide di piano di tipo OBJECT corrispondono alle query eseguite nel contesto di stored procedure Transact-SQL, funzioni scalari, funzioni valutate a livello di tabella con istruzioni multiple e trigger DML.
• Le guide di piano di tipo SQL corrispondono 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.
• Le guide di piano di tipo TEMPLATE corrispondono 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.

Guide di piano di tipo OBJECT

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

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

Questa stored procedure è stata compilata e ottimizzata per @Country = N'AU' (Australia). Gli ordini di vendita provenienti dall'Australia tuttavia, sono in numero relativamente basso. Quando la query viene eseguita utilizzando i valori dei parametri di paesi con un numero maggiore di ordini di vendita, le prestazioni peggiorano. Poiché il paese da cui proviene la maggior parte degli ordini di vendita sono gli Stati Uniti, un piano di query generato per @Country=N'US' offrirebbe probabilmente prestazioni migliori per tutti i possibili valori del parametro @Country.

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

sp_create_plan_guide 
@name = N'Guide1',
@stmt = N'SELECT * FROM Sales.SalesOrderHeader h,
        Sales.Customer c,
        Sales.SalesTerritory t
        WHERE h.CustomerID = c.CustomerID 
            AND c.TerritoryID = t.TerritoryID
            AND CountryRegionCode = @Country',
@type = N'OBJECT',
@module_or_batch = N'Sales.GetSalesOrderByCountry',
@params = NULL,
@hints = N'OPTION (OPTIMIZE FOR (@Country = 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''), anch'essa specificata.

Guide di piano di tipo SQL

Le guide di piano di tipo SQL si applicano a istruzioni e batch che vengono inoltrati di frequente 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 h ORDER BY OrderDate DESC

Per evitare la generazione di un piano di esecuzione parallelo su questa query, creare la guida di piano seguente:

sp_create_plan_guide 
@name = N'Guide1', 
@stmt = N'SELECT TOP 1 * FROM Sales.SalesOrderHeader h 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 guide devono corrispondere esattamente 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.

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.

Vedere anche

Concetti

Ottimizzazione delle query nelle applicazioni distribuite tramite le guide di piano
Progettazione e implementazione di guide di piano

Altre risorse

Prestazioni delle query
sp_create_plan_guide (Transact-SQL)
sp_control_plan_guide (Transact-SQL)
sys.plan_guides

Guida in linea e informazioni

Assistenza su SQL Server 2005