Query Hints
SQL Server has a couple of keywords that can be added to a query to give the query optimizer some ideas on how to best compile your SQL statements. Hints like FORCE ORDER, MAXDOP and NOEXPAND can be used as a way to tell the optimizer “Hey, I’ve got this idea, you should try it.”
And while these Query Hints can certainly help in some scenarios, I think it should be the exception rather than the rule. If you are using Query Hints on a large number of statements to fix performance problems, then you probably have a bigger problem underlying your performance issues.
The problem I have with Query Hints, is that the name is misleading. When you provide a Query Hint, you are not providing a suggestion like “Hey SQL, I’ve got an idea, can you think about it and let me know?” It’s not like a missing index request where SQL asks you to consider a change. Query Hints are actually Query Commands – SQL will do exactly what you say, regardless of the consequences.
Query Hints Commands let us boss SQL Server around and tell it how to do it’s job. When you’re the expert in a subject matter, you don’t like your boss coming over to tell you how best to do it. If you’re the expert then you should be able to work out how best to do it. There are times when the boss should step in because they know more about a situation than you do, or they can see a better solution that you can, but you are hopefully only doing that when you know that you’re right and you are preventing a bad decision.
You have employed SQL Server to do a job for you, so when you pass in a Query Hint Command, you better be very sure that you know better than SQL for every different way that this query is called. Are you sure you don’t want SQL to expand that view? Are you sure you about changing the MAXDOP? Are you sure about the Join Order that you’ve put in? Are you sure enough that the data won’t change in the next x number of years? Are you sure that the parameters you’ve passed in to the Query represent all of the ways users are going to query the data? Because if you’re not, and if you’re not actively monitoring it, then there is a really good chance that your Query Hints Commands won’t be right all of the time, and in fact could be terribly wrong some of the time.
I’m not saying that you can never use Query Hints Commands, they can be a great tool, but whenever I use it, I want to ask myself why is it that I know more about the data than the database? And that should lead in to the next question, how can I tell the database more about the data so it picks the right plan?