The SQL Booklet

Search SQL Server

Enter two or more characters.

Español

OPTION and query hints: influence compilation

A query describes the result we need. The SQL Server optimizer compares alternatives and chooses a plan to produce it. A query hint—an instruction applied to the query—restricts or changes some of those decisions.

Hints can help test a hypothesis or address a measured case. They can also prevent the optimizer from adapting when data, statistics, indexes, or the engine version changes. We therefore observe the plan and gather evidence first, then test the hint alongside a version without it.

OPTION: where query hints are written

OPTION appears at the end of a SELECT, INSERT, UPDATE, DELETE, or MERGE statement. Inside the parentheses, it accepts one or more comma-separated query hints.

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

In this query, RECOMPILE controls plan reuse and MAXDOP 2 limits statement-level parallelism. Sharing the same clause does not mean both solve the same problem: each hint changes a different decision.

RECOMPILE: compile with current values

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 creates a plan for that execution using the current values and does not retain that plan for reuse. It can help when different values need different strategies, but it adds compilation work every time the statement runs.

Compilation is the process that transforms a statement into an executable plan. Reusing a plan avoids repeating that work; recompiling allows the alternatives to be reconsidered with the current context. The comparison requires measuring both effects, not assuming that one always outperforms the other.

MAXDOP: limit statement-level parallelism

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 sets the maximum number of processors a parallel plan can use for this query. It does not force a parallel plan or reserve processors. The optimizer can still choose a serial plan.

Parallelism distributes part of the work among several workers. Whether it helps depends on cost, concurrency, and instance configuration. Lowering MAXDOP can reduce a query’s simultaneous consumption and can also increase its duration; the decision is verified with the actual workload.

MAXRECURSION: limit a recursive CTE

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 limits how many times the recursive member can repeat. It accepts values from 0 through 32767; the default is 100, and 0 removes the limit. An explicit limit helps stop an unexpected relationship, but it does not replace a correct termination condition.

A recursive CTE uses its own result again to produce the next level. The condition n.Numero < 500 defines when it should stop; MAXRECURSION 500 adds a boundary for this execution.

OPTIMIZE FOR: choose the value considered during compilation

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 changes the value the optimizer considers when estimating rows during compilation. With UNKNOWN, it uses the available statistical distribution instead of the current value. A specific value can also be provided, but that value is a compilation decision; it does not change the parameter or filter the result.

A cardinality estimate calculates how many rows a part of the plan may produce. That quantity influences access methods, joins, and memory. If the distribution changes, an assumption fixed through a hint may stop representing the workload.

USE HINT: name a specific behavior

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 enables a behavior identified by name. In this example, compilation does not use the first parameter value as its reference and instead considers the available distribution.

Supported names depend on the SQL Server version and its updates. Before retaining one, we verify that it exists on the target server and that the evidence shows the expected effect.

FORCE ORDER: preserve the written join order

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 preserves the join order expressed by the query. It does not force Nested Loops, Hash Match, or Merge Join, and SQL Server can still reverse an operator’s input roles when the transformation allows it.

Restricting the order can help verify whether a particular choice explains an observed change. It can also block a better alternative when tables grow or selectivity changes.

Aggregation and join hints

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 limits the available aggregation strategy. ORDER GROUP requests the order-based alternative. Both describe how rows are grouped, not which rows the query should return.

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, and MERGE JOIN inside OPTION restrict the join strategies allowed for the query. If several are combined, the optimizer chooses among those listed whenever it can produce a valid plan.

Query hint, join hint, and table hint

The hint’s location defines its scope:

  • a query hint is written inside OPTION and affects the statement;
  • a join hint is placed between two inputs and affects that join;
  • a table hint is written after a table or view and affects its access within that reference.
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 restricts only the strategy for that relationship. If the query contains more joins, it does not automatically turn all of them into Nested Loops.

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

FORCESEEK is a table hint: it limits the access methods considered for that table reference. It can produce an error when no valid plan exists under that restriction. Table hints are normally written with WITH (...); keeping that form avoids deprecated syntax and makes their scope visible.

What to check before retaining a hint

  1. Capture the actual plan and query metrics without the hint.
  2. State the hypothesis: which decision should change, and which evidence connects it to the problem.
  3. Test representative values, volumes, and distributions, not one favorable parameter.
  4. Compare estimated and actual rows, reads, CPU, duration, memory, waits, and concurrency.
  5. Repeat the test with and without the hint after updating statistics or changing the volume.
  6. Record the compatible version and a condition for reviewing or removing the restriction.

A different plan is an observation. A sustained improvement in the workload that matters is the verification. Retain the hint only when that verification justifies restricting the optimizer.