Hints aren’t hints; they are directives.
Don’t think of a hint as some way of encouraging the optimizer to do something, it is a demand to do something. Generally, hints are only “ignored” when you miss spell it or put in something that can’t be done. For example, have the FULL hint and an INDEX hint on the same table. There are also cases when optimizing will transforms a query in such a way that hints in effect disappear from the final query that the optimizer really uses. This isn’t common in most shops but can happen.
Hints are best used rarely and must be documented when used.
If you find yourself “always” using a hint, then you have a problem. Two hints I’ve seen used “out of habit” are the NO_MERGE and MATERILZE hints. Just because you have sub-query doesn’t mean you need the NO_MERGE. It might not be getting merge anyway and maybe it’s a good idea if it is. The same basic issues are true with MATERLIZE on CTEs (Common Table Expressions or Sub-Query Factors). The MATERLIZE hint tells the optimizer to resolve it as a separate query, and turn the results into a temporary table like structure. And often this is exactly what we want to have happen with a CTE. But the same can be true of a CTE, it sometimes wouldn’t be merged into the main query and even if it is, sometimes this is a good thing.
If your testing validates that the hint helps the optimizer do the right thing and you can’t get it any other way, document it’s use. This documentation, doesn’t have to be elaborate and is really for you. Always document the code as if you will be working on this code again in two years and have no recollection of working on this code before. Help yourself understand what you did today.
Hints can be a fast fix for a bug.
I know this may be a shock, but there are bugs in the optimizer. This is where the use of a hint might be a better solution then some elaborate coding trick. Cardinality can be an issue here, and the CARDINALITY hint can help. And CTEs again can be a problem. If the cardinality estimate is way off for a CTE and that CTE is used many times in the main query, then the odds of the main query having a good plan are slim.
If you can’t rework the code of the CTE to get a better estimate then a CARINAILTY hint can really save the day. BUT this is a hint that could go bad over time. Always put in a comment as to when this was used and that it will need to be checked in the future. There may come a time it wouldn’t be needed or that the value used needs to be updated. Keep in mind that the number needs to be in the correct magnitude, not exactly right.
Hint should be viewed as a temporary fix and not the final solution.
All this leads up to this, using hints are temporary fixes to a problem. Often, we are tuning around bugs or poor coding. If you don’t have access to the author of the query then significate rewriting of the code can be difficult or impossible. Using a few well-placed hints can get the code to at least run in an acceptable amount of time. This can at least get thru an “emergency” and hopefully there will come a time when the code can be rewritten or the bug fix is applied and the hints are no longer needed.
