Edit

Share via

Configuring Query Hints for Optimizing SQL Server Performance with Business Central

  • Article

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 Business Central 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 want this hint to be used in SQL queries, then set DisableQueryHintOptimizeForUnknown to (false).		 No

For more information about configuring the Business Central Server instance, see Configuring Business Central 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.

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