Migrar planos de consulta

Artigo
02/18/2014

Na maioria dos casos, a atualização de um banco de dados para a versão mais recente do SQL Server resultará em melhoria do desempenho de consulta. No entanto, se você tiver consultas essenciais que foram cuidadosamente ajustadas para desempenho, pode desejar preservar os planos dessas consultas antes da atualização através da criação de um guia de plano para cada consulta. Se depois da atualização o otimizador de consultas escolher um plano menos eficiente para uma ou mais consultas, você pode habilitar os guias de plano e forçar o otimizador de consulta a usar planos anteriores à atualização.

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

Grave o plano atual para cada consulta crítica usando o procedimento armazenado do sp_create_plan_guide e especificando o plano de consulta na dica de consulta USE PLAN.
Verifique se o guia de plano foi aplicado à consulta.
Atualize o banco de dados para a versão mais recente do SQL Server.

Os planos permanecem no banco de dados atualizado nos guias de plano e servem como sistema de apoio no caso de regressões de plano após a atualização.

Recomendamos que os guias de plano não sejam habilitados depois da atualização por que você pode perder oportunidades de planos melhores na versão nova ou recompilações vantajosas devido a estatísticas atualizadas.
Se planos menos eficientes forem escolhidos após a atualização, habilite todos ou um subconjunto dos guias de planos para substituir os planos novos.

Exemplo

O exemplo a seguir mostra como registrar um plano anterior à atualização para uma consulta através da criação de um guia de plano.

Etapa 1: Colete o plano

O plano de consulta registrado no guia de plano deve estar em formato XML. Planos de consulta em formato XML podem ser produzidos das seguintes formas:

SET SHOWPLAN_XML
SET STATISTICS XML
Consultando a coluna query_plan da função de gerenciamento dinâmico sys.dm_exec_query_plan.
As classes de eventos SQL Server Profiler Showplan XML, Showplan XML Statistics Profile e Showplan XML For Query Compile.

O exemplo a seguir coleta o plano de consulta para a instrução SELECT City, StateProvinceID, PostalCode FROM Person.Address ORDER BY PostalCode DESC; examinando exibiçõ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: Crie a guia de plano para forçar o plano

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

No próprio plano XML, faça a saída das aspas (') que aparecem no plano com um segundo sinal de aspas antes de criar o guia de plano. Por exemplo, a saída de um plano que contenha WHERE A.varchar = 'This is a string' deve ser feita modificando-se o código para WHERE A.varchar = ''This is a string''.

O exemplo a seguir cria um guia de plano para o plano da consulta coletado na etapa 1 e insere o Plano de Execução XML para a consulta no parâmetro @hints. Para ser breve, apenas uma saída parcial do Plano de Execução é 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: Verifique se o guia de plano foi aplicado à consulta.

Execute a consulta novamente e examine o plano de consulta produzido. Você deve verificar se o plano corresponde ao que você especificou no guia de plano.

Consulte também

Referência

sp_create_plan_guide (Transact-SQL)

dicas de consulta (Transact-SQL)

Conceitos

Guias de plano

Share via