Utilizar las sugerencias de consulta FORCESEEK e INDEX en guías de plan

  • Artículo

Puede especificar las sugerencias de tabla INDEX y FORCESEEK como sugerencias de consulta. Cuando se especifican como sugerencias de consulta, estas sugerencias se comportan de la misma manera que una tabla insertada o una sugerencia de vista.

La sugerencia INDEX obliga al optimizador de consultas a usar sólo los índices especificados para tener acceso a los datos a los que se hace referencia en la tabla o vista de la consulta. La sugerencia FORCESEEK obliga al optimizador de consultas a usar sólo una operación de Index Seek para tener acceso a los datos a los que se hace referencia en la tabla o en la vista. Estas sugerencias se pueden utilizar en la cláusula OPTION de una guía de plan para influir en la optimización de una consulta. Cuando una consulta coincide con una guía de plan, la cláusula OPTION especificada en la guía de plan se agrega a la consulta antes de la compilación y optimización. Para obtener más información acerca de las guías de plan, vea Descripción de las guías de plan.

Advertencia

Las guías de plan que usan incorrectamente las sugerencias de consulta pueden provocar problemas de compilación, ejecución o rendimiento. Sólo deben utilizarlas los programadores y administradores de bases de datos con experiencia.

Cuando se especifican como una sugerencia de consulta, las sugerencias de tabla INDEX y FORCESEEK son válidas para los objetos siguientes:

  • Tablas

  • Vistas

  • Vistas indizadas

  • Expresiones de tabla comunes (la sugerencia se debe especificar en la instrucción SELECT cuyo conjunto de resultados rellena la expresión de tabla común).

  • Vistas de administración dinámica

  • Subconsultas con nombre

Las sugerencias de tabla no se pueden especificar para las funciones con valores de tabla, variables de tabla o las instrucciones OPENROWSET.

Para especificar una sugerencia de índice para una vista indizada, la sugerencia NOEXPAND también se debe especificar en la cláusula OPTION, de lo contrario se omite la sugerencia de índice. Para obtener más información, vea Resolver índices de vistas.

Para obtener más información acerca de la sintaxis que se usa para especificar las sugerencias INDEX y FORCESEEK como sugerencias de consulta, vea Sugerencias de consulta (Transact-SQL).

Prácticas recomendadas

Se recomiendan las siguientes prácticas:

  • Use las sugerencias ÍNDICE y FORCESEEK solamente como sugerencias de consulta en el contexto de una guía de plan o en consultas ad hoc al probar las instrucciones de guía de plan. Para todas las otras consultas ad hoc, especifique estas sugerencias como sugerencias de la tabla.

  • Antes de usar la sugerencia FORCESEEK, asegurarse de que las estadísticas en la base de datos son actuales y precisas.

    Las estadísticas actualizadas permiten al optimizador analizar con precisión el costo de los distintos planes de consulta y elegir un plan de buena calidad. Por consiguiente, recomendamos ajustar AUTO_CREATE_STATISTICS y AUTO_UPDATE_STATISTICS en ON (el valor predeterminado) para cada base de datos de usuario. Alternativamente, puede actualizar manualmente las estadísticas en una tabla o vista mediante la instrucción UPDATE STATISTICS.

  • No use innecesariamente la sugerencia INDEX en combinación con FORCESEEK. Es decir, si FORCESEEK solo genera un plan adecuado, al utilizar también la sugerencia INDEX, se pueden limitar excesivamente las opciones del optimizador. Además, una sugerencia INDEX hará que su consulta genere un error si cambia el esquema físico de su tabla para eliminar el índice especificado en la sugerencia. Por contraste, mientras exista como mínimo un índice utilizable en la tabla en la que se aplica la sugerencia FORCESEEK, la consulta se compilará aunque cambie sus estructuras de índice.

  • No use la sugerencia de INDEX (0) de INDEX (0) con la sugerencia FORCESEEK. INDEX (0) fuerza un recorrido de la tabla base. Cuando se usa con FORCESEEK, no se encuentra ningún plan y se devuelve el error 8622.

  • No use la sugerencia de consulta USE PLAN con la sugerencia FORCESEEK. Si lo hace, se omitirá la sugerencia FORCESEEK.

Usar las sugerencias INDEX y FORCESEEK con otras sugerencias de tabla

Se pueden especificar las sugerencias INDEX y FORCESEEK para una consulta que no tiene ninguna sugerencia de tabla existente, o bien se pueden usar para reemplazar una o más sugerencias INDEX o FORCESEEK en la consulta. Si una consulta que coincide con una guía de plan ya tiene una cláusula WITH que especifica estas sugerencias de tabla, las sugerencias especificadas en el parámetro @hints de la guía de plan reemplazan las de la consulta. Por ejemplo, si la consulta contiene la sugerencia de tabla WITH INDEX (PK_Employee_BusinessEntityID) para la tabla HumanResources.Employee y el parámetro @hints en la guía de plan especifica OPTION (TABLE HINT ( HumanResources.Employee, INDEX( AK_Employee_LoginID ) ), el optimizador de consultas usará el índice K_Employee_LoginID.

Las sugerencias de tabla distintas de INDEX y FORCESEEK no están permitidas como sugerencias de consulta en la guía de plan, a menos que la consulta ya tenga una cláusula WITH que especifique la sugerencia de tabla. En este caso, también se debe especificar una consulta coincidente como sugerencia de consulta mediante el uso de TABLE HINT en la cláusula OPTION para conservar la semántica de la consulta. Por ejemplo, si la consulta contiene la sugerencia de tabla NOLOCK, el parámetro @hints de la guía de plan también debe contener la sugerencia NOLOCK, junto con cualquier otra sugerencia de tabla INDEX o FORCESEEK, en la cláusula OPTION. Vea el ejemplo C más adelante en este tema. Cuando se especifica una sugerencia de tabla distinta de INDEX o FORCESEEK usando TABLE HINT en la cláusula OPTION sin una sugerencia de consulta coincidente, o viceversa, se genera el error 8702, que indica que la cláusula OPTION puede hacer que la semántica cambie y la consulta produzca un error.

Usar las sugerencias INDEX y FORCESEEK con otras sugerencias de consulta

Si una consulta que coincide con una guía de plan ya tiene una cláusula OPTION que especifica las sugerencias de consulta, las sugerencias de consulta especificadas en el parámetro @hints de la guía de plan reemplazan las de la consulta. Sin embargo, para que una guía de plan coincida con una consulta que ya tiene una cláusula OPTION, debe incluir la cláusula OPTION de la consulta al especificar el texto con el que debe coincidir en la instrucción sp_create_plan_guide (Transact-SQL). Si desea que las sugerencias especificadas en la guía de plan se agreguen a las que ya existen en la consulta, en lugar de sustituirlas, debe especificar tanto las originales como las adicionales en la cláusula OPTION de la guía de plan.

Ejemplos

A. Usar FORCESEEK

El ejemplo siguiente usa la sugerencia FORCESEEK del parámetro @hints de la guía de plan. Esta opción fuerza al optimizador a usar una operación INDEX SEEK para tener acceso a los datos de la tabla HumanResources.Employee. Observe que esto puede hacer que el optimizador use un índice distinto del que se especificó en la sugerencia de tabla.

USE AdventureWorks2008R2;
GO
EXEC sp_create_plan_guide 
    @name = N'Guide3', 
    @stmt = N'SELECT c.LastName, c.FirstName, HumanResources.Employee.JobTitle
              FROM HumanResources.Employee
              JOIN Person.Person AS c ON HumanResources.Employee.BusinessEntityID = c.BusinessEntityID
              WHERE HumanResources.Employee.OrganizationLevel = 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. Usar varias sugerencias de tabla

El ejemplo siguiente aplica la sugerencia INDEX a una tabla y la sugerencia FORCESEEK a otra.

USE AdventureWorks2008R2;
GO
EXEC sp_create_plan_guide 
    @name = N'Guide4', 
    @stmt = N'SELECT c.LastName, c.FirstName, e.JobTitle
              FROM HumanResources.Employee AS e 
              JOIN Person.Person AS c ON e.BusinessEntityID = c.BusinessEntityID
              WHERE OrganizationLevel = 3;', 
    @type = N'SQL',
    @module_or_batch = NULL, 
    @params = NULL, 
    @hints = N'OPTION (TABLE HINT ( e, INDEX( IX_Employee_OrganizationLevel_OrganizationNode ) ) 
                       , TABLE HINT ( c, FORCESEEK) )';
GO

C. Especificar sugerencias que afectan a la semántica

El ejemplo siguiente contiene dos sugerencias de tabla en la consulta: NOLOCK, que afecta a la semántica, e INDEX, que no afecta a la semántica. Para conservar la semántica de la consulta, la sugerencia NOLOCK se especifica en la cláusula OPTIONS de la guía de plan. Además de la sugerencia NOLOCK, las sugerencias de INDEX y FORCESEEK se especifican y reemplazan la sugerencia INDEX que no afecta a la semántica en la consulta cuando la instrucción se compila y optimiza.

USE AdventureWorks2008R2;
GO
EXEC sp_create_plan_guide 
    @name = N'Guide6', 
    @stmt = N'SELECT c.LastName, c.FirstName, e.JobTitle
              FROM HumanResources.Employee AS e 
              JOIN Person.Person AS c ON e.BusinessEntityID = c.BusinessEntityID
              WHERE OrganizationLevel = 3;',
    @type = N'SQL',
    @module_or_batch = NULL, 
    @params = NULL, 
    @hints = N'OPTION (TABLE HINT ( e, INDEX( IX_Employee_OrganizationLevel_OrganizationNode) , NOLOCK, FORCESEEK ))';
GO

El ejemplo siguiente muestra un método alternativo para conservar la semántica de la consulta y permitir al optimizador elegir un índice distinto del índice que se especificó en la sugerencia de tabla. Esto se consigue especificando la sugerencia NOLOCK en la cláusula OPTIONS (porque afecta a la semántica) y especificando la palabra clave TABLE HINT solamente con una referencia de tabla y ninguna sugerencia INDEX.

USE AdventureWorks2008R2;
GO
EXEC sp_create_plan_guide 
    @name = N'Guide7', 
    @stmt = N'SELECT c.LastName, c.FirstName, e.JobTitle
              FROM HumanResources.Employee AS e 
              JOIN Person.Person AS c ON e.BusinessEntityID = c.BusinessEntityID
              WHERE OrganizationLevel = 2;',
    @type = N'SQL',
    @module_or_batch = NULL, 
    @params = NULL, 
    @hints = N'OPTION (TABLE HINT ( e, NOLOCK))';
GO

D. Usar TABLE HINT para anular temporalmente una sugerencia de tabla existente

El ejemplo siguiente muestra cómo usar TABLE HINT sin especificar una sugerencia INDEX para anular temporalmente el comportamiento de la sugerencia de tabla INDEX que se especificó en la cláusula FROM de la consulta. Este método permite al optimizador elegir un índice distinto del índice especificado en la sugerencia de tabla.

USE AdventureWorks2008R2;
GO
EXEC sp_create_plan_guide 
    @name = N'Guide5', 
    @stmt = N'SELECT c.LastName, c.FirstName, e.JobTitle
              FROM HumanResources.Employee AS e WITH (INDEX (IX_Employee_OrganizationLevel_OrganizationNode))
              JOIN Person.Person AS c ON e.BusinessEntityID = c.BusinessEntityID
              WHERE OrganizationLevel = 3;', 
    @type = N'SQL',
    @module_or_batch = NULL, 
    @params = NULL, 
    @hints = N'OPTION (TABLE HINT(e))';
GO

Vea también

Referencia

Sugerencias de consulta (Transact-SQL)

Sugerencias de tabla (Transact-SQL)

sp_create_plan_guide (Transact-SQL)

sp_control_plan_guide (Transact-SQL)

Conceptos

Utilizar la sugerencia de tabla FORCESEEK