Artículo
03/30/2012

Utilizar la sugerencia de tabla FORCESEEK

La sugerencia de tabla FORCESEEK obliga al optimizador de consultas a usar sólo una operación de Index Seek como ruta de acceso a los datos de la tabla o vista a la que se hace referencia en la consulta. Puede usar esta sugerencia de la tabla para invalidar el plan predeterminado elegido por el optimizador de consultas para evitar problemas de rendimiento producidos por un plan de consultas ineficaz. Por ejemplo, si un plan contiene a tabla u operadores de exploración de índice, y las tablas correspondientes producen un número alto de lecturas durante la ejecución de la consulta, como se observa en la salida STATISTICS IO, al forzar una operación de index seek, se puede lograr un mejor rendimiento de las consultas. Esto es especialmente cierto cuando una cardinalidad inexacta o las estimaciones de costos hacen que el optimizador favorezca las operaciones de recorrido en el tiempo de compilación del plan.

FORCESEEK se aplica a ambas operaciones de búsqueda de índice agrupado y no agrupado. Se puede especificar para cualquier tabla o ver en la cláusula FROM de una instrucción SELECT y en la cláusula FROM <table_source> de una instrucción UPDATE o DELETE.

Advertencia
Como el optimizador de consultas de SQL Server selecciona normalmente el mejor plan de ejecución para las consultas, se recomienda que solo los programadores y administradores de bases de datos experimentados usen sugerencias y como último recurso.

Evaluar los planes de consultas para la aplicabilidad de FORCESEEK

La sugerencia de tabla de FORCESEEK puede ser útil cuando el plan de consultas usa una tabla o un operador de exploración de índice en una tabla o vista, pero un operador de index seek puede ser más eficaz. Considere la consulta siguiente y el plan de ejecución subsiguiente.

USE AdventureWorks2008R2;
GO
SELECT *
FROM Sales.SalesOrderHeader AS h
INNER JOIN Sales.SalesOrderDetail AS d 
    ON h.SalesOrderID = d.SalesOrderID 
WHERE h.TotalDue > 100
AND (d.OrderQty > 5 OR d.LineTotal < 1000.00);
GO

El siguiente plan de ejecución muestra que el optimizador de consultas eligió a un operador Clustered Index Scan para tener acceso a los datos en ambas tablas.

Plan de ejecución con operadores clustered index scan

Puede obligar al optimizador de consultas a que realice una operación de la búsqueda en la tabla Sales.SalesOrderDetail especificando la sugerencia FORCESEEK como se muestra en la consulta siguiente.

USE AdventureWorks2008R2;
GO
SELECT *
FROM Sales.SalesOrderHeader AS h
INNER JOIN Sales.SalesOrderDetail AS d WITH (FORCESEEK)
    ON h.SalesOrderID = d.SalesOrderID 
WHERE h.TotalDue > 100
AND (d.OrderQty > 5 OR d.LineTotal < 1000.00);
GO

El plan de ejecución siguiente muestra los resultados de utilizar la sugerencia FORCESEEK en la consulta. Se usa una operación de Clusteres Index Seek para tener acceso a los datos en la tabla Sales.SalesOrderDetail.

Plan de ejecución con operadores clustered index seek

Unión de índices y compatibilidad de intersección

La sugerencia FORCESEEK admite uniones de índices e intersecciones. La sugerencia más probablemente realiza el optimizador de consultas para utilizar estas técnicas. Para evitar desacelerar el tiempo de compilación de consultas simples, las uniones de índices e intersecciones se eligen normalmente sólo según reglas que tienen en cuenta la cardinalidad y selectividad de las columnas. Sin embargo, cuando se especifica la sugerencia FORCESEEK, se omiten tales reglas y estas técnicas siempre se tienen en cuenta. Por ejemplo, considere la siguiente consulta:

SELECT * FROM T WITH(FORCESEEK) WHERE T.a = 1 AND T.b = 2;

Si hay índices no agrupados y separados en columnas a y b en la tabla T, se puede elegir un plan de intersección de índice. Es decir, el plan contiene una operación Nonclustered index seek en la columna a y una operación Nonclustered index seek en la columna b y forma una intersección con los conjuntos de clave de índice resultantes antes de realizar una operación de búsqueda en la tabla base.

En el ejemplo siguiente, se elige un plan de unión de índice. Es decir, el plan contiene una operación de búsqueda en la columna a y una operación de búsqueda en la columna b y forma una intersección con las uniones y los conjuntos de clave de índice resultantes antes de realizar una operación de búsqueda en la tabla base.

SELECT * FROM T WITH(FORCESEEK) WHERE T.a = 1 OR T.b = 2;

Usar FORCESEEK en consultas que usan LIKE o IN

Las reglas del optimizador de consultas y la estimación de la cardinalidad pobre también pueden hacer una operación de recorrido de índice o de tabla en lugar de Index seek cuando una consulta usa IN o LIKE como predicados de búsqueda.

El ejemplo siguiente muestra cómo la sugerencia FORCESEEK puede obligar al optimizador de consultas a que realice una operación Index seek en lugar de un recorrido de tabla cuando LIKE o IN se usan como predicados de búsqueda. Para ver los planes de ejecución de la consulta, haga clic en el botón Incluir plan de ejecución real de la barra de herramientas antes de ejecutar el ejemplo.

USE tempdb;
GO
DROP TABLE t;
GO
CREATE TABLE t(i int UNIQUE, j int, vc varchar(100));
CREATE INDEX t_vc ON t(vc);
GO
DECLARE @p1 int, @p2 int, @p3 int, @p4 int, @p5 int;
SELECT * FROM t WHERE i IN (@p1, @p2, @p3, @p4, @p5);
GO
DECLARE @p1 int, @p2 int, @p3 int, @p4 int, @p5 int;
SELECT * FROM t WITH (FORCESEEK) WHERE i IN (@p1, @p2, @p3, @p4, @p5);
GO
SELECT * FROM t WHERE vc LIKE 'Test%';
GO
SELECT * FROM t WITH (FORCESEEK) WHERE vc LIKE 'Test%';
GO
DECLARE @vc varchar(100);
SELECT * FROM t WHERE vc LIKE @vc;
GO
DECLARE @vc varchar(100);
SELECT * FROM t WITH (FORCESEEK) where vc like @vc;
GO

Usar FORCESEEK en Vistas

FORCESEEK se puede especificar con o sin una sugerencia de índice. Al aplicar una sugerencia de tabla de FORCESEEK a una vista o vista indizada, la sugerencia FORCESEEK se propaga de forma recursiva sobre todas las tablas en la versión expandida de la vista. La sugerencia del índice, si se especifica, se omite. Si las tablas subyacentes no tienen por lo menos un índice cada una, no se busca ningún plan y se devuelve el error 8622.

Cuando se usan las sugerencias FORCESEEK y NOEXPAND juntas en una referencia a una vista indizada, la vista indizada se usa sin expandirla primero. La sugerencia FORCESEEK se aplica directamente a la vista indizada, que simplemente se trata como una tabla.

Si aplica una sugerencia FORCESEEK a una referencia de la tabla, la referencia de la tabla no puede participar en concordancia de la vista indizada. Sin embargo, otras partes de la consulta que no se ven afectadas por la sugerencia FORCESEEK pueden participar en la concordancia de la vista indizada. Esto es comparable al comportamiento de vista indizada que coincide cuando se usa con sugerencias INDEX.

Consideraciones acerca de prácticas recomendadas

Se recomiendan las siguientes prácticas recomendadas:

Antes de usar la sugerencia de tabla FORCESEEK, asegúrese 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.
Evalúe la consulta para elementos que pueden producir una pobre cardinalidad o estimaciones de costos y quite esos elementos si es posible. Por ejemplo, reemplace las variables local con parámetros o literales y limite el uso de funciones con valores de tabla de multi-instrucción y variables de tabla en la consulta. Para obtener más información acerca de otros elementos que hay que buscar, vea el tema acerca de las estadísticas usadas por el optimizador de consultas en Microsoft SQL Server 2005
No use innecesariamente la sugerencia INDEX en combinación con FORCESEEK. Es decir, si FORCESEEK solo genera un plan suficiente, 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 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.