Published 20 November 2017
QUERY HINT is used
It is not recommended to use query hints.
Query hints take control of an entire query and can affect all operators within the execution plan. We can use query hints to force the use of a specific operator for all aggregations in a query, or for all joins. We can use them to instruct the optimizer to optimize a query for a defined parameter value, or to compile a new plan on every execution of that query, to control use of parallelism for that query, and more.
Some query hints are useful occasionally, while a few are for rare circumstances. As with all hints, injudicious use of query hints can cause you more performance problems than they solve! They should be used only as a last resort by experienced developers and database administrators, and only if you are certain of the consequences for performance and scalability. You will also need a maintenance plan to identify and re-test all hints in response to changes in the data distribution, or an upgrade to a new SQL Server version, or application of a new service pack.
For more information on the dangers of using hints, see SQL Prompt Code Analysis: A Hint is Used (PE004-7).