How Application Configurations Affect Performance

Article
11/13/2023

The sections in this article are tips and tricks on how to set up Business Central for performance and also describe how in-product configurations affect the performance of Business Central.

Uninstall extensions that you don't use

Any extensions that you install can affect the overall system performance. So if you've installed an app from AppSource, but later discover it's not needed, then uninstall it. The same advice applies to the extension that comes preinstalled in an environment. For example, uninstall all migration extensions after you've migrated data, or if you don't intend to migrate data.

Run things in the background

It's often desirable to offload work from the user session to happen in the background. Examples are:

Schedule long running reports to run in background
Schedule jobs (for example posting) to run in background
Enable background posting in areas where your business is using reservations and item tracking using serial and lot numbers
Adjust item costs as a periodic background job. Don't adjust automatically.

Job queues and performance

Scheduled tasks or job queue entries that are set to run on a recurring schedule can impact the performance of Business Central under either of the following conditions:

They run too frequently.

Consider how often the task or job needs to run. Especially for polling scenarios, you might have better and more performant ways to react, such as using webhooks.
They run heavy jobs while many users are also using Business Central.

Consider running them outside working hours. This might decrease locking and deadlock issues both for users and for the task or job itself.

Avoid locking

When the Business Central database needs to have exclusive access to a table or a data row, it will issue a lock. If another session needs to access a locked resource, it needs to wait until the session holding the lock is finished with its work. There are a few places in the Business Central application where you can reduce the risk of locking.

Use number series that allow gaps

Number series in Business Central are shared resources that sometimes cause locking issues. Not all records that you create in Business Central are financial transactions that must use sequential numbering. Customer cards, sales quotes, and warehouse activities are examples of records that are assigned a number from a number series. They aren't subject to financial auditing and can be deleted. For all such number series, consider using number series that allow gaps to avoid locking issues. For more information, see Gaps in Number Series.

Don't adjust cost item entries with too high a frequency

All sales transactions have to get their cost calculated at some point—either at the time they're posted or batched up for later, like nightly or weekly, where all sales transactions that haven’t had their cost calculated yet are "adjusted". The main reason for postponing this operation to off-hours is that it locks many tables while running for a long time. A good frequency to start with could be to do it nightly and then evaluate if it needs to be adjusted to happen more or less frequently.

Be cautious with the Rename/Copy company operations

The Rename company and Copy company operations aren't intended to run while business transactions are being applied to Business Central. First, the operations are likely to induce locks on the tables that data is copied from. These locks will block users from transacting in the company. Second, the operations use resources on the database, which can in turn cause resource starvation for users working in other companies.

If you must do a Rename/Copy company operation, it's highly recommended to do it outside working hours. Turn off scheduled jobs to avoid locking issues.

The Copy Company operation also has many long term effects including the following:

Increased database size
Upgrade operations take longer
Larger .bacpac files when requesting backups from the Business Central administration center, which also means that export/import operations involving .bacpac files take longer.

Note

For Business Central online, the Rename company operation is no longer supported. Instead, you can change a company's display name.

Periodic activities that maintain performance

Block inactive customers, vendors, or items to improve filtering and searching on document data entry

Performance effect of enabling integration on a table

There's a performance overhead involved in enabling integration on an entity such as Customer or Contact. Only enable integration if you intend to integrate with Dynamics 365 Sales. Enable it only on the required entities.

For more information, see Synchronizing Data in Business Central and Dynamics 365 Sales.

Performance effect of enabling change logging on a table

There's a performance overhead involved in having change log enabled on a table. First, there are extra writes to the database when inserting, modifying, or deleting data. Each of these operations triggers an insert statement per field that's enabled change logging. Second, the size of the change log table can impact the performance of admin operations such as copy company, export, and backup/restore of environments.

It's recommended to critically evaluate what fields should be tracked in the change log and also to do extra testing with realistic data before rolling out configuration changes to the setup of the change log.

For more information, see Change log.

Functionality with known performance impact

These areas of the application are known to cause a performance impact and require extra testing with realistic data setup before they're rolled out.

If processing of Sales Order lines is slow

If you experience that processing of Sales Order lines that contain bill-of-materials (BOMs) is slow, then check if Stockout Warning on the page Sales & Receivables Setup, is set to true. If that is the case, then change the value to false.

Why? Stockout Warning specifies if a warning should be displayed if a user enters a quantity on a sales document that brings the item’s inventory below zero. The calculation includes all sales document lines that havnn't yet been posted. Stockout Warning can still be used on items; by setting the individual Item’s Stockout Warning to true on the Item Card.

Manage the database access intent on reports, API pages, and queries

Business Central supports the Read Scale-Out feature in Azure SQL Database and SQL Server to load-balance analytical workloads. Read Scale-Out is built in to Business Central online, but it can also be enabled for on-premises.

Read Scale-Out applies to queries, reports, or API pages. With these objects, instead of sharing the primary, they can be set up to run against a read-only replica. This setup essentially isolates them from the main read-write workload. This way, they won't affect the performance of business processes.

A drawback of reading from a replica is that it introduces a slight delay compared to reading from the primary database. Read Scale-Out is controlled by the DataAccessControl property on objects. This property determines whether to use a replica if one is available. If this delay isn't acceptable for an object, you can overwrite the default database access intent from the UI. For more information, see Managing Database Access Intent

Number of companies

Having many companies can cause administrative tasks, like upgrades, point-in-time restores, and database exports, to take a long time and potentially hit timeout values. If you have more than 50 companies, we recommend that you test these operations and typical usage scenarios extensively. Delete companies that are no longer needed.

Don't do these things

Finally, make sure that you don't repeat these performance mistakes that we have seen cause massive performance issues for customers:

Don't adjust cost item entries with a high frequency.
Don't set up a change log for everything. For more information, see Auditing Changes in Business Central.
Don't run job queues too frequently.
Don't adjust item costs automatically if you have many item entries. Run in the background instead.
Don't postpone setting up global dimensions, because it can be a heavy operation when you have much data. Set up correct global dimensions to avoid changing them later on.
Don't run the Copy company operation during business hours.
Don't apply large configuration packages during business hours. See also Prepare a Configuration Package.

Partager via