Configuring Query Hints for Optimizing SQL Server Performance with Dynamics NAV

On its own, SQL Server query optimizer will try to select the best execution plan for queries. Most of the time, query optimizer makes the right choice. Query hints are strategies that can be enforced by the SQL Server query processor to override any execution plan that the query optimizer might select for a query. The Microsoft Dynamics NAV Server instance includes configuration settings that let you enable or disable the use of the following hints on queries in the database:

Hint	Description	Used by default
FORCE ORDER	Instructs the query optimizer to preserve the join order that is indicated by the query syntax.<	No
LOOP JOIN	Instructs the query optimizer to use LOOP JOIN for all join operations in the whole query.	No
OPTIMIZE FOR UNKNOWN	Instructs the query optimizer to use statistical data instead of the initial values for all local variables when the query is compiled and optimized, including parameters created with forced parameterization. If you clear the check box (false), the OPTIMIZE FOR UNKNOWN hint is used in queries.	Yes

For more information about configuring the Microsoft Dynamics NAV Server instance, see Configuring Dynamics NAV Server.

Important

We recommend that you do not change the query hint settings from the default values unless you are certain that the change will better suit your scenario.

See Also

Optimizing SQL Server Performance with Business Central
Installation Considerations for Microsoft SQL Server
Microsoft SQL Server documentation