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

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

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

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

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

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

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

Conceitos