Articolo
03/31/2012

Progettazione e implementazione di guide di piano

Le guide di piano consentono di ottimizzare le prestazioni delle query quando non è possibile o non si desidera modificare direttamente il testo della query. Le guide di piano influiscono sull'ottimizzazione delle query mediante l'aggiunta di hint o di un piano di query fisso. È 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 Informazioni sulle guide di piano.

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.

È 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 causa errori in una query, il piano viene compilato senza utilizzare la guida di piano. Dopo aver aggiornato un database, si consiglia di eseguire le seguenti attività per convalidare le guide di piano esistenti utilizzando la funzione sys.fn_validate_plan_guide. In alternativa, è possibile monitorare le guide di piano non valide utilizzando l'evento Plan Guide Unsuccessful in SQL Server Profiler.

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, le guide di piano non vengono modificate.

Associazione degli hint per le query a una guida di piano

In una guida di piano è possibile utilizzare qualsiasi combinazione di hint per la query validi. Quando una guida di piano corrisponde a una query, la clausola OPTION specificata nella clausola dell'hint di una guida di piano viene aggiunta alla query prima che questa venga compilata e ottimizzata. Se una query che corrisponde a una guida di piano contiene già una clausola OPTION, gli hint per la query specificati nella guida di piano sostituiscono quelli della query. Perché una guida di piano possa corrispondere a una query che già contiene una clausola OPTION è tuttavia necessario includere la clausola OPTION della query quando si specifica il testo della query da far corrispondere nell'istruzione sp_create_plan_guide. Se si desidera che gli hint specificati nella guida di piano vengano aggiunti agli hint già esistenti nella query, invece di sostituirli è necessario specificare sia gli hint originali che gli hint aggiuntivi nella clausola OPTION della guida di piano.

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 esperti di database.

Hint per le query comuni utilizzate nelle guide di piano

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. Per un esempio, vedere Informazioni sulle guide di piano.

Inoltre, è possibile specificare gli hint di tabella INDEX e FORCESEEK come hint per query. Se specificati come hint per query, tali hint si comportano come un hint di vista o di una tabella inline. L'hint INDEX impone a Query Optimizer di utilizzare soltanto gli indici specificati per accedere ai dati nella tabella o nella vista. L’hint FORCESEEK impone a Optimizer di utilizzare soltanto un’operazione Index Seek per accedere ai dati nella tabella o nella vista a cui si fa riferimento. Tali hint forniscono funzionalità della guida di piano aggiuntive e consentono di avere maggiore influenza sull'ottimizzazione di query che utilizzano la guida di piano. Per un esempio, vedere Utilizzo degli hint per le query INDEX e FORCESEEK nelle guide di piano.

Associazione di un piano di query a una guida di piano

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. L'applicazione di un piano fisso a una query presuppone che Query Optimizer non è più in grado pertanto di adattare il piano per la query alle modifiche apportate a statistiche e indici. Quando si prendono in considerazione le guide di piano che utilizzano i piani di query fissi, confrontare i vantaggi dell'applicazione di un piano fisso all'impossibilità di adattare automaticamente il piano alle modifiche apportate alla distribuzione dei dati e degli indici disponibili.

È possibile associare un piano di query specifico a una guida di piano specificando lo Showplan XML del piano nel parametro xml_showplan nell'istruzione sp_create_plan_guide oppure specificando l'handle di un piano memorizzato nella cache nell'istruzione sp_create_plan_guide_from_handle. Entrambi questi metodi prevedono l'applicazione del piano di query fisso alla query di destinazione.

Guida di piano corrispondente ai requisiti

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

SELECT FirstName, LastName FROM Person.Person;

È possibile far corrispondere alla query solo le guide di piano nel database AdventureWorks2008R2.

Se tuttavia il database corrente è AdventureWorks2008R2 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, perché la query è in esecuzione nel contesto di DB1.

Per guide di piano basate su SQL o TEMPLATE, SQL Server fa corrispondere i valori per gli argomenti @module_or_batch e @params a una query confrontando i due valori carattere per carattere. Per questo motivo è necessario immettere il testo esattamente come SQL Server lo riceve nel batch.

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.

È in genere consigliabile testare le guide di piano utilizzando SQL Server Profiler per verificare che la query venga fatta corrispondere alla guida di piano. L'esecuzione di test su guide di piano basate su SQL o su modelli mediante l'esecuzione di batch da SQL Server Management Studio può produrre risultati imprevisti. Per ulteriori informazioni, vedere Utilizzo di SQL Server Profiler per creare e testare guide di piano.

Nota

Il batch che contiene 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 dato database.