Why I Avoid Using Hints in my Database Queries

For those who have watched my Pluralsight courses on either Oracle or SQL Server performance tuning, you will notice that I don't talk about database hints in either course.  This is intentional on my part.  I tend not to use hints in any of my production SQL statements and overall I only use hints in very limited cases.

I made the decision not to cover hints in my courses because too often times, I have seen hints used incorrectly, often by someone who didn't understand what the hint was doing.  I've seen cases where someone read about hints "on the Internet" and thought by dropping a hint into their SQL statement, it would act as some sort of magical performance booster for their statement.  Unfortunately, there is no magic going on, and like any technology or technique, the result can actually be a worse outcome if you don't understand what is going on.

So lets explain what a little bit about hints.

The Query Optimization Process
When you submit a SQL statement to any database, a piece of software called the Query Optimizer parses the SQL statement and determines the fastest way to process that statement.  It is the query optimizer that determines if an index can be used or a table scan should be performed.  It also determines how to perform joins between tables and in what order the tables should be joined if you have multiple join conditions.

To determine the most efficient way to process your statement, the optimizer looks at the statistics of the tables involved in the statement, including the total number of rows and the distribution of those rows in the table.  It also looks at the indexes on the table and the number of unique keys in the index and matches all of this data up with the where clauses and join criteria you have specified in your statement.  Using these statistics, the optimizer can estimate the cost of all of the different ways to perform the SQL statement, and it will pick the lowest cost combination of these operations.

All of this usually happens in 100 milliseconds or less.  And optimizers today are really, really good at picking the right execution plan that will result in the fastest way to execute your statement.

What Does a Hint Do
When you supply a database hint, you are taking control of how the statement will execute and taking this control away from the query optimizer.  The problem is that the best way to execute a statement may change over time.  Maybe a table gets more data or the distribution of the data in a table changes.  Under normal circumstances, the query optimizer can adjust to these changes and come up with a new plan which is most efficient for the current data set.

When you provide a hint, you are taking this flexibility away from the optimizer.  So now as things change in your database, the optimizer cannot adjust.  So now, you have a sub-optimal plan, and many times that plan is much less efficient than the plan the query optimizer would have come up with on its own.  The problem is that while you can figure out the right hint for the way the data looks today, you have no way of knowing what the data will look like tomorrow.  Yet by providing a hint, you are really committing to a specific execution plan, even if that plan is wildly inefficient for tomorrows problem.

Is There Ever a Time to Use Hints
Sometimes I am surprised when I get a certain execution plan.  For example, I may have expected the optimizer to use a different index than what it did.  So in my SQL editor, I'll use a hint to look at that different version of the plan.  In almost every case, the plan produced by the optimizer is less expensive than the plan with the hint.  Its not that I don't trust the optimizer, but sometimes being able to contrast the two execution plans helps me understand why the optimizer is making the decisions it is.

The other time to use a hint would be if you were instructed to do so by Oracle or Microsoft technical support.  I've never had to do this, and I'm guessing these instances are very few and very far between.

Summary
I really feel like it is best to avoid using hints in any production SQL statements your application might run.  At first, they may seem magical.  But the optimizers included in today's database products are really, really good.  When you start including hints in your statement, you are taking control away from the optimizer and its ability to find the best plan for the current state of the table.  This almost always results in a less efficient plan and therefore slower running SQL statement.  So I encourage you to avoid using hints in any production SQL you might run.