First, let's look at the Microsoft documentation:
Hints are options or strategies specified for enforcement by the SQL Server query processor on SELECT, INSERT, UPDATE, or DELETE statements.
In simple words, it's a kind of traffic sign on the execution way. Hints are forcing the execution way of the processes. The query optimizer might NOT choose the best execution plan for the query.
Second, to clear up any confusion, I'm not against hints, but nonetheless, I oppose their usage. Mostly, because of the wrong usage of the hints.
It's a powerful tool, and you need a complete understanding of what its power is and what you want to achieve through its usage. If one of those is not absolute, it can be a disaster!
Now, imagine that you need to drive to some location. You entered your car, opened a navigation application, and started your ride. At some point, the navigator told you to turn right, but there is no turn there. Somebody makes a fake road, starts roadwork, or just sets a new sign. Now your app has confused. It's trying to fix your road by telling you to make a U-turn or taking you around an extra block. Time and resources are wasted...
Well, in this situation, the navigation app is the Query Optimizer.
The SQL Server Query Optimizer is a part of the engine that chooses execution methods for each process. The main goal of this component is to choose the lowest-cost plan. If it fails and chooses an imperfect plan, the process can take more resources or time for execution. In most cases, it happened, when the calculation faced something unknown. The same can happen when someone adds a hint that forces a different execution way.
Recently, I saw an environment with a lot of lock problems. The developers have used a lot of hints like ROWLOCK, NOLOCK, TABLOCK, and so on. They know the meaning of each of the hints. They believe that they are managing the transactions, but hints haven't worked.
I'll show you the mistake:
SELECT
Column1,
Column2,
...
ColumnX
FROM
dbo.Table NOLOCKGot it?
The word "NOLOCK" works as an alias here, not as a hint! This is how SQL Server actually sees this query:
SELECT
NOLOCK.Column1,
NOLOCK.Column2,
...
NOLOCK.ColumnX
FROM
dbo.Table NOLOCKNot as a hint, nop...
This is how it must be:
SELECT
T.Column1,
T.Column2,
...
T.ColumnX
FROM
dbo.Table AS T WITH (NOLOCK)In this way, it will work as expected!
Funny, right? Yes, but not really!
It was a kind of legacy and was applied to almost all processes. After we slowly and carefully removed these "hints", most of the problems were gone.
I don't mean to mock anyone in this article. My point is to explain the importance of a full understanding of what is being done. As I say at the beginning: It's a powerful tool, and you need a complete understanding of what its power is and what you want to achieve through its usage. If one of those is not absolute, it can be a disaster!