Query Hint (Transact-SQL)

Actualizado: 15 de septiembre de 2007

Especifica que se debe utilizar la sugerencia de consulta especificada en toda la consulta. La sugerencia de consulta afecta a todos los operadores de la instrucción. Si hay un argumento UNION implicado en la consulta principal, sólo la última consulta que implique una operación UNION puede contener la cláusula OPTION. Las sugerencias de consulta se especifican como parte de la cláusula OPTION. Si una o varias sugerencias de consulta provocan que el optimizador de consultas no genere un plan válido, se genera el error 8622.

Importante:
Como el optimizador de consultas de SQL Server 2005 selecciona normalmente el mejor plan de ejecución para las consultas, se recomienda que las sugerencias, incluida <query_hint>, se utilicen sólo como último recurso por los programadores y los administradores de bases de datos experimentados.

Se aplica a:

DELETE

INSERT

SELECT

UPDATE

Convenciones de sintaxis de Transact-SQL

Sintaxis

<query_hint > ::= 
{ { HASH | ORDER } GROUP 
  | { CONCAT | HASH | MERGE } UNION 
  | { LOOP | MERGE | HASH } JOIN 
  | FAST number_rows 
  | FORCE ORDER 
  | MAXDOP number_of_processors 
  | OPTIMIZE FOR ( @variable_name = literal_constant [ , ...n ] ) 
  | PARAMETERIZATION { SIMPLE | FORCED }
  | RECOMPILE
  | ROBUST PLAN 
  | KEEP PLAN 
  | KEEPFIXED PLAN
  | EXPAND VIEWS 
  | MAXRECURSION number 
  | USE PLAN N'xml_plan'
} 

Argumentos

• { HASH |ORDER } GROUP
  Especifica que las agregaciones especificadas en la cláusula GROUP BY, DISTINCT o COMPUTE de la consulta deben utilizar hash o un orden.

• { MERGE |HASH |CONCAT } UNION
  Especifica que todas las operaciones UNION se deben realizar mediante la mezcla, hash o concatenación de conjuntos UNION. Si se especifica más de una sugerencia UNION, el optimizador de consultas seleccionará la estrategia menos costosa entre las sugerencias especificadas.

  [!NOTA] Si también se especifica una <joint_hint> para una pareja de tablas combinadas en la cláusula FROM, tiene prioridad sobre cualquier <join_hint> especificada en la cláusula OPTION.

• { LOOP | MERGE | HASH } JOIN
  Especifica que todas las operaciones de combinación se realicen mediante LOOP JOIN, MERGE JOIN o HASH JOIN en toda la consulta. Si se especifica más de una sugerencia de combinación, el optimizador seleccionará la estrategia menos costosa de entre las permitidas.

  Si, en la misma consulta, se especifica también una sugerencia de combinación para una pareja de tablas específicas, ésta tendrá prioridad en la combinación de esas dos tablas aunque aún se deban cumplir las sugerencias de combinación. Por tanto, es posible que la sugerencia de combinación para la pareja de tablas sólo restrinja la selección de los métodos de combinación permitidos en la sugerencia de la consulta. Para obtener más información, vea Sugerencias (Transact-SQL).

• FAST number_rows
  Especifica que se optimice la consulta para una recuperación rápida de las primeras number_rows. Es un entero no negativo. Después de que se devuelven las primeras number_rows, la consulta continúa la ejecución y presenta su conjunto de resultados completo.

• FORCE ORDER
  Especifica que el orden de combinación que indica la sintaxis de la consulta se mantenga durante la optimización de la consulta.

  [!NOTA] El uso de FORCE ORDER no afecta el posible comportamiento de inversión de funciones del optimizador de consultas. Para obtener más información, vea Descripción de las combinaciones hash.

  Para obtener más información sobre cómo el optimizador de consultas de SQL Server aplica la sugerencia FORCE ORDER cuando una consulta contiene una vista, vea Resolución de vistas.

• MAXDOP number
  Invalida la opción de configuración max degree of parallelism de sp_configure para la consulta que especifica esta opción. La sugerencia de consulta MAXDOP puede superar el valor configurado con sp_configure. Se pueden aplicar todas las reglas semánticas utilizadas con la opción de configuración max degree of parallelism cuando se utiliza la sugerencia de consulta MAXDOP. Para obtener más información, vea max degree of parallelism (opción).

• OPTIMIZE FOR
  Indica al optimizador de consultas que utilice un valor concreto para una variable local cuando la consulta se compila y optimiza. El valor se utiliza sólo durante la optimización de la consulta y no durante la ejecución de la misma. OPTIMIZE FOR puede contrarrestar el comportamiento de detección de parámetros del optimizador o puede utilizarse cuando se crean guías de plan. Para obtener más información, vea Volver a compilar procedimientos almacenados y Optimizar consultas en aplicaciones implementadas mediante guías de plan.

• @variable_name
  Es el nombre de una variable local que se utiliza en una consulta, a la que se puede asignar un valor para utilizarlo con la sugerencia de consulta OPTIMIZE FOR.

• literal_constant
  Es un valor de constante literal al que se asigna @variable_name para utilizarlo con la sugerencia de consulta OPTIMIZE FOR. literal_constant se utiliza sólo durante la optimización de la consulta y no como el valor de @variable_name durante la ejecución de la consulta. literal_constant puede tener cualquier tipo de datos de sistema de SQL Server que se pueda expresar como una constante literal. El tipo de datos de literal_constant debe convertirse de forma implícita al tipo de datos al que @variable_name hace referencia en la consulta.

• ,…n
  Indica que se puede asignar más de un @variable_name a literal_constant para usarla con la sugerencia de consulta OPTIMIZE FOR.

• PARAMETERIZATION { SIMPLE | FORCED }
  Especifica las reglas de parametrización que aplica el optimizador de consultas de SQL Server cuando se compila la consulta.

  Importante:
  La sugerencia de consulta PARAMETERIZATION sólo puede especificarse en una guía de plan. No se puede especificar directamente en una consulta.

  SIMPLE instruye al optimizador de consultas para que intente Parametrización simple. FORCED instruye al optimizador para que intente Parametrizaciones forzadas. La sugerencia de consulta PARAMETERIZATION se utiliza para invalidar la configuración actual de la opción SET de base de datos PARAMETERIZATION de una guía de plan. Para obtener más información, vea Especificar el comportamiento de parametrización de consultas por medio de guías de plan.

• RECOMPILE
  Indica al SQL Server 2005 Database Engine (Motor de base de datos de SQL Server 2005) que descarte el plan generado para la consulta una vez ejecutada, obligando así al optimizador de consultas a que vuelva a compilar un plan de consulta la próxima vez que se ejecute la misma consulta. Sin especificar RECOMPILE, el Database Engine (Motor de base de datos) almacena en la memoria caché los planes de consulta y los reutiliza. Cuando se compilan planes de consulta, la sugerencia de consulta RECOMPILE utiliza los valores actuales de cualquier variable local de la consulta y, si la consulta está en un procedimiento almacenado, los valores actuales enviados a cualquier parámetro.

  RECOMPILE es una alternativa útil a la creación de un procedimiento almacenado que utiliza la cláusula WITH RECOMPILE cuando sólo se debe volver a compilar un subconjunto de consultas del procedimiento almacenado, en lugar de todo el procedimiento almacenado. Para obtener más información, vea Volver a compilar procedimientos almacenados. RECOMPILE también es útil al crear guías de plan. Para obtener más información, vea Optimizar consultas en aplicaciones implementadas mediante guías de plan.

• ROBUST PLAN
  Hace que el optimizador de consultas intente aplicar un plan que funcione según el tamaño máximo de fila posible en detrimento del rendimiento. Cuando se procesa la consulta, es posible que las tablas intermedias y los operadores necesiten almacenar y procesar filas más anchas que las filas de entrada. Las filas pueden llegar a ser tan anchas que, en algunos casos, el operador especificado no puede procesar la fila. Si esto sucede, el Database Engine (Motor de base de datos) genera un error durante la ejecución de la consulta. Mediante la utilización de ROBUST PLAN, puede indicar al optimizador de consultas que no tenga en cuenta los planes de consulta donde pueda ocurrir este problema.

  Si no es posible realizar tal plan, el optimizador de consultas devuelve un error en lugar de diferir la detección de errores hasta la ejecución de la consulta. Las filas pueden contener columnas de longitud variable; el Database Engine (Motor de base de datos) permite definir filas con un tamaño potencial máximo que supere la capacidad del Database Engine (Motor de base de datos) para procesarlas. Normalmente, a pesar del tamaño potencial máximo, una aplicación almacena filas cuyo tamaño real se encuentra dentro de los límites que puede procesar Database Engine (Motor de base de datos). Si Database Engine (Motor de base de datos) encuentra una fila demasiado larga, devuelve un error de ejecución.

• KEEP PLAN
  Hace que el optimizador de consultas aumente el umbral calculado para volver a compilar una consulta. El umbral calculado para volver a compilar es el momento en el que una consulta se vuelve a compilar automáticamente cuando se ha realizado el número calculado de cambios de columnas indizadas en una tabla al ejecutar las instrucciones UPDATE, DELETE o INSERT. Al especificar KEEP PLAN, se asegura de que no se volverá a compilar una consulta con tanta frecuencia cuando se producen varias actualizaciones en una tabla.

• KEEPFIXED PLAN
  Hace que el optimizador de consultas no compile de nuevo una consulta debido a cambios en las estadísticas. Al especificar KEEPFIXED PLAN, se asegura de que sólo se volverá a compilar una consulta si el esquema de las tablas subyacentes cambia o si sp_recompile se ejecuta en estas tablas.

• EXPAND VIEWS
  Especifica que las vistas indizadas se expanden y que el optimizador de consultas no considerará ninguna vista indizada como un sustituto de una parte de la consulta. Una vista se expande cuando se reemplaza el nombre de la vista por la definición de la vista en el texto de la consulta.

  Esta sugerencia de consulta prácticamente no permite el uso directo de vistas indizadas ni índices en vistas indizadas en el plan de consulta.

  La vista indizada no se expande sólo si se hace referencia directa a la vista en la parte SELECT de la consulta y se especifica WITH (NOEXPAND) o WITH (NOEXPAND, INDEX( index_val [ ,...n ] ) ). Para obtener más información acerca de la sugerencia de consulta WITH (NOEXPAND), vea FROM (Transact-SQL).

  La sugerencia sólo afecta a las vistas en la parte SELECT de las instrucciones, incluidas las instrucciones INSERT, UPDATE y DELETE.

• MAXRECURSION number
  Especifica el número máximo de llamadas de recursividad permitido para esta consulta. number es un número entero no negativo comprendido entre 0 y 32767. Cuando se especifica 0, no se aplica ningún límite. Si no se especifica esta opción, el límite predeterminado para el servidor es 100.

  Cuando se alcanza el número predeterminado o especificado para el límite de MAXRECURSION durante la ejecución de la consulta, la consulta finaliza y se devuelve un error.

  Debido a este error, todos los efectos de la instrucción se revierten. Si la instrucción es una instrucción SELECT, es posible que no se devuelva ningún resultado o que los resultados sean parciales. Puede que los resultados parciales no incluyan todas las filas de los niveles de recursividad que superen el nivel de recursividad máximo especificado.

  Para obtener más información, vea WITH common_table_expression (Transact-SQL).

• USE PLAN N**'xml_plan'**
  Hace que el optimizador de consultas use un plan de consulta existente para una consulta especificada por 'xml_plan'. Para obtener más información, vea Especificar planes de consulta mediante la exigencia de planes. USE PLAN no puede especificarse con las instrucciones INSERT, UPDATE ni DELETE.

Notas

No se pueden especificar sugerencias de consulta en una instrucción INSERT excepto cuando se utiliza una cláusula SELECT en la instrucción.

Sólo se pueden especificar sugerencias de consulta en la consulta de nivel superior, no en las subconsultas.

Ejemplos

A. Usar MERGE JOIN

En el siguiente ejemplo se especifica que la operación JOIN de la consulta está realizada por MERGE JOIN.

USE AdventureWorks;
GO
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. Usar OPTIMIZE FOR

En el siguiente ejemplo se indica al optimizador de consultas que utilice el valor 'Seattle' para la variable local @city_name al optimizar la consulta.

DECLARE @city_name nvarchar(30)
SET @city_name = 'Ascheim'
SELECT * FROM Person.Address
WHERE City = @city_name
OPTION ( OPTIMIZE FOR (@city_name = 'Seattle') );
GO

C. Usar MAXRECURSION

MAXRECURSION se puede utilizar para impedir que una expresión de tabla común recursiva con formato incorrecto entre en un bucle infinito. En el ejemplo siguiente se crea un bucle infinito intencionadamente y se utiliza la sugerencia MAXRECURSION para limitar el número de niveles de recursividad a dos.

USE AdventureWorks;
GO
--Creates an infinite loop
WITH cte (EmployeeID, ManagerID, Title) as
(
    SELECT EmployeeID, ManagerID, Title
    FROM HumanResources.Employee
    WHERE ManagerID IS NOT NULL
  UNION ALL
    SELECT cte.EmployeeID, cte.ManagerID, cte.Title
    FROM cte 
    JOIN  HumanResources.Employee AS e 
        ON cte.ManagerID = e.EmployeeID
)
--Uses MAXRECURSION to limit the recursive levels to 2
SELECT EmployeeID, ManagerID, Title
FROM cte
OPTION (MAXRECURSION 2);
GO

Después de corregir el error de código, ya no se requiere MAXRECURSION.

D. Usar UNION

En el siguiente ejemplo se utiliza la sugerencia de consulta MERGE UNION.

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

E. Usar HASH GROUP y FAST

En el siguiente ejemplo se utilizan las sugerencias de consulta HASH GROUP y FAST.

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

En el siguiente ejemplo se utiliza la sugerencia de consulta MAXDOP.

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

Vea también

Referencia

Sugerencias (Transact-SQL)

Ayuda e información

Obtener ayuda sobre SQL Server 2005

Historial de cambios