One query needs a different planning choice, but changing the whole instance is too broad. USE HINT applies supported optimizer behavior at statement scope. Verify the hint exists on your server, compare its actual effect, and keep the reason and removal condition with the query.
Discover the USE HINT Names on This Server
sys.dm_exec_valid_use_hints lists hint names supported by the running engine. Read that list instead of copying a hint name from an unrelated build. USE HINT provides supported query-level controls for specific optimizer behavior without relying on an instance-wide trace-flag change.
I start with the baseline plan and the actual problem. A hint is useful only when it addresses an identified planning issue. The fact that a supported name sounds relevant does not establish that the query needs it or that it improves the workload.
The following query locates two examples used later. If a name is absent, inspect your version and supported feature set before executing that hint. Do not disguise an unsupported hint by adding another undocumented setting. Supported discovery is the first check. Optimizer switches are easy to collect, but a query is not a souvenir cabinet for interesting switches.
SELECT name FROM sys.dm_exec_valid_use_hints
WHERE name IN('DISABLE_OPTIMIZER_ROWGOAL','FORCE_LEGACY_CARDINALITY_ESTIMATION')
ORDER BY name;Establish a Baseline With the Normal Statement
The sample below uses synthetic data in temporary tables. A TOP query with a join supplies a controlled opportunity to inspect row-goal behavior. Enable actual execution plans and capture IO and time. The tiny population is for correctness and plan inspection, not proof of a production performance benefit.
Keep the query's result and ordering requirements unchanged across comparisons. Row goals can favor plans designed to obtain the requested first rows quickly. That is useful for some workloads and problematic when the plan's assumptions do not match the real data distribution.
I test representative parameters on the actual design before applying a hint. A plan that succeeds for one selective value can perform differently for a broad value. Keep those cases in the comparison. A hint should be a response to the observed planning problem, not a method for forcing the same attractive plan picture for every possible input.
CREATE TABLE #HintCustomers(ID int PRIMARY KEY,RegionID int);
CREATE TABLE #HintOrders(ID int PRIMARY KEY,CustomerID int,Amount int);
INSERT #HintCustomers VALUES(1,10),(2,20);
INSERT #HintOrders VALUES(1,1,10),(2,1,20),(3,2,30);
SELECT TOP(1) c.ID,o.Amount
FROM #HintCustomers AS c JOIN #HintOrders AS o ON o.CustomerID=c.ID
WHERE c.RegionID=10 ORDER BY o.Amount DESC,o.ID;
Compare Row-Goal and Cardinality USE HINT Options Separately
DISABLE_OPTIMIZER_ROWGOAL disables the optimizer's row-goal adjustment for qualifying statements. It does not remove TOP or change the requested result. Compare the resulting access and join decisions with the baseline, then inspect the actual execution work.
FORCE_LEGACY_CARDINALITY_ESTIMATION applies the older cardinality-estimation model to the statement. It is a different intervention, addressing a different planning dimension. Do not combine both hints immediately and then claim you know which one helped.
The next block runs the same query with each hint separately. Keep the test population, parameter values, and session settings constant. Check correct results first, then compare estimates, actual rows, IO, and execution behavior. If neither change improves the identified issue, remove the hints from the investigation rather than keeping them because they did not raise an error.
SELECT TOP(1) c.ID,o.Amount
FROM #HintCustomers AS c JOIN #HintOrders AS o ON o.CustomerID=c.ID
WHERE c.RegionID=10 ORDER BY o.Amount DESC,o.ID
OPTION(USE HINT('DISABLE_OPTIMIZER_ROWGOAL'));
SELECT TOP(1) c.ID,o.Amount
FROM #HintCustomers AS c JOIN #HintOrders AS o ON o.CustomerID=c.ID
WHERE c.RegionID=10 ORDER BY o.Amount DESC,o.ID
OPTION(USE HINT('FORCE_LEGACY_CARDINALITY_ESTIMATION'));Attach a Tested Hint Through Query Store
SQL Server 2022 supports Query Store hints for applying eligible query hints without changing the submitted SQL text. The query must be captured and identified in Query Store. Select the correct query_id and verify its text and context before attaching anything.
The following block looks up the captured statement by its text and attaches the hint only when it finds one. In the default AUTO capture mode, Query Store skips trivial queries, so this tiny sample can return the not-captured message instead. On a real system, confirm the query_id and its text before attaching anything. The hint string uses the same OPTION syntax, with doubled quotation marks inside the T-SQL string. This does not attach a hint to every similar-looking query automatically.
What happens if the application changes the statement text? It can receive a new query identity, leaving the old attachment irrelevant to the new request. Keep Query Store hint ownership connected to application deployments. The ability to apply a change outside the source code is operationally useful, but it also creates a configuration dependency that future maintainers need to see.
DECLARE @QueryID bigint=
(SELECT MAX(q.query_id)
FROM sys.query_store_query AS q
JOIN sys.query_store_query_text AS t ON t.query_text_id=q.query_text_id
WHERE t.query_sql_text LIKE N'%JOIN #HintOrders AS o%'
AND t.query_sql_text NOT LIKE N'%OPTION%');
IF @QueryID IS NULL
SELECT N'Query not captured yet' AS Result;
ELSE
BEGIN
EXEC sys.sp_query_store_set_hints @query_id=@QueryID,
@query_hints=N'OPTION(USE HINT(''DISABLE_OPTIMIZER_ROWGOAL''))';
SELECT query_id,query_hint_text,query_hint_failure_count
FROM sys.query_store_query_hints WHERE query_id=@QueryID;
END;Validate Application and Failure State
After attaching the Query Store hint, inspect the stored hint metadata and failure information exposed by the catalog. A requested hint is not proof that every execution used it successfully. Capture the resulting plan and runtime behavior through representative application requests.
Record the original problem, the baseline evidence, the tested values, and the reason the hint was chosen. Also record the conditions that should trigger a review, such as data growth, an index change, or an engine upgrade. A supported hint can become the wrong decision when those conditions change.
Do not use a hint to conceal a correctable schema or query problem indefinitely. Better estimates, appropriate indexing, or clearer predicates can remove the need for the intervention. Compare those alternatives when they are within scope. A scoped hint is valuable as a controlled response, while the durable design still deserves attention when the underlying cause remains present.
Remove the Hint When Its Reason Ends
For a source-code hint, remove the OPTION clause through the normal tested change. For a Query Store attachment, use sp_query_store_clear_hints for the reviewed query identity. Retest the unhinted behavior and keep the before-and-after evidence.
The removal block below finds the query carrying this hint and clears only that one. Verify the query identifier again because the operational record should identify exactly which decision is ending. Keep the removal separate from unrelated Query Store maintenance.
Check the next qualifying compilation and execution after removal. An old captured plan still records the earlier hint and does not describe the new test.
USE HINT is useful when it gives one statement a supported, evidence-backed planning adjustment. Discover the available names, compare each intervention separately, validate actual use, and keep a removal condition. The query should remain understandable to the next person who needs to explain why its optimizer behavior differs from the normal path.
DECLARE @QueryID bigint=
(SELECT MAX(query_id) FROM sys.query_store_query_hints
WHERE query_hint_text LIKE N'%DISABLE_OPTIMIZER_ROWGOAL%');
IF @QueryID IS NOT NULL
EXEC sys.sp_query_store_clear_hints @query_id=@QueryID;
SELECT COUNT(*) AS RemainingHints FROM sys.query_store_query_hints;Related reading on this blog: Disable Rowgoal Optimizer and Auditing Query Hints Left in Production Code.
An optimizer hint is not a permanent cure, it is a scoped decision that needs evidence and review.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
