Cool hints of requests — SQL Server documentation contains a pretty cool list of query hints: But what is the color box? Let’s see.
Since SQL Server query optimizer usually chooses the best query execution plan, we recommend using hints only as a last resort for experienced developers and database administrators.
I could be joking, but…every single thing in this warning is true. SQL Server does tend to choose the best execution plan for a query, about 99.999% of the time, and you are probably making a mistake if you use a query hint.
However, it should be said that if you are reading this blog, you are already more experienced than most SQL Server users. No, seriously, I know that you are doing well and that it is high time to think about using a few of them when the situation with setting up queries becomes really awful.
You can read more about each of them, including versions when they appeared, in the documentation on the queries hints.
OPTION (MIN_GRANT_PERCENT = 5) or OPTION (MAX_GRANT_PERCENT = 10) — when your query plans request too much (or not enough) memory and you have already tried to configure queries and indexes, this hint is the last useful tool.
OPTION (FORCE ORDER) — if you are struggling with a execution plan that doesn’t quite understand which table is the most selective or should be processed first, this quote forces SQL Server to process the tables in the order they are written in the query. This hit I like more than index hints, because it still gives SQL Server the freedom of action when choosing a method of access to each table.
OPTION (MAXDOP 0) — If you rely on a third party vendor server that requires you to set MAXDOP = 1, did you know that you can set MAXDOP higher for your own query using a hit? Okay, now you know: this request level hit rewrites the MAXDOP server installation. Perfect for building indices or tables for reports.
The documentation contains a lot more that you will definitely not use often — but today seems like a good day to come back and see what new options are available for the version of SQL Server you are using.