A query hint is a directive you add to your SQL statement that tells the database optimizer how to execute that query. You’re basically overriding the optimizer’s judgment with your own instructions.
Most of the time, your database’s query optimizer does a pretty solid job figuring out the best execution plan. It analyzes statistics, indexes, and table structures to determine the most efficient path. But sometimes you know better (or at least you think you do) and that’s where query hints can be useful.
Why Would You Use Query Hints?
While the optimizer is usually pretty good, it isn’t perfect. Sometimes it chooses a lousy execution plan. This could be because statistics are outdated, the data distribution is unusual, or simply that the query is complex enough to confuse it. In these cases, a hint can force the optimizer to use a specific index, join method, or execution strategy that you know will perform better.
That said, hints should be your last resort. They’re a bit like hardcoding values in your application – convenient in the moment, but potentially problematic down the road. If your data changes significantly, that hint you added six months ago might actually make things worse.
Common Types of Query Hints
Different database systems have different hint syntaxes, but the concepts are pretty universal. Here are some common categories:
- Join hints tell the optimizer which join algorithm to use (nested loops, hash joins, merge joins).
- Index hints force the use of a specific index or tell the optimizer to avoid one.
- Locking hints control how rows are locked during the query.
- Optimization hints might set a timeout for query optimization or suggest parallel execution.
Example of Using a Query Hint
Say you’re running an internal analytics platform that uses SQL Server to track application deployments across different cloud regions. And let’s say you have a pesky query that never seems to use the index that you created specifically for it. The query optimizer stubbornly chooses a table scan every time, and you’re convinced that an index seek would be quicker.
In this case, you can use a query hint to explicitly tell the optimizer to use the index.
Let’s set up some data for this scenario:
CREATE TABLE Deployments (
DeploymentID INT PRIMARY KEY,
ApplicationName VARCHAR(100),
Region VARCHAR(50),
DeploymentDate DATETIME,
Status VARCHAR(20),
DurationMinutes INT
);
CREATE INDEX IX_Region_Date ON Deployments(Region, DeploymentDate);
-- Insert sample data
INSERT INTO Deployments VALUES
(1, 'PaymentProcessor', 'us-east-1', '2025-11-15 08:30', 'Success', 12),
(2, 'InventoryAPI', 'eu-west-2', '2025-11-15 09:15', 'Success', 8),
(3, 'PaymentProcessor', 'ap-south-1', '2025-11-16 14:20', 'Failed', 45),
(4, 'UserAuthService', 'us-east-1', '2025-11-17 11:00', 'Success', 6),
(5, 'InventoryAPI', 'us-east-1', '2025-11-18 16:45', 'Success', 7),
(6, 'PaymentProcessor', 'eu-west-2', '2025-11-19 10:30', 'Success', 15),
(7, 'InventoryAPI', 'ap-south-1', '2025-11-20 13:10', 'Success', 9),
(8, 'UserAuthService', 'us-east-1', '2025-11-21 08:00', 'Success', 5);OK done.
Now, your pesky query is below. It looks for all deployments in a specific region within a date range. But it never seems to use the index you created for it. You can check this by enabling SET SHOWPLAN_TEXT:
SET SHOWPLAN_TEXT ON;
GO
SELECT ApplicationName, DeploymentDate, Status, DurationMinutes
FROM Deployments
WHERE Region = 'us-east-1'
AND DeploymentDate >= '2025-11-01'
AND DeploymentDate < '2025-12-01';
GO
SET SHOWPLAN_TEXT OFF;
GOJust to be clear, this is the query without a query hint.
And here’s the estimated query plan:
StmtText
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|--Clustered Index Scan(OBJECT:([db_446enhsz6_446hppnec].[dbo].[Deployments].[PK__Deployme__5EF8D7174E961EA2]), WHERE:([db_446enhsz6_446hppnec].[dbo].[Deployments].[DeploymentDate]>='2025-11-01 00:00:00.000' AND [db_446enhsz6_446hppnec].[dbo].[Deployments].[DeploymentDate]<'2025-12-01 00:00:00.000' AND [db_446enhsz6_446hppnec].[dbo].[Deployments].[Region]='us-east-1'))
What you’re seeing there is a clustered index scan. This is basically a full table scan through the primary key. The optimizer is reading through every row in the table and filtering as it goes. This could end up being quite inefficient when you have a perfectly good nonclustered index (IX_Region_Date) that could handle this query much better.
I should point out that in this case, we have a very small table, and so the optimizer has determined that it’s quicker to just scan the table. And it’s probably right. However, let’s say you have more data and you really think the optimizer got it wrong.
In this case you can use a hint to force it to use the index:
SET SHOWPLAN_TEXT ON;
GO
SELECT ApplicationName, DeploymentDate, Status, DurationMinutes
FROM Deployments WITH (INDEX(IX_Region_Date))
WHERE Region = 'us-east-1'
AND DeploymentDate >= '2025-11-01'
AND DeploymentDate < '2025-12-01';
GO
SET SHOWPLAN_TEXT OFF;
GOOutput:
StmtText
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|--Nested Loops(Inner Join, OUTER REFERENCES:([db_446enhjzv_446hprqew].[dbo].[Deployments].[DeploymentID]))
|--Index Seek(OBJECT:([db_446enhjzv_446hprqew].[dbo].[Deployments].[IX_Region_Date]), SEEK:([db_446enhjzv_446hprqew].[dbo].[Deployments].[Region]='us-east-1' AND [db_446enhjzv_446hprqew].[dbo].[Deployments].[DeploymentDate] >= '2025-11-01 00:00:00.000' AND [db_446enhjzv_446hprqew].[dbo].[Deployments].[DeploymentDate] < '2025-12-01 00:00:00.000') ORDERED FORWARD)
|--Clustered Index Seek(OBJECT:([db_446enhjzv_446hprqew].[dbo].[Deployments].[PK__Deployme__5EF8D7177AA52ACF]), SEEK:([db_446enhjzv_446hprqew].[dbo].[Deployments].[DeploymentID]=[db_446enhjzv_446hprqew].[dbo].[Deployments].[DeploymentID]) LOOKUP ORDERED FORWARD)
The query hint asked the optimizer to use the index, and the estimated query plan shows that it did just that.
Instead of the clustered index scan from before, we now see an index seek on IX_Region_Date that efficiently finds only the rows matching our region and date range. The nested loops join then performs key lookups against the clustered index to retrieve the additional columns (ApplicationName, Status, DurationMinutes) that aren’t included in the nonclustered index.
For our tiny 8-row table, the original table scan was probably more efficient. After all, the optimizer isn’t stupid and there’s usually a reason it chooses one execution plan over another. But this hint-driven approach might be what you’d want as the table grows to thousands or millions of rows, where scanning everything becomes prohibitively expensive and the seek-plus-lookup approach pays off.
Another Hint Approach
Another way to deal with the above problem is to use the WITH (FORCESEEK) query hint. Instead of specifying the index, we specify that we want the optimzer to perform a seek.
SET SHOWPLAN_TEXT ON;
GO
SELECT ApplicationName, DeploymentDate, Status, DurationMinutes
FROM Deployments WITH (FORCESEEK)
WHERE Region = 'us-east-1'
AND DeploymentDate >= '2025-11-01'
AND DeploymentDate < '2025-12-01';
GO
SET SHOWPLAN_TEXT OFF;
GOOutput:
StmtText
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|--Nested Loops(Inner Join, OUTER REFERENCES:([db_446enhfjt_446hr3muu].[dbo].[Deployments].[DeploymentID]))
|--Index Seek(OBJECT:([db_446enhfjt_446hr3muu].[dbo].[Deployments].[IX_Region_Date]), SEEK:([db_446enhfjt_446hr3muu].[dbo].[Deployments].[Region]='us-east-1' AND [db_446enhfjt_446hr3muu].[dbo].[Deployments].[DeploymentDate] >= '2025-11-01 00:00:00.000' AND [db_446enhfjt_446hr3muu].[dbo].[Deployments].[DeploymentDate] < '2025-12-01 00:00:00.000') ORDERED FORWARD)
|--Clustered Index Seek(OBJECT:([db_446enhfjt_446hr3muu].[dbo].[Deployments].[PK__Deployme__5EF8D717D8153465]), SEEK:([db_446enhfjt_446hr3muu].[dbo].[Deployments].[DeploymentID]=[db_446enhfjt_446hr3muu].[dbo].[Deployments].[DeploymentID]) LOOKUP ORDERED FORWARD)
While this approach doesn’t specify which index to use, it does tell the optimizer to perform a seek operation.
However, be careful here. If there’s no index that supports a seek operation for the query, you’ll get an error. Here’s what happens if I drop the index and run the FORCESEEK query again:
Msg 8622, Level 16, State 1, Server a37048bc31a0, Line 2
Query processor could not produce a query plan because of the hints defined in this query. Resubmit the query without specifying any hints and without using SET FORCEPLAN.
When Query Hints Cause Problems
Query hints can backfire in several ways, and it’s worth understanding the risks before you start sprinkling them throughout your code.
Here are some of the potential issues that can occur when you start using query hints:
- The query fails entirely. If you use the wrong hint, you could end up with an error instead of query results. We saw an example of this with
FORCESEEKabove. When I dropped the index, there was no longer an index that supported a seek operation for the query. Other scenarios where query hints cause the whole query to fail could include specifying a non-existent index or specifying contradictory query hints. - Your data changes but your hint doesn’t. That index hint you added when the table had 1,000 rows might be terrible when it grows to 10 million rows, or when the data distribution shifts. Maybe filtering by region was highly selective before, but now 80% of your deployments are in us-east-1. The optimizer would adapt to this change automatically, but your hint locks it into the old strategy.
- Index maintenance becomes a nightmare. If you drop or rename an index that’s referenced in a hint, every query using that hint breaks. You can’t refactor your indexing strategy without hunting down all the hardcoded index names in your application code.
- Hints don’t always transfer between environments. Production might have different indexes than your development database, or different data volumes that make the hint helpful in one place and harmful in another. That hint that fixes a problem in prod might actually slow things down in dev.
- The optimizer gets smarter, but you’re stuck. Database engines improve with every version. A hint you added to work around an optimizer bug in SQL Server 2016 might be completely unnecessary (and even counterproductive) in SQL Server 2022 or SQL Server 2025. But if you don’t remember to revisit it, you’re stuck with suboptimal performance.
Despite these potential issues, query hints can have their place. They can be very useful when you need them, but treat them as temporary solutions that require documentation and periodic review.
When to Actually Use Them
We’ve seen how query hints can be used to help us direct the query optimizer to produce the most optimal query plan, but we’ve also seen how they can easily cause problems. The best course of action would be to use query hints only when you’ve exhausted other options.
First, make sure your statistics are up to date. Check that you have appropriate indexes. Look at the execution plan to understand what the optimizer is actually doing. Sometimes adding a missing index or rewriting the query slightly solves the problem without any hints at all.
But if you’ve done all that and the optimizer is still making a clearly suboptimal choice? Then sure, add a hint. Just ensure the hint is thoroughly documented, explaining the rationale for its inclusion to support long-term code maintenance.