Compartilhar via

Migrar planos de consulta

Na maioria dos casos, a atualização de um banco de dados para a versão mais recente do SQL Server resultará em um melhor desempenho de consulta. No entanto, se você tiver consultas críticas para a missão que foram cuidadosamente ajustadas para desempenho, convém preservar seus planos de consulta antes de atualizar, criando um guia de plano para cada consulta. Se, após a atualização, o otimizador de consulta escolher um plano menos eficiente para uma ou mais das consultas, você poderá habilitar os guias de plano e forçar o otimizador de consulta a usar os planos de pré-atualização.

Para criar guias de plano antes da atualização, siga estas etapas:

  1. Registre o plano atual para cada consulta crítica de missão usando o procedimento armazenado sp_create_plan_guide e especificando o plano de consulta na dica de consulta USE PLAN.

  2. Verifique se o guia do plano é aplicado à consulta.

  3. Atualize o banco de dados para a versão mais recente do SQL Server.

    Os planos são mantidos no banco de dados atualizado nos guias de planos e servem como uma alternativa no caso de regressões de planos após a atualização.

    Recomendamos que você não habilite os guias de plano após a atualização, pois pode perder oportunidades para melhores planos na nova versão ou recompilações benéficas devido a estatísticas atualizadas.

  4. Se planos menos eficientes forem escolhidos após a atualização, ative todos ou um subconjunto dos guias de plano para substituir os novos planos.

Exemplo

O exemplo a seguir mostra como registrar um plano de pré-atualização para uma consulta criando um guia de plano.

Etapa 1: Coletar o plano

O plano de consulta registrado no guia de plano deve estar no formato XML. Os planos de consulta formatados por XML podem ser produzidos pelas seguintes maneiras:

O exemplo a seguir coleta o plano de consulta da instrução SELECT City, StateProvinceID, PostalCode FROM Person.Address ORDER BY PostalCode DESC; ao consultar as visões de gerenciamento dinâmico.

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

Etapa 2: Criar o guia de plano para forçar o plano

Usando o plano de consulta formatado em XML (obtido por qualquer um dos métodos descritos anteriormente) no guia do plano, copie e cole o plano de consulta como um literal de cadeia de caracteres dentro da dica de consulta USE PLAN especificada na cláusula OPTION de sp_create_plan_guide.

Dentro do próprio plano XML, aspas de escape (') que aparecem no plano com uma segunda aspa antes de criar o guia do plano. Por exemplo, um plano que contém WHERE A.varchar = 'This is a string' deve ser escapado modificando o código para WHERE A.varchar = ''This is a string''.

O exemplo a seguir cria um guia de plano para o plano de consulta coletado na etapa 1 e insere o Plano de Exibição XML para a consulta no @hints parâmetro. Para fins de brevidade, somente a saída parcial do Showplan é incluída no exemplo.

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

Etapa 3: verificar se o guia de plano é aplicado à consulta

Execute a consulta novamente e examine o plano de consulta produzido. Você deve ver que o plano corresponde ao especificado no guia do plano.

Consulte Também

sp_create_plan_guide (Transact-SQL)
Dicas de consulta (Transact-SQL)
Guias de Planejamento

  • Last updated on