The revealed wisdom in the SQL Server community has generally been that query hints should only be used as a last resort. For a complete novice of SQL Server, I would agree. But to call avoiding query hints a “best practise” is taking it too far.
For anyone with a little experience who knows what they are doing, I find that query hints are not only a good reactive solution, they are a proactive, design time, tool.
If I find a stored procedure or query that either
A) Gets run often… or
B) Is very important and needs to be consistent (“important” is nearly always the same as consistent)
Putting it in simple term: I don’t trust the optimiser with these types of queries. Mission critical queries are much too important to leave in the hands of an unpredictable process like a query optimiser.
Once I have seen a good plan for the query (and validated that I can’t make a better one myself) I force the query using hints. I don’t wait for it to go wrong, I do it when I see the query the first time (even if the plan is good). This has already saved our production systems from trouble and I am teaching this technique to my colleagues.
However, I do restrict myself to these hints:
|OPTION (<join type> JOIN)||Pretty safe, even under changing index conditions. In OLTP system, if OPTION (LOOP JOIN) cant give you a good plan, you are likely missing an index (and it will be obvious which one when you see the forced plan)|
|LEFT/INNER <join type> JOIN||Allows me to force ordering and really control execution to harvest filters in the right order.|
|FORCESCAN||The optimiser often gets this wrong for reporting queries. I find that it is a little too fond of seeking when a scan is better. Scans are often “safer” in the sense that you know they are proportional with the size of the table scanning. Seeks gone wrong (especially if they cause table spools) can really wreck havoc.|
|FORCESEEK||While this hint can be risky (because it assumes the existence of an index) – there are rare cases where the optimiser needs a bit of manhandling. This is a hint of last resort if the join strategies don’t give me enough|
|CROSS APPLY||Yes, I use this as a hint. If I know that a result will return a specific number of rows (and the optimiser does not) I can do a CROSS APPLY to SELECT TOP 1. A good example of this is BETWEEN joins on dates|
|OPTIMIZE FOR UNKNOWN||My favourite hint. Most of the times, I don’t want a plan to assuming anything about the boundary values of stats in another table (my fondness for GUID helps here as they are more likely to be nicely spread out). This hint is great for stability under changing conditions|
|MAXDOP||Very few queries (except those tuned by master Machanic) takes advantage of more than 8-16 cores.|
|OPTION (HASH GROUP)||Sometimes, the optimiser will trigger a massive sort before going into an aggregate operation. If I know that the aggregate will have a small result (often the case) I force the hashing strategy instead to avoid the excessive memory usage of the sort.|
Hints I avoid:
|Index hints||I don’t want queries to break if I changes indexes. In my entire career, I think I have found only 1-2 cases where I needed an index hint and forcing of join orders wasn’t good enough. Index hints make queries very brittle – which I suspect is why query hints in general have such a bad rap.|
|FORCE ORDER||If I force order, I also force the join strategy (as above). So I just haven’t found a need for this hint.|
|OPTIMISE FOR (@var = x)||Making something dependent on the contents of a column is dangerous. Data changes faster than code.However, Adam Machanic has some really cool ways to force some interesting side effects by using this hint together with a TOP (@var) (See his blog: http://sqlblog.com/blogs/adam_machanic/)|