Articolo
01/24/2011

Migrazione dei piani di query

Nella maggior parte dei casi, l'aggiornamento di un database a SQL Server 2008 comporta un miglioramento nelle prestazioni di esecuzione delle query. Tuttavia, se sono presenti query critiche attentamente ottimizzate per le prestazioni, potrebbe essere necessario mantenere i piani per tali query prima di eseguire l'aggiornamento creando una guida di piano per ciascuna query. Se, dopo aver eseguito l'aggiornamento, Query Optimizer sceglie un piano meno efficiente per una o più query, è possibile attivare le guide di piano e forzare il Query Optimizer a utilizzare i piani precedenti all'aggiornamento.

Per creare guide di piano prima di eseguire l'aggiornamento, eseguire la procedura seguente:

Registrare il piano corrente per ciascuna query critica utilizzando la stored procedure sp_create_plan_guide e specificando il piano di query nell'hint della query USE PLAN.
Verificare che la guida di piano sia applicata alla query.
Aggiornamento del database a SQL Server 2008.

I piani sono persistenti nel database aggiornato nelle guida di piano e servono come fallback in caso di regressioni del piano dopo l'aggiornamento.

Si consiglia di non attivare le guide di piano dopo l'aggiornamento, in quanto si potrebbero perdere opportunità per migliori piani nella nuova versione o ricompilazioni vantaggiose grazie a statistiche aggiornate.
Se vengono scelti piani meno efficienti dopo l'aggiornamento, attivare tutte o un subset delle guide di piano per sostituire i nuovi piani.

Esempio

Nel seguente esempio viene illustrato come registrare un piano prima dell'aggiornamento per una query creando una guida di piano.

Passaggio 1: Raccolta del piano

Il piano di query registrato nella guida di piano deve essere in formato XML. È possibile creare piani di query in formato XML nei seguenti modi:

SET SHOWPLAN_XML
SET STATISTICS XML
Esecuzione di query sulla colonna query_plan della funzione a gestione dinamica sys.dm_exec_query_plan.
Le classi di evento SQL Server ProfilerShowplan XML, Showplan XML Statistics Profile e Showplan XML For Query Compile.

Per ulteriori informazioni sulla generazione e sull'analisi dei piani di query, vedere Analisi di una query.

Nell'esempio seguente viene raccolto il piano di query per l'istruzione SELECT City, StateProvinceID, PostalCode FROM Person.Address ORDER BY PostalCode DESC; eseguendo una query sulle viste a gestione dinamica.

USE AdventureWorks;
GO
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;%';
GO

Passaggio 2: Creazione della guida di piano per l'utilizzo forzato del piano

Utilizzando nella guida di piano il piano di query in formato XML ottenuto con uno dei metodi descritti in precedenza, copiare e incollare il piano di query come valore letterale stringa nell'hint per la query USE PLAN specificato nella clausola OPTION di sp_create_plan_guide.

All'interno del piano XML, utilizzare i caratteri di escape alle virgolette (') visualizzate nel piano, tramite una seconda virgoletta, prima di creare la guida di piano. Ad esempio, per un piano che contiene WHERE A.varchar = 'This is a string' è necessario utilizzare i caratteri di escape modificando il codice in WHERE A.varchar = ''This is a string''.

Nell'esempio seguente viene creata una guida di piano per il piano di query raccolto nel passaggio 1 e viene inserito Showplan XML per la query nel parametro @hints. In breve, solo l'output Showplan parziale viene incluso nell'esempio.

EXECUTE sp_create_plan_guide 
@name = N'Guide1',
@stmt = N'SELECT City, StateProvinceID, PostalCode FROM Person.Address ORDER BY PostalCode DESC;',
@type = N'SQL',
@module_or_batch = NULL,
@params = NULL,
@hints = N'OPTION(USE PLAN N''<ShowPlanXML xmlns=''''https://schemas.microsoft.com/sqlserver/2004/07/showplan'''' 
    Version=''''0.5'''' Build=''''9.00.1116''''>
    <BatchSequence><Batch><Statements><StmtSimple>
    …
    </StmtSimple></Statements></Batch>
    </BatchSequence></ShowPlanXML>'')';
GO

Passaggio 3: Verifica dell'applicazione della guida di piano alla query

Eseguire nuovamente la query ed esaminare il piano di query prodotto. Verificare che il piano corrisponda a quello di cui specificato nella guida.

Vedere anche

Riferimento

sp_create_plan_guide (Transact-SQL)

Concetti

Utilizzo dell'hint per la query USE PLAN

Scenari ed esempi di utilizzo forzato dei piani

Condividi tramite