The SQL Server Query Optimizer does an amazing job of execution plan generation. Most of the time, for most of the queries, it generates an optimal execution plan. And this is not easy at all. There is a lot of potential to get a suboptimal plan: wrong server configuration, poorly designed databases, missing and suboptimal indexes, suboptimal written queries, nonscalable solutions, and more. And the Query Optimizer should work for all those workloads, all over the world, all the time.
Depending on data constellation, Query optimizer generates suboptimal execution plans sometimes. If the execution of the queries is very important from a business point of view, you have to do something to try to achieve at least an acceptable execution plan. One of the weapons you have for this is hints to the Query Optimizer. With hints, which are actually instructions, you instruct the Query Optimizer on how to generate the execution plan. You take responsibility or you take part of the responsibility for the execution plan generation.
There are three types of hints: table, join, and query hints. You use them if you cannot enforce the required execution plan with other actions. Hints should be considered a last resort in query tuning. You should use them if you don't know another way to get a desired plan, when you have tried all that you know, or when you are under time pressure and have to fix a query as soon as possible. For instance, if you suddenly encounter a problem in the production system on the weekend or during the night, you can use the hint as a temporary solution or a workaround and then look for a definitive solution without time or operative pressures. You can use query hints if you know what you are doing. However, you should not forget that the hint and the plan remain forever and you need to periodically evaluate whether the plan is still adequate. In most cases, developers forget about plans and hints as soon as the performance problem is gone.
SQL Server 2016 brings three new query hints to address problems related to memory grants and performance spools:
- NO_PERFORMANCE_SPOOL
- MAX_GRANT_PERCENT
- MIN_GRANT_PERCENT