The SQL Booklet

Buscar en SQL Server

Escribe dos caracteres o más.

English

OPTION y query hints: influir en la compilación

Una consulta describe el resultado que necesitamos. El optimizador de SQL Server compara alternativas y elige un plan para producirlo. Un query hint —una indicación aplicada a la consulta— restringe o modifica alguna de esas decisiones.

Los hints pueden ayudar a comprobar una hipótesis o atender un caso medido. También pueden impedir que el optimizador se adapte cuando cambian los datos, las estadísticas, los índices o la versión del motor. Por eso primero observamos el plan y reunimos evidencia; después probamos el hint junto con una versión sin él.

OPTION: dónde se escriben los query hints

OPTION aparece al final de una instrucción SELECT, INSERT, UPDATE, DELETE o MERGE. Dentro de los paréntesis admite uno o varios query hints separados por comas.

SELECT
    p.IdCliente,
    COUNT_BIG(*) AS Pedidos
FROM dbo.Pedido AS p
GROUP BY p.IdCliente
OPTION (RECOMPILE, MAXDOP 2);

En esta consulta, RECOMPILE controla la reutilización del plan y MAXDOP 2 limita el paralelismo de la instrucción. Compartir la misma cláusula no significa que ambos resuelvan el mismo problema: cada hint modifica una decisión distinta.

RECOMPILE: compilar con los valores actuales

DECLARE @IdCliente INT = 42;

SELECT
    p.IdPedido,
    p.FechaPedido,
    p.Estado,
    p.Total
FROM dbo.Pedido AS p
WHERE p.IdCliente = @IdCliente
OPTION (RECOMPILE);

RECOMPILE crea un plan para esa ejecución utilizando los valores actuales y no conserva ese plan para reutilizarlo. Puede ser útil cuando valores distintos necesitan estrategias distintas, pero añade trabajo de compilación cada vez que se ejecuta la instrucción.

Una compilación es el proceso que transforma la instrucción en un plan ejecutable. Reutilizar un plan evita repetir ese trabajo; recompilar permite reconsiderar las alternativas con el contexto actual. La comparación requiere medir ambos efectos, no asumir que uno siempre supera al otro.

MAXDOP: limitar el paralelismo de una instrucción

SELECT
    p.Estado,
    COUNT_BIG(*) AS Pedidos,
    SUM(p.Total) AS TotalAcumulado
FROM dbo.Pedido AS p
GROUP BY p.Estado
OPTION (MAXDOP 2);

MAXDOP establece el número máximo de procesadores que puede utilizar un plan paralelo para esta consulta. No obliga a que el plan sea paralelo ni reserva procesadores. El optimizador todavía puede elegir un plan serial.

El paralelismo reparte parte del trabajo entre varios trabajadores. Su conveniencia depende del costo, la concurrencia y la configuración de la instancia. Reducir MAXDOP puede bajar el consumo simultáneo de una consulta y también aumentar su duración; la decisión se comprueba con la carga real.

MAXRECURSION: limitar una CTE recursiva

WITH Numeros AS
(
    SELECT 1 AS Numero

    UNION ALL

    SELECT n.Numero + 1
    FROM Numeros AS n
    WHERE n.Numero < 500
)
SELECT n.Numero
FROM Numeros AS n
OPTION (MAXRECURSION 500);

MAXRECURSION limita cuántas veces puede repetirse la parte recursiva. Acepta valores entre 0 y 32767; el valor predeterminado es 100 y 0 elimina el límite. Un límite explícito ayuda a detener una relación inesperada, pero no reemplaza una condición de terminación correcta.

Una CTE recursiva vuelve a utilizar su propio resultado para producir el siguiente nivel. La condición n.Numero < 500 define cuándo debe terminar; MAXRECURSION 500 añade una frontera para esta ejecución.

OPTIMIZE FOR: elegir el valor considerado al compilar

DECLARE @Estado VARCHAR(20) = 'Pendiente';

SELECT
    p.IdPedido,
    p.IdCliente,
    p.FechaPedido,
    p.Total
FROM dbo.Pedido AS p
WHERE p.Estado = @Estado
OPTION (OPTIMIZE FOR (@Estado UNKNOWN));

OPTIMIZE FOR cambia el valor que el optimizador considera al estimar filas durante la compilación. Con UNKNOWN, utiliza la distribución estadística disponible en lugar del valor actual. También puede indicarse un valor concreto, pero ese valor representa una decisión de compilación; no cambia el parámetro ni filtra el resultado.

Una estimación de cardinalidad calcula cuántas filas podría producir una parte del plan. Esa cantidad influye en accesos, uniones y memoria. Si la distribución cambia, una suposición fijada mediante un hint puede dejar de representar la carga.

USE HINT: nombrar un comportamiento específico

DECLARE @IdCliente INT = 42;

SELECT
    p.IdPedido,
    p.FechaPedido,
    p.Estado
FROM dbo.Pedido AS p
WHERE p.IdCliente = @IdCliente
OPTION (USE HINT ('DISABLE_PARAMETER_SNIFFING'));

USE HINT habilita un comportamiento identificado por nombre. En este ejemplo, la compilación no utiliza el primer valor del parámetro como referencia y considera la distribución disponible.

Los nombres admitidos dependen de la versión de SQL Server y de sus actualizaciones. Antes de conservar uno, comprobamos que exista en el servidor objetivo y que la evidencia muestre el efecto esperado.

FORCE ORDER: conservar el orden escrito de las uniones

SELECT
    c.IdCliente,
    p.IdPedido,
    d.IdDetalle
FROM dbo.Cliente AS c
INNER JOIN dbo.Pedido AS p
    ON p.IdCliente = c.IdCliente
INNER JOIN dbo.PedidoDetalle AS d
    ON d.IdPedido = p.IdPedido
OPTION (FORCE ORDER);

FORCE ORDER conserva el orden de unión expresado por la consulta. No obliga a utilizar Nested Loops, Hash Match o Merge Join, y SQL Server todavía puede invertir los papeles de las entradas de un operador cuando la transformación lo permite.

Restringir el orden puede servir para comprobar si una elección concreta explica un cambio observado. También puede bloquear una alternativa mejor cuando crecen las tablas o cambia la selectividad.

Hints de agregación y unión

SELECT
    p.Estado,
    COUNT_BIG(*) AS Pedidos,
    SUM(p.Total) AS TotalAcumulado
FROM dbo.Pedido AS p
GROUP BY p.Estado
OPTION (HASH GROUP);

HASH GROUP limita la estrategia disponible para la agregación. ORDER GROUP solicita la alternativa basada en orden. Ambos describen cómo se agrupan filas, no qué filas debe devolver la consulta.

SELECT
    c.IdCliente,
    p.IdPedido
FROM dbo.Cliente AS c
INNER JOIN dbo.Pedido AS p
    ON p.IdCliente = c.IdCliente
OPTION (LOOP JOIN);

LOOP JOIN, HASH JOIN y MERGE JOIN dentro de OPTION restringen las estrategias de unión permitidas para la consulta. Si se combinan varios, el optimizador elige entre los indicados siempre que pueda producir un plan válido.

Query hint, join hint y table hint

El lugar donde aparece el hint define su alcance:

  • un query hint se escribe dentro de OPTION y afecta la instrucción;
  • un join hint se coloca entre dos entradas y afecta esa unión;
  • un table hint se escribe después de una tabla o vista y afecta su acceso dentro de esa referencia.
SELECT
    c.IdCliente,
    p.IdPedido
FROM dbo.Cliente AS c
INNER LOOP JOIN dbo.Pedido AS p
    ON p.IdCliente = c.IdCliente;

INNER LOOP JOIN restringe únicamente la estrategia de esa relación. Si la consulta contiene más uniones, no convierte automáticamente todas en Nested Loops.

SELECT
    p.IdPedido,
    p.FechaPedido,
    p.Total
FROM dbo.Pedido AS p WITH (FORCESEEK)
WHERE p.IdCliente = 42;

FORCESEEK es un table hint: limita los accesos considerados para esa referencia a la tabla. Puede provocar un error si no existe un plan válido con esa restricción. Los table hints se escriben normalmente con WITH (...); conservar esa forma evita sintaxis obsoleta y hace visible su alcance.

Qué comprobar antes de conservar un hint

  1. Capturamos el plan real y las métricas de la consulta sin el hint.
  2. Escribimos la hipótesis: qué decisión esperamos cambiar y qué evidencia la relaciona con el problema.
  3. Probamos valores, volúmenes y distribuciones representativos, no un solo parámetro favorable.
  4. Comparamos filas estimadas y reales, lecturas, CPU, duración, memoria, esperas y concurrencia.
  5. Repetimos la prueba con el hint y sin él después de actualizar estadísticas o cambiar el volumen.
  6. Registramos la versión compatible y una condición para revisar o retirar la restricción.

Un plan diferente es una observación. Una mejora sostenida en la carga que importa es la comprobación. El hint se conserva únicamente cuando esa comprobación justifica limitar al optimizador.

Relacionado con