Everybody does it, but nobody admits it. Forceseek is a forbidden word in the DBA world.

In the world of experts, query hints should be the last resort for fixing performance issues. And is true, with a good data model, good code and good maintenance, the query optimizer should come up with a very good plan. But good data models are hard to come by in real life, good code gets bad in time and maintenance is but a dream when the databases become very large.

As long as plans are understood, then fixing using hints is totally acceptable. Coming up with empirical fixes using hints however, might not always lead to the right results. Not immediate, but in the long run issues might arise.

I have to admit I am a heavy user of hints. I find them very useful and there are many times where I put them in my code from the beginning. In general in most of the cases the developers know the data distribution. In that case they can use the hints to model the plan from the start.

Most of the code I deal with is executed hundreds or even thousands times per second. In these cases, having stable plans is extremely important. These are the situations where well placed hints shine. I had the chance to observe a number of times situations where one small plan change created very big issues. Many of these situations are impossible to debug and fix.

However every situation is different and in some other type of workloads, the query optimizer shines. By using hints, we take the ability to adapt. This might also develop into problems given time.

In the end it comes down to experience to choose the correct path.