Performance of Business Central On-Premises Installations
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:
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:
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:
Database (SQL Server or Azure SQL database)
First of all, make sure that you avoid common pitfalls in your SQL Server setup:
- Installation Considerations for Microsoft SQL Server and Business Central
- Setting SQL Compatibility Level to Optimize Database Performance
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:
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:
- Troubleshooting: Long Running SQL Queries Involving FlowFields by Disabling SmartSQL
- Configuring Query Hints for Optimizing SQL Server Performance with Business Central
- Troubleshooting: Using Query Store to Monitor Query Performance in Business Central
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:
- Monitoring Business Central Server Events
- Tools for Monitoring Performance Counters and Events
- Troubleshooting: Using the Event Viewer to Monitor Long Running SQL Queries in Business Central
- Troubleshooting: Using Query Store to Monitor Query Performance in Business Central
See Also
Performance Overview
Performance Topics For Developers
Performance tips for business users
How Application Configurations Affect Performance
Performance Online
How to Work with a Performance Problem