Dicas (Transact-SQL) – consulta

Aplica-se a: simSQL Server (todas as versões compatíveis) SimBanco de Dados SQL do Azure

As dicas de consulta especificam que as dicas mencionadas são usadas no escopo de uma consulta. Elas afetam todos os operadores na instrução. Se UNION estiver envolvida na consulta principal, só a última consulta envolvendo uma operação UNION poderá ter a cláusula OPTION. As dicas de consulta são especificadas como parte da cláusula OPTION. O Erro 8622 ocorrerá se uma ou mais dicas de consulta fizerem com que o otimizador de consulta não gere um plano válido.

Cuidado

Como o otimizador de consulta do SQL Server seleciona, normalmente, o melhor plano de execução para uma consulta, recomendamos usar dicas apenas como último recurso para desenvolvedores e administradores de banco de dados experientes.

Aplica-se a:

DELETE

INSERT

SELECT

UPDATE

MERGE

Sintaxe

<query_hint> ::=   
{ { HASH | ORDER } GROUP   
  | { CONCAT | HASH | MERGE } UNION   
  | { LOOP | MERGE | HASH } JOIN   
  | EXPAND VIEWS   
  | FAST <integer_value>   
  | FORCE ORDER   
  | { FORCE | DISABLE } EXTERNALPUSHDOWN
  | { FORCE | DISABLE } SCALEOUTEXECUTION
  | IGNORE_NONCLUSTERED_COLUMNSTORE_INDEX  
  | KEEP PLAN   
  | KEEPFIXED PLAN  
  | MAX_GRANT_PERCENT = <numeric_value>  
  | MIN_GRANT_PERCENT = <numeric_value>  
  | MAXDOP <integer_value>   
  | MAXRECURSION <integer_value>   
  | NO_PERFORMANCE_SPOOL   
  | OPTIMIZE FOR ( @variable_name { UNKNOWN | = <literal_constant> } [ , ...n ] )  
  | OPTIMIZE FOR UNKNOWN  
  | PARAMETERIZATION { SIMPLE | FORCED }   
  | QUERYTRACEON <integer_value>   
  | RECOMPILE  
  | ROBUST PLAN   
  | USE HINT ( <use_hint_name> [ , ...n ] )
  | USE PLAN N'<xml_plan>'  
  | TABLE HINT ( <exposed_object_name> [ , <table_hint> [ [, ]...n ] ] )  
}  
  
<table_hint> ::=  
{ NOEXPAND [ , INDEX ( <index_value> [ ,...n ] ) | INDEX = ( <index_value> ) ]  
  | INDEX ( <index_value> [ ,...n ] ) | INDEX = ( <index_value> )
  | FORCESEEK [ ( <index_value> ( <index_column_name> [,... ] ) ) ]  
  | FORCESCAN  
  | HOLDLOCK   
  | NOLOCK   
  | NOWAIT  
  | PAGLOCK   
  | READCOMMITTED   
  | READCOMMITTEDLOCK   
  | READPAST   
  | READUNCOMMITTED   
  | REPEATABLEREAD   
  | ROWLOCK   
  | SERIALIZABLE   
  | SNAPSHOT  
  | SPATIAL_WINDOW_MAX_CELLS = <integer_value>  
  | TABLOCK   
  | TABLOCKX   
  | UPDLOCK   
  | XLOCK  
}  

<use_hint_name> ::=
{ 'ASSUME_JOIN_PREDICATE_DEPENDS_ON_FILTERS'
  | 'ASSUME_MIN_SELECTIVITY_FOR_FILTER_ESTIMATES'
  | 'ASSUME_FULL_INDEPENDENCE_FOR_FILTER_ESTIMATES'
  | 'ASSUME_PARTIAL_CORRELATION_FOR_FILTER_ESTIMATES'
  | 'DISABLE_BATCH_MODE_ADAPTIVE_JOINS'
  | 'DISABLE_BATCH_MODE_MEMORY_GRANT_FEEDBACK'
  | 'DISABLE_DEFERRED_COMPILATION_TV'
  | 'DISABLE_INTERLEAVED_EXECUTION_TVF'
  | 'DISABLE_OPTIMIZED_NESTED_LOOP'
  | 'DISABLE_OPTIMIZER_ROWGOAL'
  | 'DISABLE_PARAMETER_SNIFFING'
  | 'DISABLE_ROW_MODE_MEMORY_GRANT_FEEDBACK'
  | 'DISABLE_TSQL_SCALAR_UDF_INLINING'
  | 'DISALLOW_BATCH_MODE'
  | 'ENABLE_HIST_AMENDMENT_FOR_ASC_KEYS'
  | 'ENABLE_QUERY_OPTIMIZER_HOTFIXES'
  | 'FORCE_DEFAULT_CARDINALITY_ESTIMATION'
  | 'FORCE_LEGACY_CARDINALITY_ESTIMATION'
  | 'QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_n'
  | 'QUERY_PLAN_PROFILE' 
}

Observação

Para ver a sintaxe do Transact-SQL para o SQL Server 2014 e versões anteriores, confira a Documentação das versões anteriores.

Argumentos

{ HASH | ORDER } GROUP

Especifica que as agregações que a cláusula GROUP BY ou DISTINCT da consulta descreve devem usar hash ou ordenação.

{ MERGE | HASH | CONCAT } UNION

Especifica que todas as operações UNION são executadas por mesclagem, hash ou concatenação de conjuntos de UNION. Se mais de uma dica de UNION for especificada, o otimizador de consulta selecionará a estratégia menos cara dentre as dicas especificadas.

{ LOOP | MERGE | HASH } JOIN

Especifica que todas as operações de junção são executadas por LOOP JOIN, MERGE JOIN ou HASH JOIN na consulta inteira. Se você especificar mais de uma dica de junção, o otimizador selecionará a estratégia de junção menos cara dentre as permitidas.

Se você especificar uma dica de junção na cláusula FROM da mesma consulta para um par de tabelas específico, essa dica de junção terá precedência na junção das duas tabelas. As dicas de consulta, no entanto, ainda devem ser respeitadas. A dica de junção para o par de tabelas pode restringir apenas a seleção dos métodos de junção permitidos na dica de consulta. Para obter mais informações, consulte Dicas de junção (Transact-SQL).

EXPAND VIEWS

Especifica que as exibições indexadas são expandidas. Também especifica que o otimizador de consulta não considera qualquer exibição indexada como uma substituição de qualquer bloco da consulta. Uma exibição é expandida quando sua definição substitui o seu nome no texto da consulta.

Esta dica de consulta desabilita o uso direto de exibições indexadas e índices em exibições indexadas no plano de consulta.

Observação

A exibição indexada permanecerá condensada se houver uma referência direta à exibição no bloco SELECT da consulta. A exibição também permanecerá condensada se você especificar WITH (NOEXPAND) ou WITH (NOEXPAND, INDEX( <index_value> [ , ...n ] ) ). Para obter mais informações sobre a dica de consulta NOEXPAND, confira Usando NOEXPAND.

A dica afetará apenas as exibições no bloco SELECT das instruções, incluindo as exibições nas instruções INSERT, UPDATE, MERGE e DELETE.

FAST <integer_value>

Especifica que a consulta é otimizada para recuperação rápida do primeiro número de linhas de <integer_value> . Esse resultado é um inteiro não negativo. Depois que o primeiro número de linhas de <integer_value> é retornado, a consulta continua a execução e produz seu conjunto de resultados completo.

FORCE ORDER

Especifica que a ordem de junção indicada pela sintaxe de consulta é preservada durante a otimização da consulta. Usar FORCE ORDER não afeta o possível comportamento de reversão de função do otimizador de consulta.

Observação

Em uma instrução MERGE, a tabela de origem é acessada antes da tabela de destino como a ordem de junção padrão, a menos que a cláusula WHEN SOURCE NOT MATCHED seja especificada. Especificar FORCE ORDER preserva esse comportamento padrão.

{ FORCE | DISABLE } EXTERNALPUSHDOWN

Forçar ou desabilitar o pushdown da computação de expressões qualificadas no Hadoop. Aplica-se somente a consultas que usam PolyBase. Não se aplicará ao armazenamento do Azure.

{ FORCE | DISABLE } SCALEOUTEXECUTION

Force ou desabilite a expansão da execução de consultas do PolyBase que usam tabelas externas em Clusters de Big Data do SQL Server 2019. Essa dica será seguida apenas por uma consulta que use a instância mestre de um cluster de Big Data do SQL. A expansão ocorrerá em todo o pool de computação do cluster de Big Data.

KEEP PLAN

Força o otimizador de consulta a relaxar o limite de recompilação estimado para uma consulta. O limite de recompilação estimado inicia uma recompilação automática para a consulta quando o número estimado de alterações na coluna indexada é feito em uma tabela com a execução de uma das seguintes instruções:

  • UPDATE
  • Delete (excluir)
  • MESCLAR
  • INSERT

Especificar KEEP PLAN assegura que uma consulta não seja recompilada tão frequentemente como quando há várias atualizações em uma tabela.

KEEPFIXED PLAN

Força o otimizador de consulta a não recompilar uma consulta devido às alterações nas estatísticas. Especificar KEEPFIXED PLAN garantirá que uma consulta seja recompilada apenas se o esquema das tabelas subjacentes for alterado ou se sp_recompile for executado nessas tabelas.

IGNORE_NONCLUSTERED_COLUMNSTORE_INDEX

Aplica-se a: SQL Server (começando com SQL Server 2012 (11.x)).

Impede a consulta de usar um índice columnstore otimizado para memória não clusterizado. Se a consulta contiver a dica de consulta para evitar o uso do índice columnstore e uma dica de índice para usar um índice columnstore, as dicas entrarão em conflito e a consulta retornará um erro.

MAX_GRANT_PERCENT = <numeric_value>

Aplica-se a: SQL Server (começando com SQL Server 2016 (13.x)) e Banco de Dados SQL do Azure.

O tamanho máximo de concessão de memória em PERCENT do limite de memória configurado. É garantido que a consulta não excederá esse limite. O limite real poderá ser inferior se a configuração do Resource Governor for mais baixa que o valor especificado por esta dica. Os valores válidos estão entre 0,0 e 100,0.

MIN_GRANT_PERCENT = <numeric_value>

Aplica-se a: SQL Server (começando com SQL Server 2016 (13.x)) e Banco de Dados SQL do Azure.

O tamanho mínimo de concessão de memória em PERCENT do limite de memória configurado. É garantido que a consulta obtenha MAX(required memory, min grant) porque é preciso pelo menos a memória necessária para iniciar uma consulta. Os valores válidos estão entre 0,0 e 100,0.

MAXDOP <integer_value>

Aplica-se a: SQL Server (começando com SQL Server 2008) e Banco de Dados SQL do Azure.

Substitui a opção de configuração de grau máximo de paralelismo de sp_configure. Também substitui o Resource Governor para a consulta que especifica essa opção. A dica de consulta MAXDOP pode exceder o valor configurado com sp_configure. Se MAXDOP exceder o valor configurado com o Resource Governor, o Mecanismo de Banco de Dados usará o valor de MAXDOP do Resource Governor, descrito em ALTER WORKLOAD GROUP (Transact-SQL). Todas as regras semânticas usadas com a opção de configuração max degree of parallelism são aplicáveis ao usar a dica de consulta MAXDOP. Para obter mais informações, veja Configurar a opção max degree of parallelism de configuração de servidor.

Aviso

Se MAXDOP estiver definido como 0, o servidor escolherá o máximo grau de paralelismo.

MAXRECURSION <integer_value>

Especifica o número máximo de recursões permitidas para esta consulta. number é um inteiro não negativo entre 0 e 32.767. Quando 0 é especificado, nenhum limite é aplicado. Se essa opção não for especificada, o limite padrão para o servidor será de 100.

Quando o número especificado ou padrão de limite de MAXRECURSION é atingido durante a execução da consulta, a consulta é encerrada e um erro é retornado.

Por causa desse erro, todos os efeitos da instrução são revertidos. Se a instrução for do tipo SELECT, poderão ser retornados resultados parciais ou nenhum resultado. Eventuais resultados parciais retornados podem não incluir todas as linhas em níveis de recursão acima do nível de recursão máximo especificado.

Para obter mais informações, confira WITH common_table_expression (Transact-SQL).

NO_PERFORMANCE_SPOOL

Aplica-se a: SQL Server (começando com SQL Server 2016 (13.x)) e Banco de Dados SQL do Azure.

Impede que um operador de spool seja adicionado aos planos de consulta (exceto para os planos em que o spool é necessário para assegurar uma semântica de atualização válida). O operador de spool pode reduzir o desempenho em alguns cenários. Por exemplo, o spool usa o tempdb e a contenção do tempdb pode ocorrer se há várias consultas simultâneas em execução com as operações de spool.

OPTIMIZE FOR ( @variable_name { UNKNOWN | = <literal_constant> }_ [ , ...n ] )

Instrui o otimizador de consulta a usar um valor específico para uma variável local quando a consulta é compilada e otimizada. O valor é usado somente durante a otimização da consulta e não durante sua execução.

@variable_name
É o nome de uma variável local usada em uma consulta para a qual um valor pode ser atribuído para uso com a dica de consulta OPTIMIZE FOR.

UNKNOWN
Especifica que o otimizador de consulta usa dados estatísticos em vez do valor inicial para determinar o valor de uma variável local durante a otimização da consulta.

literal_constant
É um valor constante literal que receberá @variable_name para uso com a dica de consulta OPTIMIZE FOR. literal_constant é usado somente durante a otimização da consulta e não como o valor de @variable_name durante a execução da consulta. literal_constant pode ser de um dos tipos de dados de sistema do SQL Server que pode ser expresso como uma constante literal. O tipo de dados de literal_constant precisa ser implicitamente conversível no tipo de dados que @variable_name referencia na consulta.

OPTIMIZE FOR pode anular o comportamento de detecção de parâmetro padrão do otimizador. Use OPTIMIZE FOR também quando criar guias de plano. Para obter mais informações, confira Recompilar um procedimento armazenado.

OPTIMIZE FOR UNKNOWN

Instrui o Otimizador de Consulta a usar a seletividade média do predicado em todos os valores de coluna em vez de usar o valor do parâmetro de runtime quando a consulta é compilada e otimizada.

Se você usar OPTIMIZE FOR @variable_name = literal_constant e OPTIMIZE FOR UNKNOWN na mesma dica de consulta, o otimizador de consulta usará a literal_constant especificada para um valor específico. O otimizador de consulta usará UNKNOWN para o restante dos valores de variável. Os valores só são usados durante a otimização de consulta e não durante a execução das consultas.

PARAMETERIZATION { SIMPLE | FORCED }

Especifica as regras de parametrização que o otimizador de consulta do SQL Server aplica à consulta quando ela é compilada.

Importante

A dica de consulta PARAMETERIZATION só pode ser especificada dentro de um guia de plano para substituir a configuração atual da opção SET do banco de dados PARAMETERIZATION. Ela não pode ser especificada diretamente dentro de uma consulta.
Para obter mais informações, confira Especificar comportamento de parametrização de consulta usando guias de plano.

SIMPLE instrui o otimizador de consulta a tentar parametrização simples. FORCED instrui o otimizador de consulta a tentar a parametrização forçada. Para obter mais informações, consulte Parametrização forçada no Guia de arquitetura de processamento de consulta e Parametrização simples no Guia de arquitetura de processamento de consulta.

QUERYTRACEON <integer_value>

Essa opção permite habilitar um sinalizador de rastreamento que afeta o plano somente durante uma compilação de consulta única. Assim como outras opções de nível de consulta, você pode usá-la junto com guias de plano para corresponder ao texto de uma consulta que está sendo executada em sessão e aplicar automaticamente um sinalizador de rastreamento que afetará o plano quando essa consulta estiver sendo compilada. A opção QUERYTRACEON só tem suporte para sinalizadores de rastreamento do Otimizador de Consulta. Para obter mais informações, consulte Sinalizadores de rastreamento.

Observação

Usar essa opção não retornará nenhum erro ou aviso se um número de sinalizador de rastreamento sem suporte for usado. Se o sinalizador de rastreamento especificado não for um que afete um plano de execução de consulta, a opção será silenciosamente ignorada.

Observação

Para usar mais de um sinalizador de rastreamento em uma consulta, especifique uma dica QUERYTRACEON para cada número de sinalizador de rastreamento diferente.

RECOMPILE

Instrui o Mecanismo de Banco de Dados do SQL Server a gerar um plano novo e temporário para a consulta e descartar esse plano imediatamente depois que a consulta conclui a execução. O plano de consulta gerado não substitui um plano armazenado em cache quando a mesma consulta é executada sem a dica RECOMPILE. Sem especificar RECOMPILE, o Mecanismo de Banco de Dados armazena em cache os planos de consulta e reutiliza-os. Ao compilar planos de consulta, a dica de consulta RECOMPILE usa os valores atuais de todas as variáveis locais na consulta. Se a consulta estiver em um procedimento armazenado, os valores atuais são passados para quaisquer parâmetros.

RECOMPILE é uma alternativa útil para criar um procedimento armazenado. RECOMPILE usa a cláusula WITH RECOMPILE quando apenas um subconjunto de consultas dentro do procedimento armazenado, em vez de todo o procedimento armazenado, deve ser recompilado. Para obter mais informações, confira Recompilar um procedimento armazenado. RECOMPILE também é útil para a criação de guias de plano.

ROBUST PLAN

Força o otimizador de consulta a tentar um plano que trabalhe para o tamanho máximo de linhas potenciais, possivelmente às custas do desempenho. Tabelas e operadores intermediários podem ter que armazenar e processar linhas maiores do que qualquer uma das linhas de entrada quando a consulta é processada. As linhas podem ser tão grandes que, às vezes, o operador específico não consegue processá-las. Se as linhas tiverem essa dimensão, o Mecanismo de Banco de Dados produzirá um erro durante a execução da consulta. Usar ROBUST PLAN, você instrui o otimizador de consulta a não considerar os plano de consulta que possam resultar nesse problema.

Se um plano não for possível, o otimizador de consulta retornará um erro, em vez de adiar a detecção de erros para a execução da consulta. Linhas podem conter colunas de tamanho variável. O Mecanismo de Banco de Dados permite definir linhas com o tamanho potencial máximo para além da capacidade de processamento do Mecanismo de Banco de Dados. Geralmente, apesar do tamanho potencial máximo, um aplicativo armazena linhas cujos tamanhos reais estão dentro dos limites de processamento do Mecanismo de Banco de Dados. Se o Mecanismo de Banco de Dados encontrar uma linha longa demais, será retornado um erro de execução.

USE HINT ( ' hint_name ' )

Aplica-se a: SQL Server (começando com SQL Server 2016 (13.x) SP1) e Banco de Dados SQL do Azure.

Fornece uma ou mais dicas adicionais ao processador de consultas. As dicas adicionais são especificadas por um nome de dica entre aspas simples.

Os seguintes nomes de dica são compatíveis:

  • 'ASSUME_JOIN_PREDICATE_DEPENDS_ON_FILTERS'
    Faz o SQL Server gerar um plano de consulta usando a suposição de Confinamento simples, em vez da suposição de Confinamento de base padrão para junções, no modelo de Estimativa de cardinalidade do otimizador de consulta do SQL Server 2014 (12.x) ou mais recente. O nome da dica é equivalente ao sinalizador de rastreamento 9476.

  • 'ASSUME_MIN_SELECTIVITY_FOR_FILTER_ESTIMATES'
    Faz com que o SQL Server gere um plano que usa a seletividade mínima ao estimar predicados AND para os filtros a serem considerados para correlação completa. O nome da dica é equivalente ao sinalizador de rastreamento 4137 quando usado com o modelo de estimativa de cardinalidade do SQL Server 2012 (11.x) e com versões anteriores, além disso, tem um efeito semelhante quando o sinalizador de rastreamento 9471 é usado com o modelo de estimativa de cardinalidade do SQL Server 2014 (12.x) ou superior.

  • 'ASSUME_FULL_INDEPENDENCE_FOR_FILTER_ESTIMATES'
    Faz com que o SQL Server gere um plano que usa a seletividade mínima máxima ao estimar predicados AND para os filtros a serem considerados para independência completa. O nome dessa dica é equivalente ao comportamento padrão do modelo de estimativa de cardinalidade de SQL Server 2012 (11.x) e versões anteriores e equivalente ao sinalizador de rastreamento 9472 quando usado com o modelo de estimativa de cardinalidade de SQL Server 2014 (12.x) ou posterior.
    Aplica-se ao: Banco de Dados SQL do Azure

  • 'ASSUME_PARTIAL_CORRELATION_FOR_FILTER_ESTIMATES'
    Faz com que o SQL Server gere um plano que usa a seletividade máxima para mínima ao estimar predicados AND para os filtros a serem considerados para correlação parcial. O nome dessa dica é o comportamento padrão do modelo de estimativa de cardinalidade de SQL Server 2014 (12.x) ou superior.
    Aplica-se ao: Banco de Dados SQL do Azure

  • 'DISABLE_BATCH_MODE_ADAPTIVE_JOINS'
    Desabilita junções adaptáveis do modo de lote. Para obter mais informações, confira Junções Adaptáveis de modo de lote.
    Aplica-se a: SQL Server (começando com SQL Server 2017 (14.x)) e Banco de Dados SQL do Azure

  • 'DISABLE_BATCH_MODE_MEMORY_GRANT_FEEDBACK'
    Desabilita os comentários de concessão de memória do modo de lote. Para obter mais informações, veja Batch mode memory grant feedback (Comentários de concessão de memória de modo de lote).
    Aplica-se a: SQL Server (começando com SQL Server 2017 (14.x)) e Banco de Dados SQL do Azure

  • 'DISABLE_DEFERRED_COMPILATION_TV'
    Desabilita a compilação adiada de variável da tabela. Para saber mais, veja Compilação adiada de variável da tabela.
    Aplica-se a: SQL Server (começando com SQL Server 2019 (15.x)) e Banco de Dados SQL do Azure

  • 'DISABLE_INTERLEAVED_EXECUTION_TVF'
    Desabilita a execução intercalada para funções com valor de tabela de várias instruções. Para saber mais, veja Execução intercalada para funções com valor de tabela de várias instruções.
    Aplica-se a: SQL Server (começando com SQL Server 2017 (14.x)) e Banco de Dados SQL do Azure

  • 'DISABLE_OPTIMIZED_NESTED_LOOP'
    Instrui o processador de consultas a não usar uma operação de classificação (classificação em lote) para junções otimizadas de loops aninhados ao gerar um plano de consulta. O nome da dica é equivalente ao sinalizador de rastreamento 2340.

  • 'DISABLE_OPTIMIZER_ROWGOAL'
    Faz com que o SQL Server gere um plano que não usa as modificações de meta de linha com consultas que contêm estas palavras-chave:

    • INÍCIO
    • OPTION (FAST N)
    • IN
    • EXISTS

    O nome da dica é equivalente ao sinalizador de rastreamento 4138.

  • 'DISABLE_PARAMETER_SNIFFING'
    Instrui o otimizador de consulta a usar a distribuição média de dados durante a compilação de uma consulta com um ou mais parâmetros. Essa instrução cria o plano de consulta independentemente do valor de parâmetro que foi usado pela primeira vez quando a consulta foi compilada. O nome da dica é equivalente ao sinalizador de rastreamento 4136 ou à definição PARAMETER_SNIFFING = OFF da Configuração de Escopo do Banco de Dados.

  • 'DISABLE_ROW_MODE_MEMORY_GRANT_FEEDBACK'
    Desabilita os comentários de concessão de memória do modo de linha. Para obter mais informações, veja Batch mode memory grant feedback (Comentários de concessão de memória de modo de lote).
    Aplica-se a: SQL Server (começando com SQL Server 2019 (15.x)) e Banco de Dados SQL do Azure

  • 'DISABLE_TSQL_SCALAR_UDF_INLINING'
    Desabilita o embutimento de UDF escalar. Para saber mais, confira Scalar UDF Inlining (Embutimento de UDF escalar).
    Aplica-se a: SQL Server (começando com SQL Server 2019 (15.x)) e Banco de Dados SQL do Azure

  • 'DISALLOW_BATCH_MODE'
    Desabilita a execução do modo de lote. Para obter mais informações, consulte Modos de execução.
    Aplica-se a: SQL Server (começando com SQL Server 2019 (15.x)) e Banco de Dados SQL do Azure

  • 'ENABLE_HIST_AMENDMENT_FOR_ASC_KEYS'
    Habilita as estatísticas rápidas geradas automaticamente (aditamento de histograma) para qualquer coluna de índice inicial para a qual a estimativa de cardinalidade seja necessária. O histograma usado para estimar a cardinalidade será ajustado no tempo de compilação da consulta para considerar o valor máximo ou mínimo real dessa coluna. O nome da dica é equivalente ao sinalizador de rastreamento 4139.

  • 'ENABLE_QUERY_OPTIMIZER_HOTFIXES'
    Habilita hotfixes do otimizador de consulta (alterações liberadas nas atualizações cumulativas do SQL Server e nos Service Packs). O nome da dica é equivalente ao sinalizador de rastreamento 4199 ou à definição QUERY_OPTIMIZER_HOTFIXES = ON da Configuração de Escopo do Banco de Dados.

  • 'FORCE_DEFAULT_CARDINALITY_ESTIMATION'
    Força o otimizador de consulta a usar o modelo de estimativa de cardinalidade que corresponde ao nível de compatibilidade do banco de dados atual. Use essa dica para substituir a definição LEGACY_CARDINALITY_ESTIMATION = ON da Configuração de Escopo do Banco de Dados ou o sinalizador de rastreamento 9481.

  • 'FORCE_LEGACY_CARDINALITY_ESTIMATION'
    Força o otimizador de consulta a usar o modelo de estimativa de cardinalidade do SQL Server 2012 (11.x) e de versões anteriores. O nome da dica é equivalente ao sinalizador de rastreamento 9481 ou à definição LEGACY_CARDINALITY_ESTIMATION = ON da Configuração de Escopo do Banco de Dados.

  • 'QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_n'
    Força o comportamento do otimizador de consulta em um nível de consulta. Esse comportamento ocorrerá se a consulta tiver sido compilada com o nível de compatibilidade do banco de dados n, em que n é um nível de compatibilidade do banco de dados com suporte (por exemplo, 100, 130 etc.). Confira sys.dm_exec_valid_use_hints para obter uma lista atual de valores com suporte para n.
    Aplica-se a: SQL Server (começando com SQL Server 2017 (14.x) CU10) e Banco de Dados SQL do Azure

    Observação

    A dica QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_n não substituirá a configuração de estimativa de cardinalidade padrão ou herdada se ela for forçada pela configuração de escopo do banco de dados, pelo sinalizador de rastreamento ou por outra dica de consulta, como QUERYTRACEON.
    Essa dica só afeta o comportamento do otimizador de consulta. Ela não afeta outros recursos do SQL Server que podem depender do nível de compatibilidade do banco de dados, como a disponibilidade de determinados recursos de banco de dados.
    Para saber mais sobre essa dica, confira Escolha do desenvolvedor: dicas do modelo de execução de consulta.

  • 'QUERY_PLAN_PROFILE'
    Permite a criação de perfil leve para a consulta. Quando uma consulta que contém essa nova dica é concluída, um novo Evento Estendido, query_plan_profile, é disparado. Esse evento estendido expõe as estatísticas de execução e o plano de execução real XML semelhante ao evento estendido query_post_execution_showplan, mas apenas para consultas que contêm a nova dica.
    Aplica-se a: SQL Server (começando com o SQL Server 2016 (13.x) SP2 CU3 e SQL Server 2017 (14.x) CU11).

    Observação

    Se você habilitar a coleta de evento estendido query_post_execution_showplan, isso adicionará a infraestrutura de criação de perfil padrão a cada consulta que esteja sendo executada no servidor e, portanto, poderá afetar o desempenho geral do servidor.
    Se você habilitar a coleção do evento estendido query_thread_profile para usar infraestrutura de criação de perfil leve, isso resultará em muito menos sobrecarga de desempenho, mas ainda afetará o desempenho geral do servidor.
    Se você habilitar o evento estendido de query_plan_profile, isso só habilitará a infraestrutura de criação de perfil leve para uma consulta executada com QUERY_PLAN_PROFILE, portanto, não afetará outras cargas de trabalho no servidor. Use essa dica para criar o perfil de uma consulta específica sem afetar outras partes da carga de trabalho do servidor. Para saber mais sobre a criação de perfil leve, confira Infraestrutura de criação de perfil de consulta.

A lista de todos os nomes de USE HINT compatíveis pode ser consultada usando a exibição de gerenciamento dinâmico sys.dm_exec_valid_use_hints.

Dica

Os nomes de dica diferenciam maiúsculas de minúsculas.

Importante

Algumas dicas USE HINT podem entrar em conflito com os sinalizadores de rastreamento habilitados no nível global ou no nível da sessão, ou com as definições de configurações de escopo do banco de dados. Nesse caso, a dica no nível da consulta (USE HINT) sempre terá precedência. Se um USE HINT estiver em conflito com outra dica de consulta ou com um sinalizador de rastreamento habilitado no nível da consulta (como por QUERYTRACEON), o SQL Server gerará um erro ao tentar executar a consulta.

USE PLAN N' <xml_plan> '

Força o otimizador de consulta a usar um plano de consulta existente para uma consulta especificada por ' xml_plan ' . USE PLAN não pode ser especificado com instruções INSERT, UPDATE, MERGE ou DELETE.

O plano de execução resultante forçado por esse recurso será o mesmo ou semelhante ao plano que está sendo forçado. Como o plano resultante pode não ser idêntico ao plano especificado por USE PLAN, o desempenho dos planos pode variar. Em casos raros, a diferença de desempenho pode ser significativa e negativa. Nesse caso, o administrador deve remover o plano forçado.

TABLE HINT ( <exposed_object_name> [ , <table_hint> [ [ , ]...n ] ] )

Aplica a dica de tabela especificada à tabela ou exibição que corresponde ao exposed_object_name. É recomendável usar uma dica de tabela como uma dica de consulta apenas no contexto de um guia de plano.

<exposed_object_name> pode ser uma das seguintes referências:

  • Quando um alias é usado para a tabela ou exibição na cláusula FROM da consulta, exposed_object_name é o alias.

  • Quando um alias não é usado, exposed_object_name é a correspondência exata da tabela ou exibição referenciada na cláusula FROM. Por exemplo, se a tabela ou exibição for referenciada usando um nome de duas partes, exposed_object_name será esse mesmo nome de duas partes.

Quando você especifica exposed_object_name sem especificar também uma dica de tabela, todos os índices especificados na consulta como parte de uma tabela para o objeto são desconsiderados. O otimizador de consulta, em seguida, determina o uso do índice. Você pode usar essa técnica para eliminar o efeito de uma dica de tabela INDEX quando não puder modificar a consulta original. Consulte o exemplo J.

<table_hint> ::= { NOEXPAND [ , INDEX ( <index_value> [ ,...n ] ) | INDEX = ( <index_value> ) ] | INDEX ( <index_value> [ ,...n ] ) | INDEX = ( <index_value> ) | FORCESEEK [ ( <index_value> ( <index_column_name> [ , ... ] )) ] | FORCESCAN | HOLDLOCK | NOLOCK | NOWAIT | PAGLOCK | READCOMMITTED | READCOMMITTEDLOCK | READPAST | READUNCOMMITTED | REPEATABLEREAD | ROWLOCK | SERIALIZABLE | SNAPSHOT | SPATIAL_WINDOW_MAX_CELLS = <integer_value> | TABLOCK | TABLOCKX | UPDLOCK | XLOCK }
É a dica a ser aplicada à tabela ou exibição que corresponde a exposed_object_name como uma dica de consulta. Para obter uma descrição dessas dicas, consulte Dicas de tabela (Transact-SQL).

Dicas de tabela diferentes de INDEX, FORCESCAN e FORCESEEK não são permitidas como dicas de consulta, a não ser que a consulta possua uma cláusula WITH que especifique a dica de tabela. Para saber mais, confira a seção de Comentários.

Cuidado

Especificar FORCESEEK com parâmetros limita mais o número de planos que podem ser considerados pelo Otimizador de Consulta em comparação à especificação FORCESEEK sem parâmetros. Isso pode resultar em um erro "Não é possível gerar o plano" em mais casos. Em uma versão futura, as modificações internas no Otimizador de Consulta poderão permitir que mais planos sejam considerados.

Comentários

Dicas de consultas não podem ser especificadas em uma instrução INSERT, exceto quando uma cláusula SELECT for usada dentro da instrução.

Só podem ser especificadas dicas de consulta na consulta de nível superior, e não em subconsultas. Quando uma dica de tabela é especificada como uma dica de consulta, a dica pode ser especificada na consulta de nível superior ou em uma subconsulta. No entanto, o valor especificado para <exposed_object_name> na cláusula TABLE HINT deve corresponder exatamente ao nome exposto na consulta ou subconsulta.

Especificando dicas de tabela como dicas de consulta

É recomendável usar a dica de tabela INDEX, FORCESCAN ou FORCESEEK como dica de consulta apenas no contexto de um guia de plano. Os guias de plano são úteis quando não é possível modificar a consulta original, por exemplo, por se tratar de um aplicativo de terceiros. A dica de consulta especificada na guia de plano é adicionada à consulta antes de ela ser compilada e otimizada. Para consultas ad hoc, use a cláusula TABLE HINT apenas ao testar instruções de guia de plano. Para todas as demais consultas ad hoc, é recomendável especificar essas dicas apenas como dicas de tabela.

Quando especificadas como uma dica de consulta, as dicas de tabela INDEX, FORCESCAN e FORCESEEK são válidas para os objetos a seguir:

  • Tabelas
  • Exibições
  • Exibições indexadas
  • Expressões de tabela comuns (a dica deve ser especificada na instrução SELECT cujo conjunto de resultados popula a expressão de tabela comum)
  • DMVs (exibições de gerenciamento dinâmico)
  • Subconsultas nomeadas

Você pode especificar as dicas de tabela INDEX, FORCESCAN e FORCESEEK como dicas de consulta para uma consulta que não tenha dicas de tabela. Você também pode usá-las para substituir as dicas existente INDEX, FORCESCAN ou FORCESEEK na consulta, respectivamente.

Dicas de tabela diferentes de INDEX, FORCESCAN e FORCESEEK não são permitidas como dicas de consulta, a não ser que a consulta possua uma cláusula WITH que especifique a dica de tabela. Nesse caso, uma dica correspondente também deve ser especificada como uma dica de consulta. Especifique a dica correspondente como uma dica de consulta usando TABLE HINT na cláusula OPTION. Essa especificação preserva a semântica da consulta. Por exemplo, se a consulta contiver a dica de tabela NOLOCK, a cláusula OPTION no parâmetro @hints do guia de plano também precisará conter a dica NOLOCK. Consulte o exemplo K.

Especificando dicas com as dicas do Repositório de Consultas

Você pode impor dicas sobre consultas identificadas por meio do Repositório de Consultas sem fazer alterações de código usando o recurso de Dicas do Repositório de Consultas (versão prévia). Use o procedimento armazenado sys.sp_query_store_set_hints para aplicar uma dica a uma consulta. Confira o Exemplo N.

Exemplos

a. Usando MERGE JOIN

O exemplo a seguir especifica que MERGE JOIN executa a operação JOIN na consulta. O exemplo usa o banco de dados AdventureWorks2012.

SELECT *   
FROM Sales.Customer AS c  
INNER JOIN Sales.CustomerAddress AS ca ON c.CustomerID = ca.CustomerID  
WHERE TerritoryID = 5  
OPTION (MERGE JOIN);  
GO    

B. Usando OPTIMIZE FOR

O exemplo a seguir instrui o Otimizador de Consulta a usar o valor 'Seattle' para @city_name e usar a seletividade média do predicado em todos os valores de coluna para @postal_code ao otimizar a consulta. O exemplo usa o banco de dados AdventureWorks2012.

CREATE PROCEDURE dbo.RetrievePersonAddress
@city_name NVARCHAR(30),  
 @postal_code NVARCHAR(15)
AS
SELECT * FROM Person.Address  
WHERE City = @city_name AND PostalCode = @postal_code  
OPTION ( OPTIMIZE FOR (@city_name = 'Seattle', @postal_code UNKNOWN) );  
GO

C. Usando MAXRECURSION

MAXRECURSION pode ser usado para impedir que uma expressão de tabela comum recursiva malformada entre em loop infinito. O exemplo a seguir cria um loop infinito intencionalmente e usa a dica MAXRECURSION para limitar o número de níveis de recursão para dois. O exemplo usa o banco de dados AdventureWorks2012.

--Creates an infinite loop  
WITH cte (CustomerID, PersonID, StoreID) AS  
(  
    SELECT CustomerID, PersonID, StoreID  
    FROM Sales.Customer  
    WHERE PersonID IS NOT NULL  
  UNION ALL  
    SELECT cte.CustomerID, cte.PersonID, cte.StoreID  
    FROM cte   
    JOIN  Sales.Customer AS e   
        ON cte.PersonID = e.CustomerID  
)  
--Uses MAXRECURSION to limit the recursive levels to 2  
SELECT CustomerID, PersonID, StoreID  
FROM cte  
OPTION (MAXRECURSION 2);  
GO  

Depois que o erro de codificação for corrigido, MAXRECURSION não será mais necessário.

D. Usando MERGE UNION

O exemplo a seguir usa a dica de consulta MERGE UNION. O exemplo usa o banco de dados AdventureWorks2012.

SELECT *  
FROM HumanResources.Employee AS e1  
UNION  
SELECT *  
FROM HumanResources.Employee AS e2  
OPTION (MERGE UNION);  
GO  

E. Usando HASH GROUP e FAST

O exemplo a seguir usa as dicas de consulta HASH GROUP e FAST. O exemplo usa o banco de dados AdventureWorks2012.

SELECT ProductID, OrderQty, SUM(LineTotal) AS Total  
FROM Sales.SalesOrderDetail  
WHERE UnitPrice < $5.00  
GROUP BY ProductID, OrderQty  
ORDER BY ProductID, OrderQty  
OPTION (HASH GROUP, FAST 10);  
GO    

F. Usando MAXDOP

O exemplo a seguir usa a dica de consulta MAXDOP. O exemplo usa o banco de dados AdventureWorks2012.

SELECT ProductID, OrderQty, SUM(LineTotal) AS Total  
FROM Sales.SalesOrderDetail  
WHERE UnitPrice < $5.00  
GROUP BY ProductID, OrderQty  
ORDER BY ProductID, OrderQty  
OPTION (MAXDOP 2);    
GO

G. Usando INDEX

O exemplo a seguir usa a dica INDEX. O primeiro exemplo especifica um único índice. O segundo exemplo especifica vários índices para uma única referência de tabela. Em ambos os exemplos, como você aplica a dica INDEX em uma tabela que usa um alias, a cláusula TABLE HINT também deve especificar o mesmo alias do nome do objeto exposto. O exemplo usa o banco de dados AdventureWorks2012.

EXEC sp_create_plan_guide   
    @name = N'Guide1',   
    @stmt = N'SELECT c.LastName, c.FirstName, e.Title  
              FROM HumanResources.Employee AS e   
              JOIN Person.Contact AS c ON e.ContactID = c.ContactID  
              WHERE e.ManagerID = 2;',   
    @type = N'SQL',  
    @module_or_batch = NULL,   
    @params = NULL,   
    @hints = N'OPTION (TABLE HINT(e, INDEX (IX_Employee_ManagerID)))';  
GO  
EXEC sp_create_plan_guide   
    @name = N'Guide2',   
    @stmt = N'SELECT c.LastName, c.FirstName, e.Title  
              FROM HumanResources.Employee AS e  
              JOIN Person.Contact AS c ON e.ContactID = c.ContactID  
              WHERE e.ManagerID = 2;',   
    @type = N'SQL',  
    @module_or_batch = NULL,   
    @params = NULL,   
    @hints = N'OPTION (TABLE HINT(e, INDEX(PK_Employee_EmployeeID, IX_Employee_ManagerID)))';  
GO    

H. Usando FORCESEEK

O exemplo a seguir usa a dica de tabela FORCESEEK. A cláusula TABLE HINT também deve especificar o mesmo nome de duas partes que o nome do objeto exposto. Especifique o nome ao aplicar a dica INDEX em uma tabela que usa um nome de duas partes. O exemplo usa o banco de dados AdventureWorks2012.

EXEC sp_create_plan_guide   
    @name = N'Guide3',   
    @stmt = N'SELECT c.LastName, c.FirstName, HumanResources.Employee.Title  
              FROM HumanResources.Employee  
              JOIN Person.Contact AS c ON HumanResources.Employee.ContactID = c.ContactID  
              WHERE HumanResources.Employee.ManagerID = 3  
              ORDER BY c.LastName, c.FirstName;',   
    @type = N'SQL',  
    @module_or_batch = NULL,   
    @params = NULL,   
    @hints = N'OPTION (TABLE HINT( HumanResources.Employee, FORCESEEK))';  
GO    

I. Usando várias dicas de tabela

O exemplo a seguir aplica a dica INDEX a uma tabela e a dica FORCESEEK a outra. O exemplo usa o banco de dados AdventureWorks2012.

EXEC sp_create_plan_guide   
    @name = N'Guide4',   
    @stmt = N'SELECT e.ManagerID, c.LastName, c.FirstName, e.Title  
              FROM HumanResources.Employee AS e   
              JOIN Person.Contact AS c ON e.ContactID = c.ContactID  
              WHERE e.ManagerID = 3;',   
    @type = N'SQL',  
    @module_or_batch = NULL,   
    @params = NULL,   
    @hints = N'OPTION (TABLE HINT (e, INDEX( IX_Employee_ManagerID))   
                       , TABLE HINT (c, FORCESEEK))';  
GO  

J. Usando TABLE HINT para substituir uma dica de tabela existente

O exemplo a seguir mostra como usar a dica TABLE HINT. É possível usar a dica sem especificar uma dica para substituir o comportamento da dica de tabela INDEX que você especifica na cláusula FROM da consulta. O exemplo usa o banco de dados AdventureWorks2012.

EXEC sp_create_plan_guide   
    @name = N'Guide5',   
    @stmt = N'SELECT e.ManagerID, c.LastName, c.FirstName, e.Title  
              FROM HumanResources.Employee AS e WITH (INDEX (IX_Employee_ManagerID))  
              JOIN Person.Contact AS c ON e.ContactID = c.ContactID  
              WHERE e.ManagerID = 3;',   
    @type = N'SQL',  
    @module_or_batch = NULL,   
    @params = NULL,   
    @hints = N'OPTION (TABLE HINT(e))';  
GO    

K. Especificando dicas de tabela que afetam a semântica

O exemplo a seguir contém duas dicas de tabela na consulta: NOLOCK, que afeta a semântica, e INDEX, que não afeta a semântica. Para preservar a semântica da consulta, a dica NOLOCK é especificada na cláusula OPTIONS do guia de plano. Em conjunto com a dica NOLOCK, especifique as dicas INDEX e FORCESEEK e substitua a dica INDEX, que não afeta a semântica, na consulta durante a compilação e otimização da instrução. O exemplo usa o banco de dados AdventureWorks2012.

EXEC sp_create_plan_guide   
    @name = N'Guide6',   
    @stmt = N'SELECT c.LastName, c.FirstName, e.Title  
              FROM HumanResources.Employee AS e   
                   WITH (NOLOCK, INDEX (PK_Employee_EmployeeID))  
              JOIN Person.Contact AS c ON e.ContactID = c.ContactID  
              WHERE e.ManagerID = 3;',  
    @type = N'SQL',  
    @module_or_batch = NULL,   
    @params = NULL,   
    @hints = N'OPTION (TABLE HINT (e, INDEX(IX_Employee_ManagerID), NOLOCK, FORCESEEK))';  
GO    

O exemplo a seguir mostra um método alternativo para preservar a semântica da consulta e permitir que o otimizador escolha um índice diferente do índice especificado na dica de tabela. Permita que o otimizador escolha especificando a dica NOLOCK na cláusula OPTIONS. Você especifica a dica porque ela afeta a semântica. Em seguida, especifica a palavra-chave TABLE HINT com apenas uma referência de tabela e nenhuma dica INDEX. O exemplo usa o banco de dados AdventureWorks2012.

EXEC sp_create_plan_guide   
    @name = N'Guide7',   
    @stmt = N'SELECT c.LastName, c.FirstName, e.Title  
              FROM HumanResources.Employee AS e   
                   WITH (NOLOCK, INDEX (PK_Employee_EmployeeID))  
              JOIN Person.Contact AS c ON e.ContactID = c.ContactID  
              WHERE e.ManagerID = 2;',  
    @type = N'SQL',  
    @module_or_batch = NULL,   
    @params = NULL,   
    @hints = N'OPTION (TABLE HINT (e, NOLOCK))';  
GO  

L. Usando USE HINT

O exemplo a seguir usa as dicas de consulta RECOMPILE e USE HINT. O exemplo usa o banco de dados AdventureWorks2012.

SELECT * FROM Person.Address  
WHERE City = 'SEATTLE' AND PostalCode = 98104
OPTION (RECOMPILE, USE HINT ('ASSUME_MIN_SELECTIVITY_FOR_FILTER_ESTIMATES', 'DISABLE_PARAMETER_SNIFFING')); 
GO  

M. Usar a QUERYTRACEON HINT

O exemplo a seguir usa as dicas de consulta QUERYTRACEON. O exemplo usa o banco de dados AdventureWorks2012. Você pode habilitar todos os hotfixes que afetam o plano controlados pelo sinalizador de rastreamento 4199 para uma consulta específica usando a seguinte consulta:

SELECT * FROM Person.Address  
WHERE City = 'SEATTLE' AND PostalCode = 98104
OPTION (QUERYTRACEON 4199);

Você também pode usar vários sinalizadores de rastreamento como na seguinte consulta:

SELECT * FROM Person.Address  
WHERE City = 'SEATTLE' AND PostalCode = 98104
OPTION  (QUERYTRACEON 4199, QUERYTRACEON 4137);

N. Usando dicas do Repositório de Consultas (versão prévia)

O recurso Dicas do Repositório de Consultas (versão prévia) no Banco de Dados SQL do Azure fornece um método fácil de usar para formatar planos de consulta sem alterar o código do aplicativo.

Primeiro, identifique a consulta que já foi executada nas exibições de catálogo do Repositório de Consultas, por exemplo:

SELECT q.query_id, qt.query_sql_text
FROM sys.query_store_query_text qt 
INNER JOIN sys.query_store_query q ON 
    qt.query_text_id = q.query_text_id 
WHERE query_sql_text like N'%ORDER BY ListingPrice DESC%'  
  AND query_sql_text not like N'%query_store%';
GO

O seguinte exemplo aplica a dica para forçar o avaliador de cardinalidade herdado query_id 39, identificado no Repositório de Consultas:

EXEC sys.sp_query_store_set_hints @query_id= 39, @query_hints = N'OPTION(USE HINT(''FORCE_LEGACY_CARDINALITY_ESTIMATION''))';

O seguinte exemplo aplica a dica para impor um tamanho máximo de concessão de memória em PERCENT do limite de memória configurado para query_id 39, identificado no Repositório de Consultas:

EXEC sys.sp_query_store_set_hints @query_id= 39, @query_hints = N'OPTION(MAX_GRANT_PERCENT=10)';

O seguinte exemplo aplica várias dicas de consulta a query_id 39, incluindo RECOMPILE, MAXDOP 1 e o comportamento do otimizador de consulta do SQL 2012:

EXEC sys.sp_query_store_set_hints @query_id= 39, @query_hints = N'OPTION(RECOMPILE, MAXDOP 1, USE HINT(''QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_110''))';

Confira também