One join plan flies for a small region and crawls for a large one. FORCE ORDER can preserve a helpful table order, but it can freeze a bad choice as data changes. Test several parameter values and keep a way to remove the hint.
Start With the Unhinted Query
The optimizer normally explores join orders within its search limits. Collect an actual execution plan for the query without a hint and save parameter values, table sizes, estimates, reads, CPU, and duration. The example joins customers, orders, and lines. Create or substitute these tables in a test database.
SELECT c.CustomerID,SUM(ol.Quantity) AS items
FROM dbo.Customer AS c
JOIN dbo.SalesOrder AS o ON o.CustomerID = c.CustomerID
JOIN dbo.OrderLine AS ol ON ol.OrderID = o.OrderID
WHERE c.RegionID = 7
AND o.OrderDate >= '20250101'
GROUP BY c.CustomerID;Look at the actual join inputs in the plan rather than assuming the written FROM order became the physical execution order. Join algorithms, parallelism, and associative transformations make the graphical layout less obvious than a simple left-to-right reading. I note the first large estimated-versus-actual row gap; that is frequently where an unfavorable order begins.
Test the Query With FORCE ORDER
Add OPTION (FORCE ORDER) to the same query and use the same parameters and data. The hint preserves the syntactic join order during optimization, but other plan choices remain available. Compare logical reads and elapsed time across several executions, not one warm-cache sample.
SELECT c.CustomerID,SUM(ol.Quantity) AS items
FROM dbo.Customer AS c
JOIN dbo.SalesOrder AS o ON o.CustomerID = c.CustomerID
JOIN dbo.OrderLine AS ol ON ol.OrderID = o.OrderID
WHERE c.RegionID = 7
AND o.OrderDate >= '20250101'
GROUP BY c.CustomerID
OPTION (FORCE ORDER);If RegionID is highly selective, beginning with customers can reduce later rows. If that region contains most customers and OrderDate is extremely selective, a date-first path can be better. The same written order can therefore help one parameter and hurt another. Which workload distribution will the production query see tomorrow?
Read Join Order in the Actual Plan
Trace data flow from leaf access operators through join operators. For each join, inspect outer and inner inputs, estimated and actual rows, join predicate, memory grant, spills, and lookups. A fixed order can produce a large intermediate result even when the final output is tiny. An Index Seek on each input does not guarantee an efficient overall join.
I compare IO for every table. A hint that lowers reads on one table while multiplying lookups on another can lose overall. For a parallel plan, include CPU and memory effects too. Save both plan XML files so the comparison is not reduced to a screenshot of icons.
Find Why the Optimizer Chose Poorly
Before retaining FORCE ORDER, inspect stale or skewed statistics, correlated predicates, missing indexes, implicit conversions, and parameter sensitivity. Fixing a bad estimate can let the optimizer choose the right order without a permanent hint. A temporary hint can be useful during an incident while a safer data or index change is tested.
SELECT s.name,STATS_DATE(s.object_id,s.stats_id) AS updated_at
FROM sys.stats AS s
WHERE s.object_id IN
(OBJECT_ID(N'dbo.Customer'),OBJECT_ID(N'dbo.SalesOrder'));A recent statistic is not necessarily representative for a skewed value. Compare actual and estimated rows at the earliest divergence. I test high-volume and low-volume regions, recent and older dates, and the procedure's normal parameter combinations. The goal is a robust plan, not a single fast benchmark.
Apply FORCE ORDER Through a Query Store Hint
On SQL Server 2022 and later, Query Store hints can attach OPTION(FORCE ORDER) to a captured query ID. This is useful when application code cannot be edited quickly. Enable and verify Query Store first, locate the correct query ID from its text and runtime context, and apply the hint in the user database. A query ID is database-specific.
EXEC sys.sp_query_store_set_hints
@query_id = 12345,
@query_hints = N'OPTION(FORCE ORDER)';
SELECT query_id,query_hint_text,last_query_hint_failure_reason_desc
FROM sys.query_store_query_hints
WHERE query_id = 12345;Replace 12345 with a verified ID. The hint can fail to apply, so check the status and a fresh plan. In my test database the hint attached with failure reason NONE, and clearing it left no hint row. Query Store hint behavior depends on engine version and feature support. I record who applied it, when, and what measurement justified it; an invisible emergency hint is hard for the next DBA to diagnose.
Remove the Hint When It Stops Helping
A Query Store hint is reversible without editing application SQL. Remove it with sp_query_store_clear_hints for the verified query ID, then capture a new execution and plan. Keep a before and after record because a plan change can come from statistics or data as well as the hint.
EXEC sys.sp_query_store_clear_hints @query_id = 12345;Do not clear all Query Store hints to fix one query. If the forced order helped only a narrow parameter set, a broader plan strategy or query rewrite can be better. I set an expiry review date on emergency hints and retest after the underlying statistics or index change ships.
Keep FORCE ORDER a Measured Choice
A Query Store hint applies to a query identity, not every similar-looking statement in the database. Capture the exact query text and query ID before setting it, and check the view after a fresh execution for a hint failure reason. If the query is parameterized in several forms, one hint can cover only one captured identity. Keep the deployment note precise enough for someone else to clear the correct ID.
Also test data-change direction. An order that starts with a selective table today can become poor after a bulk load or a changed region distribution. A fixed order can increase intermediate rows, memory grant, or spills even when the final row count stays the same. Monitor those counters, not just average duration.
The hint reduces the optimizer's freedom. That is valuable when evidence shows a consistent error in its choice, but it can block a better order as data shifts. Watch plan regressions after large loads, seasonal changes, or new indexes. Query Store runtime intervals help compare the same query across those events.
I have seen a hint look excellent against a small test set and struggle when a region became the largest one. The written join order was unchanged; the cardinalities were not. Keep representative test values and a removal procedure beside the hint. The performance result, rather than the presence of a hint, decides whether it stays.
Related reading on this blog: Avoid Join Hints: SQL in Sixty Seconds #172 and How to Use Multiple Hints Together for a Query? Interview Question of the Week #241.
FORCE ORDER is not a lasting cure by default, it is a measured hint with an exit path.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
