Condividi tramite

Migrazione dei piani di query

Nella maggior parte dei casi, l'aggiornamento di un database alla versione più recente di SQL Server comporterà un miglioramento delle prestazioni delle query. Tuttavia, se hai query mission-critical che sono state ottimizzate con attenzione per le prestazioni, potresti voler conservare i piani di esecuzione per queste query prima di eseguire l'aggiornamento creando una guida di piano per ciascuna query. Se, dopo l'aggiornamento, Query Optimizer sceglie un piano meno efficiente per una o più query, è possibile abilitare le guide di piano e forzare Query Optimizer a usare i piani di pre-aggiornamento.

Per creare guide di piano prima dell'aggiornamento, seguire questa procedura:

  1. Registrare il piano corrente per ogni query mission critical usando la stored procedure sp_create_plan_guide e specificando il piano di query nell'hint per la query USE PLAN.

  2. Verificare che la guida di piano venga applicata alla query.

  3. Aggiornare il database alla versione più recente di SQL Server.

    I piani vengono mantenuti nel database aggiornato nelle guide dei piani e agiscono come misura di sicurezza in caso di regressioni del piano dopo l'aggiornamento.

    Consigliamo di non abilitare le guide al piano dopo l'aggiornamento perché si potrebbero perdere opportunità per piani migliori nella nuova versione o ricompilazioni vantaggiose a causa di statistiche aggiornate.

  4. Se dopo l'aggiornamento vengono scelti piani meno efficienti, attivare tutte o una parte delle guide di piano per sostituire i nuovi piani.

Esempio

Esempio seguente mostra come registrare un piano di pre-aggiornamento per una query creando una guida del piano.

Passaggio 1: Raccogliere le informazioni del piano

Il piano di query registrato nella guida del piano deve essere in formato XML. I piani di query in formato XML possono essere prodotti nei modi seguenti:

Nell'esempio seguente viene raccolto il piano di query per l'istruzione SELECT City, StateProvinceID, PostalCode FROM Person.Address ORDER BY PostalCode DESC; tramite interrogazione delle viste di 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: Creare la Guida del Piano per forzare il Piano

Usando il piano di query in formato XML (ottenuto da uno dei metodi descritti in precedenza) nella guida di piano, copiare e incollare il piano di query, come valore letterale di stringa, all'interno dell'hint di query USE PLAN specificato nella clausola OPTION del comando sp_create_plan_guide.

All'interno del piano XML stesso, virgolette di escape (') visualizzate nel piano con una seconda virgoletta prima di creare la guida di piano. Ad esempio, un piano che contiene WHERE A.varchar = 'This is a string' deve essere sottoposto a escape modificando il codice in WHERE A.varchar = ''This is a string''.

Nell'esempio seguente viene creata una guida al piano per il piano di query raccolto nel passaggio 1 e lo showplan XML per la query viene inserito nel parametro @hints. Per brevità, nell'esempio è incluso solo l'output parziale di Showplan.

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: Verificare che la Guida del Piano venga applicata alla query

Eseguire di nuovo la query ed esaminare il piano di query generato. Si noterà che il piano corrisponde a quello specificato nella guida di piano.

Vedere anche

sp_create_plan_guide (Transact-SQL)
Suggerimenti per la query (Transact-SQL)
Guide del piano strategico

  • Last updated on