Usando as dicas de consulta INDEX e FORCESEEK em guias de plano

Você pode especificar as dicas de tabela INDEX e FORCESEEK como dicas de consulta. Quando especificadas como dicas de consulta, essas dicas 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. É possível usar essas dicas na cláusula OPTION de um guia de plano para influenciar a otimização de uma consulta. Quando uma consulta corresponde a um guia de plano, a cláusula OPTION especificada no guia de plano é adicionada à consulta antes da compilação e otimização. Para obter mais informações sobre guias de plano, consulte Compreendendo os guias 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.

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

• 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.)

• Exibições de gerenciamento dinâmico

• Subconsultas nomeadas

Dicas de tabela não podem ser especificadas para funções com valor de tabela, variáveis de tabela ou instruções OPENROWSET.

Para especificar uma dica de índice para uma exibição indexada, a dica NOEXPAND também deve ser especificada na cláusula OPTION; caso contrário, a dica de índice será ignorada. Para obter mais informações, consulte Resolvendo índices em exibições.

Para obter informações sobre a sintaxe usada para especificar as dicas INDEX e FORCESEEK como dicas de consulta, consulte dicas de consulta (Transact-SQL).

Práticas recomendadas

Sugerimos as seguintes práticas recomendadas:

• Use as dicas INDEX e FORCESEEK como dicas de consulta no contexto de um plano de guia ou consultas ad hoc ao testar instruções de guia de plano. Para todas as demais consultas ad hoc, especifique essas dicas como dicas de tabela.

• Antes de usar a dica FORCESEEK, verifique se as estatísticas no banco de dados são atuais e precisas.

  Estatísticas atualizadas permitem ao otimizador avaliar com precisão o custo de planos de consulta diferentes e escolher um plano de alta qualidade. Portanto, recomendamos que se defina AUTO_CREATE_STATISTICS e AUTO_UPDATE_STATISTICS como ON (o padrão) para cada banco de dados de usuário. Alternativamente, você pode atualizar as estatísticas manualmente em uma tabela ou exibição usando a instrução UPDATE STATISTICS.

• Não use a dica INDEX desnecessariamente em combinação com FORCESEEK. Ou seja, se FORCESEEK individualmente produzir um plano adequado, o uso da dica INDEX também poderá limitar excessivamente as escolhas do otimizador. Além disso, uma dica INDEX fará com que a sua consulta falhe se você alterar o esquema físico de sua tabela para eliminar o índice especificado na dica. Ao contrário, desde que pelo menos um índice utilizável exista na tabela na qual a dica FORCESEEK é aplicada, a consulta será compilada até mesmo quando você alterar as estruturas de índice.

• Não use a dica INDEX INDEX (0) com a dica FORCESEEK. INDEX (0) força uma varredura da tabela base. Quando usado com FORCESEEK, nenhum plano é encontrado e o erro 8622 é retornado.

• Não use a dica de consulta USE PLAN com a dica FORCESEEK. Se você o fizer, a dica FORCESEEK será ignorada.

Usando as dicas INDEX e FORCESEEK com outras dicas de tabela

As dicas INDEX e FORCESEEK podem ser especificadas para uma consulta que não tenha dicas de tabela existentes ou podem ser usadas para substituir uma ou mais dicas INDEX ou FORCESEEK existentes na consulta. Se uma consulta que corresponde a um guia de plano já tiver uma cláusula WITH especificando essas dicas de tabela, as dicas especificadas no parâmetro @hints do guia de plano substituirão as outras dicas da consulta. Por exemplo, se a consulta contiver a dica de tabela WITH INDEX (PK_Employee_EmployeeID) para a tabela HumanResources.Employee e o parâmetro @hints no guia de plano especificar OPTION (TABLE HINT ( HumanResources.Employee, INDEX( IX_Employee_ManagerID ) ), o otimizador de consulta usará o índice IX_Employee_ManagerID.

Dicas de tabela diferentes de INDEX e FORCESEEK não são permitidas como dicas de consulta no guia de plano, exceto se a consulta tiver uma cláusula WITH especificando a dica de tabela. Nesse caso, será necessário especificar também uma dica correspondente como dica de consulta usando TABLE HIT na cláusula OPTION para preservar a semântica da consulta. Por exemplo, se a consulta tiver a dica de tabela NOLOCK, o parâmetro @hints do guia de plano também deverá ter a dica NOLOCK, além de qualquer dica de tabela INDEX ou FORCESEEK na cláusula OPTION. Veja o exemplo C mais adiante nesse tópico. Quando uma dica de tabela diferente de INDEX ou FORCESEEK é especificada pela TABLEHINT na cláusula OPTION sem uma dica de consulta correspondente, ou vice-versa, o erro 8702 é gerado, indicando que a cláusula OPTION pode modificar a semântica da consulta, gerando falha na execução da consulta.

Usando as dicas INDEX e FORCESEEK com outras dicas de consulta

Se uma consulta que corresponde a um guia de plano já tiver uma cláusula OPTION especificando dicas de consulta, as dicas de consulta especificadas no parâmetro @hints do guia de plano substituirão as outras dicas da 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 (Transact-SQL). 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, deve-se especificar tanto as dicas originais quanto as dicas adicionais na cláusula OPTION do guia de plano.

Exemplos

A. Usando FORCESEEK

O exemplo a seguir usa a dica FORCESEEK no parâmetro @hints do guia de plano. Essa opção força o otimizador a usar uma operação de busca de índice para acessar os dados na tabela HumanResources.Employee. Observe que isso pode levar o otimizador a usar um índice diferente daquele especificado na dica de tabela.

USE AdventureWorks;
GO
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

B. Usando dicas de várias tabela

O exemplo a seguir aplica a dica INDEX a uma tabela e a dica FORCESEEK a outra.

USE AdventureWorks;
GO
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

C. Especificando dicas 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. Além da dica NOLOCK, as dicas INDEX e FORCESEEK são especificadas e substituem a dica INDEX, que não afeta a semântica, na consulta, quando a instrução é compilada e otimizada.

USE AdventureWorks;
GO
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 para permitir que o otimizador escolha um índice diferente do índice especificado na dica de tabela. Isso é feito especificando-se a dica NOLOCK na cláusula OPTIONS (porque não afeta a semântica) e especificando a palavra-chave TABLE HINT com apenas uma referência de tabela e nenhuma dica INDEX.

USE AdventureWorks;
GO
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

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

O exemplo a seguir mostra como usar TABLE HINT sem especificar uma dica INDEX para substituir o comportamento da dica de tabela INDEX especificado na cláusula de consulta FROM. Esse método permite ao otimizador escolher um índice diferente do índice especificado na dica de tabela.

USE AdventureWorks;
GO
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

Consulte também

Referência

dicas de consulta (Transact-SQL)

Dicas de tabela (Transact-SQL)

sp_create_plan_guide (Transact-SQL)

sp_control_plan_guide (Transact-SQL)

Conceitos

Usando a dica de tabela FORCESEEK