Condividi tramite

Guide di piano

  • Articolo

Le guide di piano consentono di ottimizzare le prestazioni delle query quando non è possibile o non si vuole modificare direttamente il testo della query effettiva in SQL Server 2014. Le guide di piano influiscono sull'ottimizzazione delle query mediante l'aggiunta di hint per la query o di un piano di query fisso. Le guide di piano risultano utili quando le prestazioni di un piccolo subset di query eseguite su un database di terze parti sono inferiori a quelle previste. In una guida di piano, viene specificata l'istruzione Transact-SQL da ottimizzare e la clausola OPTION che contiene gli hint per la query da utilizzare o un piano di query specifico da utilizzare per ottimizzare la query. Quando la query viene eseguita, SQL Server corrisponde all'istruzione Transact-SQL alla guida di piano e associa la clausola OPTION alla query in fase di esecuzione o usa il piano di query specificato.

Il numero totale di guide di piano che è possibile creare è limitato solo dalle risorse di sistema disponibili. È comunque consigliabile utilizzare le guide di piano per le sole query critiche di cui si desidera migliorare o stabilizzare le prestazioni. Le guide di piano non vanno utilizzate per modificare la maggior parte del carico di query di un'applicazione distribuita.

Nota

Le guide di piano non possono essere usate in ogni edizione di MicrosoftSQL Server. Per un elenco delle funzionalità supportate dalle edizioni di SQL Server, vedere Funzionalità supportate dalle edizioni di SQL Server 2014. Le guide di piano sono visibili in qualsiasi edizione. È inoltre possibile collegare un database che contiene guide di piano a qualsiasi edizione. Le guide di piano rimangono intatte quando si ripristina o si collega un database a una versione aggiornata di SQL Server.

Tipi di guide di piano

È possibile creare i seguenti tipi di guide di piano:

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

Si supponga che la stored procedure seguente, che accetta il @Countryparametro _region , si trova in un'applicazione di database distribuita nel database AdventureWorks2012 :

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 originati dall'Australia, le prestazioni diminuiscono quando la query viene eseguita usando i valori dei parametri di paesi/aree geografiche con più ordini di vendita. Poiché la maggior parte degli ordini di vendita ha origine nel Stati Uniti, è probabile che un piano di query generato per @Country_region = N'US' esegua prestazioni migliori per tutti i valori possibili del @Countryparametro _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 AdventureWorks2012 .

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

Guida di piano di tipo SQL
Una guida di piano SQL corrisponde alle query eseguite nel contesto di istruzioni Transact-SQL autonome e batch 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 SQL si applicano a istruzioni Transact-SQL autonome e batch. Spesso tali istruzioni vengono inoltrate da un'applicazione mediante 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 seguente guida di piano e impostare l'hint per la 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)';

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 altre informazioni, vedere sp_create_plan_guide (Transact-SQL) e Usare 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.

TEMPLATE - guida di piano
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.

È possibile creare una guida di piano di tipo TEMPLATE nelle seguenti situazioni:

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

Requisiti di corrispondenza per la guida di piano

Le guide di piano sono definite a livello di ambito del database in cui vengono create. Pertanto, è possibile far corrispondere alla query solo le guide di piano presenti nel database corrente al momento dell'esecuzione della query. Ad esempio, se AdventureWorks2012 è il database corrente e viene eseguita la query seguente:

SELECT FirstName, LastName FROM Person.Person;

Solo le guide di piano nel database AdventureWorks2012 sono idonee per la corrispondenza a questa query. Tuttavia, se AdventureWorks2012 è il database corrente e vengono eseguite le istruzioni seguenti:

USE DB1;

SELECT FirstName, LastName FROM Person.Person;

È possibile far corrispondere alla query solo le guide di piano in DB1 , poiché la query è in esecuzione nel contesto di DB1.

Per le guide di piano basate su SQL o TEMPLATE, SQL Server corrisponde ai valori per gli @module_or_batch argomenti e @params a una query confrontando i due valori carattere per carattere. Ciò significa che è necessario specificare il testo esattamente come SQL Server lo riceve nel batch effettivo.

Quando @type = 'SQL' e @module_or_batch è impostato su NULL, il valore di @module_or_batch viene impostato sul valore di @stmt. Ciò significa che il valore per statement_text deve essere specificato nel formato identico, carattere per carattere, come viene inviato a SQL Server. Per semplificare questa corrispondenza, non viene eseguita alcuna conversione interna.

Quando è possibile applicare sia una guida di piano normale (SQL o OBJECT) sia una guida di piano TEMPLATE a un'istruzione, verrà utilizzata solo la guida di piano normale.

Nota

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

Effetto delle guide 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 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 database.

Attività Argomento
Viene descritto come creare una guida di piano. Creare una nuova guida di piano
Viene descritto come creare una guida di piano per le query con parametri. Creare una guida di piano per le query con parametri
Viene descritto come controllare il comportamento di parametrizzazione delle query utilizzando guide di piano. Specificare il comportamento di parametrizzazione delle query tramite guide di piano
Viene descritto come includere un piano di query fisso in una guida di piano. Applicare un piano di query fisso a una guida di piano
Viene descritto come specificare hint per la query in una guida di piano. Associare gli hint per le query a una guida di piano
Viene descritto come visualizzare le proprietà di una guida di piano. Visualizzare le proprietà delle guide di piano
Viene descritto come utilizzare SQL Server Profiler per creare e testare guide di piano. Usare SQL Server Profiler per creare e testare guide di piano
Viene descritto come convalidare una guida di piano. Convalidare le guide di piano dopo l'aggiornamento

Vedere anche

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)