Artigo
09/28/2010

Criar e implementar guias de plano

É possível usar guias de plano para otimizar o desempenho das consultas quando você não puder ou não quiser alterar o texto diretamente da consulta. Os guias de plano influenciam a otimização das consultas, anexando dicas de consulta ou um plano de consulta fixo. Guias de plano podem ser criados para corresponder consultas que sejam executadas nos contextos a seguir:

O guia de plano OBJECT corresponde às consultas executadas no contexto dos procedimentos armazenados Transact-SQL, funções escalares definidas pelo usuário, funções com valor de tabela de várias instruções definidas pelo usuário e gatilhos DML.
O guia de plano SQL corresponde às que são executadas no contexto de instruções autônomas Transact-SQL e lotes que não são parte de um objeto do banco de dados. Os guias de plano com base em SQL também podem ser usados para corresponder consultas que parâmetros de uma forma especificada.
O guia de plano TEMPLATE corresponde consultas autônomas com parâmetros para uma forma especificada. Esses guias de plano são usados para substituir a opção SET do banco de dados PARAMETERIZATION atual de um banco de dados para a classe de consultas.

Para obter mais informações, consulte Compreendendo os guias de plano.

O número total de guias de plano que é possível criar só está limitado através de recursos do sistema disponíveis. De outro modo, guias de plano devem ser limitados para consultas de missão-crítica que são direcionados para aprimorar ou estabilizar o desempenho. Guias de plano não podem ser usados para influenciar a maioria da carga de consulta de um aplicativo implantado.

Recomendamos que as definições do guia de plano sejam reavaliadas e testadas quando o aplicativo for atualizado para uma versão nova do SQL Server. Os requisitos de ajuste de desempenho e o comportamento de correspondência do guia de plano podem ser alterados. Embora um guia inválido não acarrete a falha da consulta, o plano é compilado sem usar o guia de plano. Depois de atualizar um banco de dados para SQL Server 2008, recomendamos realizar as seguintes tarefas para validar os guias de plano já existentes ao usar a função sys.fn_validate_plan_guide. Como alternativa, é possível monitorar para guias de plano inválidos usando o evento Plan Guide Unsuccessful em SQL Server Profiler.

Observação
Os guias de plano só podem ser usados no SQL Server Standard, Developer, Evaluation e edições Enterprise. Entretanto, podem ser visualizados em qualquer edição. Também é possível anexar um banco de dados que contenha guias de plano a qualquer edição. Os guias de plano permanecem intactos quando o banco de dados é restaurado ou anexado a uma versão atualizada do SQL Server 2008.

Anexando dicas de consulta para um guia de plano

Pode ser usada qualquer combinação de dicas de consulta válidas em um guia de plano. Quando um guia de plano corresponde a uma consulta, a cláusula OPTION especificada na cláusula de dicas de um guia de plano é adicionada à consulta antes da compilação e otimização. Se uma consulta que está de acordo com um guia de plano já tem uma cláusula de OPTION, as dicas especificadas no guia substituem aquelas na consulta. Porém, para que um guia de plano corresponda a uma consulta que já tenha uma cláusula OPTION, deve-se incluir a cláusula OPTION da consulta ao especificar o texto da consulta, para que corresponda à instrução sp_create_plan_guide. Se você quiser que as dicas especificadas no guia de plano sejam adicionadas às dicas que já existem na consulta, em vez de substituí-las, é necessário especificar tanto as dicas originais como as dicas adicionais na cláusula OPTION do guia de plano.

Cuidado
Guias de plano que usam dicas de consulta de forma indevida podem causar problemas de compilação, execução ou de desempenho. Guias de plano devem ser usados apenas por desenvolvedores e administradores de banco de dados experientes.

Consulta de dicas comuns usadas nos guias de plano

As consultas que podem ser beneficiadas pelos guias de plano, geralmente, têm base em parâmetros podem ser realizadas de modo mais fraco porque usam planos de consulta em cache, cujos valores de parâmetro não representam um cenário de casos graves ou um mais representativos. As dicas do OPTIMIZE FOR e RECOMPILE podem ser usadas dicas para tratar deste problema. OPTIMIZE FOR instrui SQL Server para usar um valor particular para um parâmetro quando a consulta é aperfeiçoada. RECOMPILE instrui o servidor para descartar um plano de consulta após a execução, forçando o otimizador de consulta a recompilar um novo plano da próxima vez que a mesma consulta for executada. Para obter um exemplo, consulte Compreendendo os guias de plano.

Além disso, é possível especificar que a tabela indica INDEX e FORCESEEK como dicas de consulta. Quando especificadas como dicas de consulta, elas se comportam da mesma forma que uma tabela embutida ou dica de exibição. A dica INDEX força o otimizador de consulta a usar somente os índices especificados para acessar os dados na tabela ou exibição referenciada na consulta. A dica FORCESEEK força o otimizador a usar somente uma operação de busca de índice para acessar os dados na tabela ou exibição referenciada. Essas dicas fornecem funcionalidade de guia de plano adicional e permitem uma maior influência em relação à otimização de consultas que usam o guia de plano. Para obter um exemplo, consulte Usando as dicas de consulta INDEX e FORCESEEK em guias de plano.

Anexando um plano de consulta a um guia de plano

As guias de plano que se aplicam a um plano de consulta fixo são úteis quando se conhece um plano de execução existente que é melhor do que aquele selecionado pelo otimizador para uma consulta específica. Observe que aplicar um plano fixo a uma consulta, significa que o otimizador não pode mais adaptar o plano para a consulta para alterações nas estatísticas e nos índices. Ao considerar guias de plano que usem planos de consulta fixos, assegure-se de comparar os benefícios de aplicar um plano fixo com a incapacidade de adaptar o plano automaticamente como distribuição de dados e mudanças disponíveis nos índices.

É possível anexar um determinado plano de consulta a um guia de plano especificando-se o XML Showplan do plano no parâmetro xml_showplan na instrução sp_create_plan_guide ou especificando-se o identificador de plano de um plano armazenado em cache na instrução sp_create_plan_guide_from_handle. Ambos os métodos aplicam o plano de consulta fixo à consulta direcionada.

Requisitos para correspondência do guia de plano

Guias de plano são aplicados ao banco de dados no qual eles são criados. Portanto, somente os guias de plano existentes no banco de dados se tornam atuais quando uma consulta pode ser combinada com outra. Por exemplo, se AdventureWorks for o banco de dados atual e a consulta seguinte executa:

SELECT * FROM Person.Contact;

Somente guias de plano do banco de dados AdventureWorks são elegíveis para corresponderem a essa consulta.

Porém, se AdventureWorks for o banco de dados atual e as instruções seguintes são executadas:

USE DB1;

SELECT * FROM Person.Contact;

Somente os guias de plano no DB1 são elegíveis para combinar com a consulta porque ela é executada no contexto de DB1.

Para guias de plano com base em TEMPLATE ou SQL, SQL Server correspondem os valores para os argumentos @module\_or\_batch e @params para uma consulta, comparando os dois valores, dígito por dígito. Isso significa você deve fornecer o texto exatamente como SQL Server recebe no lote atual.

Quando @type = 'SQL' e @module\_or\_batch estão configurados como NULL, o valor do @module\_or\_batch é o valor de @stmt. Isso significa que o valor para statement_text deve ser fornecido exatamente no mesmo formato, dígito a dígito, como enviado para SQL Server. Nenhuma conversão interna é executada para facilitar essa correspondência.

De maneira geral, você deve testar guias de plano usando o SQL Server Profiler para verificar se a sua consulta está sendo correspondida à guia de plano. Testar o SQL - ou guias de plano com base em TEMPLATE ao executar os lotes do SQL Server Management Studio podem gerar resultados inesperados. Para obter mais informações, consulte Usando o SQL Server Profiler para criar e testar guias de plano.

Observação
O lote que contém a instrução na qual deseja criar o guia de plano não pode conter uma instrução de database USE.

Efeito de guia de plano no cache do plano

Criar um guia de plano em um módulo remove o plano de consulta para aquele módulo do cache do plano. Criar um guia de plano do tipo OBJECT ou SQL em um lote remove o plano que tem o mesmo valor de ‘hash’. Criar uma guia de plano de tipo TEMPLATE remove todos os lotes da única-instrução do cache do plano no banco de dados.