Configuring query hints for optimizing SQL Server performance

Important

This content is archived and is not being updated. For the latest documentation, see Microsoft Dynamics 365 product documentation. For the latest release plans, see Dynamics 365 and Microsoft Power Platform release plans.

Note

These release notes describe functionality that may not have been released yet. To see when this functionality is planned to release, please review What's new and planned for Dynamics 365 Business Central. Delivery timelines and projected functionality may change or may not ship (see Microsoft policy).

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 the following configuration settings that let you enable or disable the use of 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.	Yes

We have made the use of SQL Server query hints in the execution plans of Business Central query objects.

These features have also been made available in Dynamics NAV 2017 and 2018.

Tell us what you think

Help us improve Dynamics 365 Business Central by discussing ideas, providing suggestions, and giving feedback. Use the Business Central forum at https://aka.ms/businesscentralideas.