Performance of Business Central On-Premises Installations

Article
06/23/2022

In this section, we highlight many resources that might be useful when doing performance investigations and tuning of on-premises installations. On-premises, in this context, means deployment to any environment that isn't the Business Central service. Running Business Central on Azure resources is also considered on-premises.

Content is ordered into the following groups:

Tuning the technology stack
Scaling Business Central
Measure and monitor performance

Performance tuning the technology stack

A Business Central installation typically consists of the following components, which can be tuned to improve performance:

Client
Web Server
Server (service-tier)
Database

Client

There are three things of importance when dealing with client performance:

Hardware of the computer running the client
Choice of browser
Network bandwidth and latency between the client and the data center running Business Central

These subjects are described in Performance Tips for Business Users.

Web Server

You can improve web server performance by configuring Kernel-mode authentication:

Configuring Kernel Mode Authentication on the Business Central Web Server

Business Central Server (service-tier)

You can adjust the following Business Central Server settings related to database performance.

Server setting	Description	Read more
BufferedInsertEnabled	Disabling bulk inserts can be helpful when you're troubleshooting failures that occur when inserting records.	Disabling Bulk Inserts
DisableQueryHintForceOrder	Check if this setting is set to true.	Configuring Query Hints for Optimizing SQL Server Performance with Business Central
DisableQueryHintLoopJoin	Check if this setting is set to true.	Configuring Query Hints for Optimizing SQL Server Performance with Business Central
DisableSmartSql	If the performance of loading a page that contains FlowFields in Business Central is bad, you might want to try isolating and testing FlowField queries separately.	Troubleshooting: Long Running SQL Queries Involving FlowFields by Disabling SmartSQL
EnableCloudReplicationMaintenance	Specifies whether to keep the cloud replication status in the database up to date. When enabled, synchronize operations on tenants and extensions will update records in the Intelligent Cloud Status table, and set change tracking on tables that are configured to replicate data. Enabling this setting isn't required for migrating most on-premises solutions to the cloud, and you'll improve synchronization and upgrade performance by disabling it.	Configuring Business Central Server - EnableCloudReplicationMaintenance
EnableProfileCacheSynchronization	Specifies whether profile cache synchronization across multiple server instances is enabled. However, enabling this setting may lower the tenant performance.	Configuring Business Central Server - EnableProfileCacheSynchronization
SqlBulkImportBatchSize	Specifies how many SQL memory chunks that a data import must be distributed across. Lowering the value increases the number of network transfers and decreases performance. But it also lowers the amount of memory that the server instance consumes.	Database Settings
SessionEventTableRetainInterval NonInteractiveSessionsLogRetainInterval SessionEventTablePurgeLookupPeriod	The system table 2000000111 Session Event stores information about sessions between Business Central Server instances and clients. Entries are recorded for various events, for example, when a user signs in or out of the client, or when a web service request starts or stops. It's a good idea to limit the number of entries in this table because performance can be adversely affected as the table size grows. Under normal conditions, you shouldn't experience any problems. But there might be situations or periods, typically dealing with web service calls, during which a high number of session events occur. These three settings enable you to control the table size by specifying how long to keep entries in the table before they're automatically deleted.	Configuring Business Central Server - Session Event Table

Web service limits

You can adjust server instance settings related to web service calls to implement resource governance (and avoid resource starvation on the server instances) here:

Task Scheduler

You can adjust server instance settings related to the task scheduler to implement resource governance (and avoid resource starvation on the server instances) here:

Maximum Concurrent Running Tasks

Database (SQL Server or Azure SQL database)

First of all, make sure that you avoid common pitfalls in your SQL Server setup:

Tune data access

These articles describe how you can use SQL Server table partitioning and data compression to get faster data retrieval:

SQL Server vs. Azure SQL database

Read more about the difference between SQL Server and Azure SQL database performance:

Differences between Azure SQL database and SQL Server

Using Read-Scale Out

If you run the Business Central database in a High Availability architecture, you can use the built-in Read Scale-Out feature in Azure SQL Database or SQL Server to load-balance read-only workloads. Read Scale-Out uses read-only replicas instead of sharing the primary database. This way, read-only workloads (reports, queries, API pages, and OData GET requests) will be isolated from the main read-write workload (codeunits). They won't affect the performance of business processes.

For more information, see Using Read Scale-Out for Better Performance.

Troubleshooting database performance issues

These articles can be useful when troubleshooting database performance issues:

Performance of BACPAC generation

The sqlpackage is the command-line tool used to generate BACPAC/DACPAC files. The February 2019 update of sqlpackage solved a significant schema-compare performance issue that occurred when generating scripts. Make sure you use version 18.1 or later if you experience issues in BACPAC generation performance.

You can also limit the amount of SQL schema to restore from a BACPAC. On tables used only with temporary record variables and pages, set the TableType property to Temporary.

Performance impact on setting up CDC on SQL Server

There's a performance impact if you set up CDC on the database. SQL Server will be slower (depending on the retention period used). You'll also need storage for the extra data.

Performance impact of enabling Transparent Data Encryption (TDE)

Enabling Transparent Data Encryption (TDE) has a slight performance degradation on SQL Server because it needs CPU resources for the encryption/decryption of data.

Scaling Business Central

On compute (Business Central Server and Web servers), it's possible to scale horizontally by separating Business Central Server instances and Web servers on different nodes. For more information, see Configuring Business Central Server.

It's also common on larger installations to separate traffic based on client type (direct UI and OData traffic to different Business Central Server instances); possibly cohosting Business Central Server instances and Web servers on the same nodes.

The Business Central Server has a built-in thread dispatcher for AL execution. If you have more cores available, then the dispatcher allows for more parallel execution. But keep in mind that AL execution is single-threaded until the Business Central 2019 release wave 2 (when we introduced async processing). For long running operations, such as heavy reports, using faster CPUs will give better performance.

On the database side, make sure SQL Server has enough resources for sessions (both CPU and memory) and try to optimize the setup of SQL Server to Business Central. For more information, see Installation Considerations for Microsoft SQL Server and Business Central.

Measure and Monitor performance

The following resources describe ways that you measure and monitor performance in your Business Central on-premises installation: