• Artigo

Usando a dica de consulta USE PLAN

A dica de consulta USE PLAN assume xml_plan como argumento. xml_plan é um literal da cadeia de caracteres derivado do plano de consulta com formato XML que é produzido para uma consulta. A dica de consulta USE PLAN pode ser especificada como dica de consulta em uma instrução SQL autônoma ou especificada no parâmetro @hints de uma guia de plano. Para anexar um plano de consulta a uma guia de plano, é recomendável usar o parâmetro xml_showplan em sp_create_plan_guide ou o procedimento armazenado sp_create_plan_guide_from_handle.

Observação importanteImportante

Deve-se sempre indicar xml_plan como um literal Unicode especificando o prefixo N, como em N'xml_plan'. Ao fazer isso, fica garantido que qualquer caractere no plano específico para o padrão Unicode não seja perdido quando Mecanismo de banco de dados do SQL Server interpretar a cadeia.

Em SQL Server, planos de consulta em formato XML podem ser produzidos das seguintes maneiras:

Para obter mais informações sobre produção e análise de planos de consulta, consulte Analisando uma consulta.

O plano de consulta em formato XML especificado em xml_plan deve ser válido em relação ao Showplanxml.xsd de esquema XSD no diretório de instalação SQL Server. Adicionalmente, no caminho que contém os elementos <ShowPlanXML> <BatchSequence> <Batch> <Statements>, deve aparecer um dos seguintes:

  • Um ou mais elementos <StmtSimple>, exatamente um dos quais contém um subelemento<QueryPlan>.

  • Um elemento <StmtCursor> que tenha exatamente um subelemento<CursorPlan>.

  • Um ou mais elementos <StmtSimple> sem um subelemento<QueryPlan>, e um elemento <StmtCursor> que tenha um subelemento <CursorPlan>.

É possível alterar o plano antes de usá-lo usando USE PLAN, alterando ordens de junção e operadores e ajustando exames e buscas. Porém, o formato do plano ainda deve corresponder a Showplanxml.xsd. É possível que você não consiga impor um plano que tenha sido alterado. Ocorre um erro se você usar um plano em uma dica USE PLAN quando o plano não for um dos planos que SQL Server normalmente consideraria para a consulta durante a otimização.

Planos de consulta gerados com a dica de consulta USE PLAN são armazenados em cache como outros planos de consulta.

Limitações da dica de consulta USE PLAN

Alterações de banco de dados, como descartar índices, podem invalidar um plano de consulta especificado por USE PLAN. Um plano de consulta pode se tornar obsoleto mesmo se um objeto descartado não for mencionado diretamente no plano. Por exemplo, um índice exclusivo pode não ser mencionado explicitamente em um plano de consulta, mas o índice, entretanto, aplica uma restrição de exclusividade sobre os dados. Um plano de consulta que é mencionado por USE PLAN pode usar essa restrição para evitar o uso de determinados operadores para impor uma distinção.

Algumas vezes, a instalação de um service pack ou de uma nova versão de SQL Server pode impedir que você imponha um plano produzido por uma versão anterior. Assim, todas as dicas USE PLAN devem ser testadas sempre que o servidor for atualizado.

Ao usar a dica USE PLAN em uma consulta, todas as dicas de junção e dicas de índice usadas na mesma consulta são substituídas.

USE PLAN não pode ser usada com dicas de consulta FORCE ORDER, EXPAND VIEWS, GROUP, UNION ou JOIN ou quando SET FORCEPLAN estiver definida como ON.

Somente planos de consulta que podem ser encontrados pela estratégica de busca típica do otimizador de consulta podem ser impostos usando-se USE PLAN. Esses planos geralmente especificam que um filho de cada junção esteja no nível folha. Usar USE PLAN para impor outros tipos de consultas causará um erro.

Elementos de plano de consulta forçados

Nem todos os elementos do plano de consulta com formatação XML são impostos com a dica USE PLAN. São ignorados elementos que computam expressões de escalar e também algumas expressões relacionais. O plano de consulta é imposto para os seguintes tipos de elementos:

  • Estrutura de árvore de plano e ordem de avaliação.

  • Algoritmos de execução como tipos de junção, classificação e uniões.

  • Operações de índice como varreduras, buscas, interseções e uniões.

  • Objetos mencionados explicitamente como outras tabelas, índices e funções.

Em especial, SQL Server impõe os itens LogicalOp, PhysicalOp e NodeID encontrados no elemento <RelOp> e também quaisquer subelementos que pertencem ao operador <PhysicalOp>. Outro conteúdo no elemento <RelOp> não é considerado por USE PLAN.

Observação importanteImportante

Informações sobre estimativas de cardinalidade ditadas pelo elemento <EstimateRows> não são impostas pela dica de consulta USE PLAN. Como o otimizador de consulta usa estimativa de cardinalidade para determinar a quantidade de memória a ser destinada para a execução de uma consulta, deve-se manter as estatísticas precisas, mesmo quando estiver usando USE PLAN. Para obter mais informações, consulte Usando estatísticas para melhorar o desempenho de consultas.

A tabela a seguir lista os valores do operador relacional que são impostos com a dica USE PLAN para os itens PhysicalOp e LogicalOp e quaisquer subelementos exigidos para cada valor PhysicalOp. A tabela também inclui informações adicionais necessárias para cada operador na forma de caminhos no estilo XPath relativos ao subelemento.

PhysicalOp

LogicalOp

Subelemento

Informação adicional1

Concatenation

Concatenation

Async Concat

Concat

Não aplicável

Constant Scan

Constant Scan

ConstantScan

Não aplicável

Deleted Scan

Deleted Scan

DeletedScan

Object/@Table

UDX

UDX

Extension

@UDXName

Hash Match

Inner Join

Left Outer Join

Right Outer Join

Full Outer Join

Left Semi Join

Left Anti Semi Join

Right Semi Join

Right Anti Semi Join

Aggregate

Partial Aggregate

Flow Distinct

Union

Hash

Não aplicável

RID Lookup

RID Lookup

IndexScan

Object/@Database, Object/@Schema, Object/@Table

Index Scan

Clustered Index Scan

Index Scan

Clustered Index Scan

IndexScan

Object/@Database, Object/@Schema, Object/@Table Object/@Index

Index Seek

Clustered Index Seek

Index Seek

Clustered Index Seek

IndexScan

Object/@Database, Object/@Schema, Object/@Table Object/@Index

Inserted Scan

Inserted Scan

InsertedScan

Object/@Table

Log Row Scan

Log Row Scan

LogRowScan

Não aplicável

Merge Join

Inner Join

Left Outer Join

Right Outer Join

Full Outer Join

Left Semi Join

Left Anti Semi Join

Right Semi Join

Right Anti Semi Join

Cross Join

Concatenation

Union

Merge

Não aplicável

Merge Interval

Merge Interval

MergeInterval

Não aplicável

Nested Loops

Inner Join

Left Outer Join

Right Outer Join

Full Outer Join

Left Semi Join

Left Anti Semi Join

Right Semi Join

Right Anti Semi Join

Cross Join

NestedLoops

Não aplicável

Parallelism

Gather Streams

Repartition Streams

Distribute Streams

Parallelism

Não aplicável

Row Count Spool

Eager Spool

Lazy Spool

RowCountSpool2

Não aplicável

Segment

Segment

Segment

Não aplicável

Sequence

Sequence

Sequence

Não aplicável

Sequence Project

Compute Scalar

SequenceProject

Não aplicável

Sort

Sort

Distinct Sort

Sort

Não aplicável

Table Spool

Index Spool

Eager Spool

Lazy Spool

Spool2

@PrimaryNodeId (para spools secundários somente)

../RelOp/@NodeId (para RelOps representando apenas spools primários)

Stream Aggregate

Aggregate

StreamAggregate

Não aplicável

Switch

Switch

Switch

Não aplicável

Table Scan

Table Scan

TableScan

Object/@Database, Object/@Schema, Object/@Table

Table-valued function

Table-valued function

TableValuedFunction

Object/@Database, Object/@Schema, Object/@Table

(nome de função com valor de tabela é Objeto/@Table)

Top

Top

Top

Não aplicável

Sort

Sort

Sort

Não aplicável

Top Sort

TopN Sort

TopSort

Não aplicável

Table Insert

Insert

Update

Object/@Table

1 O número e a ordem desses inputs para cada operador relacional devem aparecer conforme exibido na tabela para impor um plano com USE PLAN.

2 A capacidade de impor um plano está limitada ao fato de o plano conter um subelemento <RowCountSpool>, ele pode aparecer em um plano forçado como um subelemento <RowCountSpool> ou <Spool>. Da mesma forma, se o plano contém um subelemento <Spool>, ele pode aparecer em um plano forçado como um subelemento <RowCountSpool> ou <Spool>.

Os operadores Assert, Bitmap, ComputeScalar e PrintDataFlow são ignorados por USE PLAN. O operador Filter é considerado por USE PLAN, mas sua localização exata no plano não pode ser imposta.

Para obter mais informações sobre os operadores lógicos e físicos usados em planos de consulta, consulte Referência de operadores lógicos e físicos.

Suporte de cursor

Você pode usar a dica de consulta USE PLAN juntamente com consultas que especificam cursores estáticos ou somente de avanço rápido, sejam eles exigidos por meio de uma função de Transact-SQL ou API. Cursores estáticos Transact-SQL com uma opção somente de avanço rápido têm suporte. Não há suporte para cursores dinâmicos, controlados por conjunto de chaves e de somente avanço.

Para obter mais informações, consulte Usando a dica de consulta USE PLAN em consultas com cursores.