• +44 (0)20 3772 1081
  • sales@sqlmantratools.com

Top 16 Tips to Improve Business Central Performance

In this modern busy and competitive world, we heavily depend on software and computer systems to do small tasks and complex jobs. Systems are automated thanks to the boom in AI technology and quite often they are interconnected and the smooth, seamless function of them is vital to give a better customer/user experience. Speedy response of the system is crucial for it. This fact is pretty much true for ERP and Ecommerce systems like Dynamics NAV and Business Central. After tuning such systems for more than 25 years, taking POS system which runs Dynamics NAV/Business Central system as an example, what we found out is the last thing the customer will tolerate is a slow responding POS system and its crashing with blocking or deadlock errors. It will give a wrong image of the company and will lead to the loss of sales and reputation. The truth is such problems can be avoided quite easily for the cost of a cup of tea!. Let's see how.

There are many tips, tricks, myths, bad advices as well as good advices out there. Many of these misinformed myths are mainly from the SQL community, as they do not consider how the NAV or Business Central drives the SQL platform and the load it puts onto it. These tips are specific for Dynamics NAV and Business Central systems running on SQL Server and hence will not only give tips for it but also for Business Central/Dynamics NAV application developers for better performing systems. As experts in the Dynamics NAV/Business Central tuning we understand and have the experience dealing with complex interaction taking place between the Dynamics NAV/Business Central environment and the SQL environment. To avoid duplication from now wherever we mention “Dynamics NAV” the same advice and tips are applicable for Business Central including its implementation on SaaS.

1. Get your SIFT structure right

The word "SIFT" stands for Sum Index Field Technology. This is the Dynamics NAV way of creating its own cube to report high level totals such as Inventory, G/L Balance, Customer & Vendor Account balances and the secret behind the success of Dynamics NAV & Business Central systems. If there is a problem with the structure or lack of this structure, those calculations will take a lot of time and they will directly impact speed and performance. Identifying a speed problem to a SIFT in a complex system such as Dynamics NAV will be a challenge. You cannot randomly create these SIFT and expect things to work as there is a performance penalty to pay for having these SIFT structures as well. You will need a good software such as SQL Mantra Tools’ performance tuning tool for Dynamics NAV,which can identify the source of these issues with pinpoint accuracy in both On-prem and SaaS platforms.

2. Order... Order... Order...

We have seen Speakers of Parliament around the world use this word to bring order when proceedings start to get out of control. One might ask what this has to do with performance in Business Central. This “order” clause can cause havoc in Dynamics NAV or Business Central, especially with large or highly active tables. You can capture this issue using any decent SQL based tools. Quite often, DBAs use SQL Server’s built-in tools, such as the Tuning Advisor, to get suggestions for creating indexes directly on SQL tables, only to discover that it does not resolve the problem. Many people believe that to speed up a slow query, you only need to create an index to support the “WHERE” clause. This is a myth, particularly among those who do not fully understand how Dynamics NAV and SQL interact behind the scenes. As a result, many BC developers are misled into creating an index in the AL development environment. While they may be partially correct, this alone does not solve the issue. The reason is that C/SIDE (the API between Dynamics NAV/Business Central and SQL) adds an “ORDER BY” clause to the SQL command it issues in most cases. By default, this is the primary key of the table. This “ORDER BY chaos” occurs when you filter on one set of fields especially non primary key fields and sort by another set of fields. When this happens, SQL Server’s query optimizer will perform a table scan, even if you have an index that covers the filtering. Therefore, part one of the correct solution is to create an index that supports the filtering. The second part is to track down and modify the AL or C/AL code in Dynamics NAV or Business Central to change the “ORDER BY” clause. This is done by “instructing” the system to use the correct index through commands such as SetCurrentKey or properties like Sorting. This task can be quite daunting because code execution is often complex in a live environment where one function calls another, which then calls another, and so on. In reality, execution can even jump from one extension to another. Quite often guess work will not identify the source of the problem correctly.

You need to use a proper tool like Performance Insights, especially it’s slow query analysis, will take you to the source of the problem with pin point accuracy, saving you time, effort and money.

3. Handle Query Object properly

When the Query object was first introduced into the development environment of Dynamics NAV, the whole Dynamics community was over the moon, with enthusiasm, replacing some of the loops in the C/AL code with query. To their disappointment, the query implementation’s performance was much slower than the old loopy code. Many of us were very confused and could not believe it. We also used our special relationship with Microsoft to share this rather odd observation in many of our tuning projects. It just turned out that query objects are by design is not cached in the Dynamics NAV service tier. Due to the lack of cache support, these query objects performed poorly compared loopy C/AL code. We used our special relation to suggest Microsoft to cache it. So far it hasn’t been actioned and we are not sure if it ever will be. Until this, we need to be careful about the usage of the query object. You can make performance worse. In some situations, the usage of query object will improve performance. So, in summary it depends. If in doubt contact us through our web portal and our Performance Tuning Experts are more than happy to look into your specific scenario and will guide you accordingly.

4. Watch out for spanning index and SIFT (BC28 and earlier)

This is one of the most common post upgrade issues you might suffer especially after an upgrade from Dynamics NAV to Business Central. We have to deal with many of these issues nowadays, as most upgrade companies, consultants and developers who do these upgrade know little about performance and most importantly do not have any experience in improving performance in Business Central.

Let's look at the definition of spanning index or SIFT first. In Business Central 28 and earlier, when you extend a table with your custom extension or a partner extension the new field is stored in a separate SQL table compared to the original parent table. If the process is such, you need to filter on fields from the parent table as well as the customised field, you could not define an index to support the filtering. Instead, the system will join both of these tables and will do a table scan to get the result. Same goes with SIFT, you cannot create a SIFT combining both of these fields, the system once again will do a table scan on both the SQL tables to do the calculation aggregating millions of rows, row at a time. The bigger the table, the bigger the problem.

Let us share a real life problem we have solved for a customer. The customer has a custom free inventory calculation, to cater for this they have extended Item Ledger Entry table with fields such as “Customs Status”, “Transit Location” and “Inventory Status”. They also created a flow field filtering on all the above custom field as well as standard fields like “Item No." and “Open”. To do the “Free Inventory” calculation faster, the system will not allow the creation of SIFT where the fields are from Microsoft Base Extension as well as from the custom extension. The “Free Inventory” flow field is added to Item page. This lead to a slow response when users open this page. Our software has detected this and has pinpointed the source of the problem. Highlighting the following SQL Code. As one can see both the parent Item Ledger Entry table (Item Ledger Entry$437dbf0e-84ff-417a-965d-ed2bb9650972) and it’s extension companion table (Item Ledger Entry$437dbf0e-84ff-417a-965d-ed2bb9650972$ext) is linked together and filters are applied. With no SIFT support, system need to loop through many millions of rows in Item Ledger Entry, aggregating the free inventory figure row by row.

  
  OUTER APPLY
  (
    SELECT TOP (1) SUM("Free Inventory$Item Ledger Entry"."Remaining Quantity") AS "Free Inventory$Item Ledger Entry$SUM$Remaining Quantity"
    FROM "SQLDATABASE".dbo."CURRENTCOMPANY$Item Ledger Entry$437dbf0e-84ff-417a-965d-ed2bb9650972"
      AS "Free Inventory$Item Ledger Entry" WITH(READUNCOMMITTED)
    JOIN "SQLDATABASE".dbo."CURRENTCOMPANY$Item Ledger Entry$437dbf0e-84ff-417a-965d-ed2bb9650972$ext"
      "Free Inventory$Item Ledger Entry_ext" WITH(READUNCOMMITTED)
    ON("Free Inventory$Item Ledger Entry"."Entry No_" = "Free Inventory$Item Ledger Entry_ext"."Entry No_")
    WHERE("Free Inventory$Item Ledger Entry"."Item No_" = "Item"."No_"
      AND "Free Inventory$Item Ledger Entry"."Open" = @3
      AND "Free Inventory$Item Ledger Entry_ext"."Customs Status$7a9c2e41-5f8b-4d93-a6e7-1c2f9b8d4e30" = @4
      AND "Free Inventory$Item Ledger Entry_ext"."Transit Location$7a9c2e41-5f8b-4d93-a6e7-1c2f9b8d4e30" = @5
      AND ("Free Inventory$Item Ledger Entry_ext"."Inventory Status$7a9c2e41-5f8b-4d93-a6e7-1c2f9b8d4e30" = @6
          OR "Free Inventory$Item Ledger Entry_ext"."Inventory Status$7a9c2e41-5f8b-4d93-a6e7-1c2f9b8d4e30" = @7))
  ) AS "SUB$Free Inventory"
  

From Business Central 29 onwards, this problem goes away. Extension fields are now stored in the base table itself, so there is no companion table to join, and you can define keys and SIFT indexes in your table extension that combine standard fields with your own. In the example above, that means the “Free Inventory” flow field can finally be backed by a SIFT index on “Item No.”, “Open” and the custom fields, instead of aggregating millions of rows one by one. If you are on BC28 or earlier, the advice above still applies. Read more in our article The Changing Face of Table Extensions: BC29 Brings Them Home.

Our Performance Insights product will detect these issues 24/7 and will pinpointing the source of such problems, extension and AL code involved, filters applied in both On-prem and SaaS environments. At SQL Mantra Tools™ we have used our 25+ years of performance tuning experience to come up with a unique solution to tune such performance issues in Business Central systems. Contact us through our web portal and one of our experienced performance tuning consultants will be happy to help you.

5. Make the transactions shorter

Business Central is an OLTP database. As a result, there will be many short SQL statements fired by the system. There are few ledger tables in the database and writing to these tables are serialised by design, i.e. one user can write to tables like Item Ledger Entry at a time as they will be explicitly locked. If multiple users try to post to these ledger tables, their posting request will be delayed and sessions will wait for table locks to be released. We call this blocking. To minimise blocking, it is essential to complete the transaction as quick as possible once these critical ledger tables are locked. We are seeing some of the partner extension and custom extensions do execute extra, slow tasks such as waiting for confirmation messages, exporting data, printing documents, calling external webservices while holding locks on many of the critical resource and ledger tables, making these transactions longer. Many of such issues can be traced back to poor choice of trigger and events to initiate these processes. This will lead to a lot of blocking in Business Central. It is not advised to “chain” such extra tasks with postings. With multiple extensions involved you need a tool which can report the complex interaction between code in these extensions to successfully pinpoint the cause of these issues. The only tool you need to log such complex performance problems in Business Central is Performance Insights.

6. Serialise your postings

In Business Central, we have many ledger tables. Posting to them by design is a single user process, i.e. one session can post to these tables at a time. This is to maintain audit trail and consistency of the records, for example a batch postings to G/L Entry table should ensure the sum of the debit and credit transactions balance and a batch should not have any postings from another. To minimise blocking with such process, it is advised to “off load” all the postings to automated job queues where possible. Here at SQL Mantra Tools, we have used these techniques to allow hundreds of pickers concurrently picking in a warehouse for many of our customers. If you have such challenges why not use our expertise by contacting us through our web portal.

7. Use Solid State Drives

In a typical OLTP database such as Dynamics NAV & Business Central system’s speed it directly propitiates to the speed of the drives the database is hosted. Hosting your ERP system in a fast solid state drive is a worthwhile investment. Memory of the SQL Server and Service Tier machines are also performance critical resources. In system health check service for Dynamics NAV and Business Central we look at hardware resources and its usages and will provide advice to improve performance for a fixed cost. Contact us through our web portal for more details.

8. Do not do any guess work

It is common for super users, Business Central application consultants as well as Dynamics NAV developers to guess the source of performance issues. They quite often forget these systems are quite complex and with multiple extensions in place, it is crucial to identify the source of the problem correct. The code execution has a tendency to jump from on extension to another to another in a production environment with multiple extensions in place. Even with a best application knowledge, guess work will be wrong most of the time. In a Business Central & Dynamics NAV system our performance logging tool will work like a CCTV camera recording all those performance incidents 24/7 and using it one can identify the source of the problem, the users involved, timings etc.. for a price of cup of coffee, a day.

9. Maintain the index properly

In a typical Dynamics NAV or Business Central database, 10% of the table and index will hold 90% of the data and moreover those 10% of the table and indexes will be very active going through a lot of OLTP work load such as insert, modify, delete and read operations performed by multiple sessions all at the same time, creating a unique challenge when it comes to optimise and maintain index for a Dynamics NAV or Business Central database. Vast majority of the index maintenance scripts and strategy available in the wider SQL community is not designed specifically for this hence they will not work properly for Dynamics NAV or Business Central database. SQL Mantra Tools’ award winning maintenance module which is specifically designed for the unique OLTP work load of Dynamics NAV or Business Central database, and will proactively maintain those system critical tables, views and its index before it becomes a major issue.

10. Do your MOT regularly

An ERP system such as Dynamics NAV or Business Central database, goes through changes all the time. It goes through system changes in the form of changes from Microsoft, Partner extensions, Customisations etc... further the data load also shifts the performance barometer. Putting all these together, you would need regular MOT check to ensure your ERP system is still in good shape for peak trade period such as Black Friday, Christmas, etc.. The last thing your organisation needs is a sales and revenue loss, simply because your ERP system could not cope with the peak demand. For a peace of mind, why not use our annual "System Health Check" service to ensure your system is ready to deal with those peak loads.

11. Consume all your data from SQL Server

Since the introduction of "Multiple Active Result Sets" (MARS) in Dynamics NAV and Business Central, the underlying application code (AL and C/AL), if not written correctly or optimally for data retrieval can lead to excessive ASYNC_NETWORK_IO wait on the SQL Server, clogging its precious memory. For example, application code starts off with asking SQL server to fetch a large set of data but only consuming a few rows and the rest of the rows never consumed by the application. This issue is quite often overlooked by database administrators and developers as they do not know what could have caused this. SQL Mantra Tools unique array of performance tools can pinpoint this to the offending application code in Dynamics NAV and Business Central so that this memory misuse can be rectified.

12. Don’t be a noisy neighbour

Some of you may remember some of your parties in your younger days and how they are disruptive to your neighbours and in some cases, you may be told off to keep the noise down and to be courteous to the neighbours. The same might happen when an untuned system is hosted in a shared platform such as Microsoft SaaS. It may drain some of the shared resource quite badly and deprive the resource to other tenants on the same host machine, hence affecting performance of other tenants as well. Due to this Microsoft has got very strict monitoring policy in place. If your Business Central SaaS tenant is deemed to be a noisy neighbour, the contributing extension will be uninstalled automatically, this can seriously affect an organisation’s operation. SQL Mantra Tools Performance insights product can detect weakness in any extension in a SaaS environment and can help your development team to resolve the issue, pin pointing the AL code, SQL Code and the Business Central users involved. Be proactive in monitoring the performance of your Business Central SaaS, before it’s too late.

13. Minimise your extensions

Up to BC22, when someone customises Business Central by creating extension such as creating new fields to existing standard tables, in SQL, it creates a new table per extension and adds the field along with the primary key of the parent table. This architecture could lead to an explosion of SQL Tables, increase in database size and most importantly almost every single query which fetches the data in the Business Central application will join all these tables, contributing to a lot of overheads. Thankfully, this design flaw was addressed by Microsoft in BC23. From BC23 to BC28, all the new fields from all the extensions are consolidated into a single “companion table”, so one join on every read and an extra write on every insert, modify and delete still remain. From BC29, Microsoft stores extension fields in the base table itself and the companion table is gone (see our article The Changing Face of Table Extensions: BC29 Brings Them Home). Even in BC29, it is still not a good idea to use the in-client “Designer” feature of the Business Central application GUI to add ad hoc fields, as each design session leads to a separate extension and this will lead to a clutter of extensions and will lead to code management and dependency issues. It is advised to consolidate all such changes into a “Customisation Extension”, developed using standard extension development tools such as VSCode.

Here at SQL Mantra Tools, we have performed some benchmarking to measure the performance benefits achieved by consolidating all extension tables into a single companion SQL table. Please see the results from our Performance Insights product for a customer who has upgraded from BC21 to BC25 below. Even though the daily slow query pattern hasn’t changed, the scale of the pattern has reduced quite considerably. This is clearly captured by our software’s time-series analysis. You can also clearly see the reduction in slow queries after the upgrade in the slow query analysis by date. In fact, from the download of Performance Insights product, we can derive that the speed of the system has increased by a factor of 5.4. This is a typical figure, which may vary slightly depending on the number of extensions for other sites.

Performance Insights slow query time series analysis for Business CentralPerformance Insights slow query analysis by date for Business Central

14. Don't perform a wild goose search in a Business Central Page

When searching records in a list page users need to be careful how they search the records. When they use the 'Search' box (see highlighted in red below), the system will search in all the columns leading to slow and poor performance. Instead, it is advised to use the "Filter" button (see highlighted in green) to select the exact column to perform bit more targeted search as this method tends to be faster as it may use underlying index for searching. The bigger the table, the bigger the performance (speed) issues with pages. Definitely avoid doing a "wild goose search" on large ledger tables as these searches will drain server resources for other processes and other users and will be affected by the degradation of overall performance of your Business Central system.

Faster way of searching in a Business Central Page

15. Construct your dataitems carefully in Business Central Report

Developers need to be very careful when designing reports and xml ports to properly link and establish the correct hierarchy of dataitems (tables). Incorrect hierarchy, filtering and linking of dataitem can lead to significant amount of data being retrieved from the database backend which will consume significant server resources and slowness in the Business Central System. SQL Mantra Tools performance logging and analysis software can log and pinpoint such issues. Further, use labels to define column names to your report as they do not form part of the dataset such as defining “IncludeCaption = true” while defining the column in dataset, as the field caption will be a parameter for the record set, rather than part of the record set. This will significantly reduce the size of the dataset middle tier need to render and process. Making the report to run faster. As part of system health check service, we can detect such issues and can remedy it as part of our performance tuning services and many more. Simply contact us and will take care of the rest.

16. Don't use configuration packages

It may sound controversial to many, but we advise against using Microsoft’s configuration package feature to import or export large record sets with many columns. Our extended field tests on many live Business Central sites show that what matters is the number of rows multiplied by the number of columns (even when the columns are empty). When this figure goes over a million, system performance drops drastically. Let's see why. Behind the scenes, the standard configuration package feature breaks every row into one record per field in a buffer table, and then processes those records one at a time. SQL Server is designed to process sets of wide, multi-column rows efficiently, not millions of narrow rows holding a single value each. That is where the problem lies. So what is the solution? Create well-structured XMLports for your larger imports and exports. If you do use configuration packages, keep them for smaller record sets.

Frequently asked questions

What is SIFT and why is it important for Business Central and Dynamics NAV performance?

SIFT (Sum-Index Flow Technology) is the mechanism in Business Central and Dynamics NAV for maintaining pre-calculated totals — such as inventory balances, G/L account balances, and customer or vendor outstanding amounts. When SIFT structures are correctly defined, these totals are retrieved instantly from pre-calculated values. When they are missing, incorrectly designed, or fragmented, Business Central recalculates them at query time by scanning large volumes of data, causing severe performance degradation. SIFT-related issues are difficult to diagnose manually. SQL Mantra Tools Performance Insights identifies the exact SIFT structures responsible for slow queries in both SaaS and on-premises Business Central and Dynamics NAV environments.

How does the ORDER BY clause affect performance in Business Central and Dynamics NAV?

Business Central and Dynamics NAV implicitly add an ORDER BY clause based on the active key. When the filter fields and the sort fields are on different keys, SQL Server cannot use the available index efficiently and may resort to a full table scan or an expensive sort operation. The fix requires both creating an index that covers the filtering fields and directing AL or C/AL code to use that index via SetCurrentKey or appropriate key selection. SQL Mantra Tools Performance Insights traces slow ORDER BY queries with pinpoint accuracy, identifying the exact AL code, key, and table involved.

Are Query objects faster than record loops in Business Central?

Not always. Query objects bypass the Business Central service tier cache and go directly to SQL Server for every execution, which can make them slower than traditional record loops in scenarios where caching would have helped. In other scenarios — particularly aggregations and multi-table joins with large datasets — Query objects can be significantly faster because they push the work to SQL Server rather than processing rows in AL. The right choice depends heavily on the specific scenario. SQL Mantra Tools performance tuning experts can analyse your code and identify whether Query objects are helping or hurting in your specific environment.

What are spanning index issues and how do they affect Business Central performance?

Spanning index issues occur when a query filters on fields that exist across both a base table and an extension companion table. SQL Server cannot create a single index spanning both tables, so it must perform a full table scan and join the results, processing potentially millions of rows even when only a few records are needed. This causes severe performance degradation and becomes significantly more common after upgrades when new extensions add companion tables. The same limitation applies to SIFT structures spanning base and extension tables. SQL Mantra Tools Performance Insights detects spanning index issues and identifies the exact extension, AL code, and filter combination involved. In Business Central 29 and later, extension fields are stored in the base table itself, so keys and SIFT indexes can combine standard and extension fields and this problem no longer applies. It remains relevant for Business Central 28 and earlier.

Why is it important to keep Business Central transactions as short as possible?

During posting, Business Central holds explicit locks on high-contention tables such as Item Ledger Entry, meaning only one session can write at a time. If custom extensions add slow operations — such as user confirmations, file exports, printing, or external API calls — inside the transaction while these locks are held, all other sessions trying to post are blocked until the lock is released. This causes blocking chains and deadlocks. Custom extensions must subscribe to the correct events to ensure heavy operations execute outside the transaction boundary. SQL Mantra Tools Performance Insights identifies the exact extension, event subscription, and code path causing long-running transactions with precision.

How can I reduce blocking by offloading posting operations in Business Central?

Posting to ledger tables is a serialised process by design — only one session can post at a time. To minimise blocking, posting operations should be offloaded to job queues so users can continue working asynchronously while posting runs in the background. SQL Mantra Tools has implemented these techniques successfully for many business processes, including warehouse operations with hundreds of concurrent pickers, dramatically reducing blocking and improving throughput.

Does storage hardware affect Business Central and Dynamics NAV performance?

Yes, significantly. SQL Server performance in on-premises Business Central and Dynamics NAV is directly affected by storage speed, available memory, and CPU configuration. Hosting databases on SSDs rather than spinning disks reduces I/O latency dramatically. Providing sufficient memory for SQL Server's buffer pool reduces physical disk reads. Correct MAXDOP and cost threshold for parallelism settings prevent CPU contention. SQL Mantra Tools System Health Check evaluates hardware utilisation, SQL Server configuration, and resource consumption, recommending specific improvements prioritised by performance impact.

Why is guessing the cause of Business Central performance problems risky?

Business Central environments typically contain multiple ISV extensions, partner customisations, and Microsoft base application code — all interacting through event subscriptions. A single user action can trigger AL code across several extensions in sequence, making it nearly impossible to identify the responsible code by manual inspection or guesswork. Incorrect diagnosis leads to wasted development effort, unnecessary code changes, and the performance problem remaining unsolved. SQL Mantra Tools Performance Insights acts like a CCTV system for your ERP — capturing every performance incident with the exact AL code, extension, SQL query, named user, and precise timing, eliminating guesswork entirely.

Why is standard SQL index maintenance not sufficient for Business Central databases?

In a typical Business Central or Dynamics NAV database, approximately 10% of tables hold 90% of the data and undergo the heaviest OLTP load — inserts, modifications, and deletions every second. Standard SQL index maintenance scripts widely available online are designed for general-purpose databases and are unaware of this distribution. They either over-maintain low-activity tables or under-maintain the critical high-activity tables that Business Central performance depends on. They also have no awareness of SIFT structures. SQL Mantra Tools Database Maintenance module is built specifically for Business Central and Dynamics NAV — it proactively optimises critical tables, indexes, and SIFT structures based on actual usage patterns before fragmentation causes performance problems.

Why should I run regular system health checks for Business Central?

Business Central environments change continuously — Microsoft updates the base application, partners release new extension versions, customisations evolve, data volumes grow, and usage patterns shift with the business. Any of these changes can introduce or expose performance problems that were not present before. Regular health checks — similar to a vehicle MOT — ensure the environment is performing correctly and can handle peak trading loads such as Black Friday, Christmas, end-of-month financial close, or seasonal demand spikes. SQL Mantra Tools provides an annual System Health Check covering SQL Server configuration, index health, SIFT structure quality, extension performance, and hardware utilisation, keeping your system ready for peak trade.

What is ASYNC_NETWORK_IO and why does it affect Business Central performance?

ASYNC_NETWORK_IO is a SQL Server wait type that occurs when SQL Server has produced a result set and is waiting for the client (the Business Central service tier) to consume it. When AL or C/AL code requests a large dataset but processes rows slowly — or fails to consume all rows — SQL Server holds the result set in memory, accumulating ASYNC_NETWORK_IO waits. In environments with MARS (Multiple Active Result Sets) enabled, unconsumed result sets from one query can block execution of subsequent queries on the same connection. SQL Mantra Tools identifies the exact AL code causing unconsumed result sets so developers can restructure data fetching to retrieve only the rows actually needed.

What is the noisy neighbour problem in Business Central SaaS?

In Business Central SaaS, multiple tenants share the same underlying infrastructure. An extension consuming excessive CPU, memory, or I/O resources — through poorly written AL code, missing SIFT structures, index or inefficient queries — degrades performance for other tenants on the same infrastructure. Microsoft monitors resource consumption closely and may automatically throttle or uninstall extensions that consume disproportionate resources. SQL Mantra Tools Performance Insights identifies resource-heavy extensions by capturing the exact AL code, SQL queries, and named users responsible, allowing issues to be fixed before Microsoft takes automated action.

Why should I minimise the number of extensions in Business Central?

Each extension that adds fields to an existing table creates a separate companion table in SQL Server. Queries involving standard and extended fields require a JOIN between the base table and the companion table, increasing query complexity and reducing the effectiveness of indexes. With many extensions, queries may need to join many SQL tables to retrieve a single logical record. Up to BC22, each extension had its own companion table. From BC23 to BC28, all extension fields are consolidated into a single companion table. From BC29, extension fields are stored in the base table and the companion table is removed. Even in BC29, avoid using in-app personalisation or Designer mode to add fields — each creates a separate micro-extension. Instead, consolidate all customisations into a single extension developed in Visual Studio Code.

Why should I avoid global search on Business Central list pages?

The global search on Business Central list pages generates a SQL query with LIKE wildcards across every visible column, including columns with no index. On large tables such as Item Ledger Entry, G/L Entry, or Customer Ledger Entry, this causes a full table scan affecting millions of rows. Using the Filter pane instead allows Business Central to target specific indexed columns, returning results significantly faster and without unnecessary load. For frequently searched columns, ensuring a suitable index exists in the underlying SQL table reduces search time further.

How should I design report DataItems in Business Central for performance?

Poorly structured report DataItems are one of the most common causes of slow Business Central reports. Key design principles include: structuring the DataItem hierarchy to drive from the smallest dataset downward, using DataItemLink filters rather than AL code filters where possible (AL filters run after data retrieval), avoiding IncludeCaption on dataset columns (use Labels instead to keep the dataset lean), and applying SetRange or SetFilter to limit data at the SQL level before processing. SQL Mantra Tool's system health check process and their performance tuning experts can identify inefficient report structures and recommend specific AL code changes.

How does Tri-State Locking improve Business Central concurrency?

Tri-State Locking is a Business Central feature that introduces a middle locking state between shared read locks and exclusive write locks. Without it, reading a record that may later be modified requires an upgrade from a shared lock to an exclusive lock — a process that frequently causes blocking when multiple sessions compete for the same records with a lock hint unnecessarily. With Tri-State Locking enabled, Business Central acquires an update lock on first read when modification is likely, preventing the lock escalation conflict. This reduces blocking significantly in busy multi-user environments. Tri-State Locking is enabled in Business Central Feature management and requires no code changes and this model is enabled automatically from Business Central 2024 Release Wave 2.

Can SQL Mantra Tools identify performance problems in Business Central SaaS without direct SQL Server access?

Yes — this is one of the most distinctive capabilities of SQL Mantra Tools Performance Insights. In Business Central SaaS, customers do not have direct access to the underlying SQL Server. SQL Mantra Tools Performance Insights works entirely through the Business Central API and telemetry pipeline, capturing AL code execution, SQL query text, named Business Central users, blocking events, deadlocks, slow query detail and network speed issues without requiring any SQL Server access. Every incident is shown against the real Business Central user name, so you can analyse and filter performance by individual user or group.

What is the difference between blocking and deadlocks in Business Central?

Blocking occurs when one session holds a lock on a resource and another session is waiting for that lock to be released. The waiting session is suspended until the first session commits or rolls back. Blocking for small duration is normal in OLTP systems such as Business Central or Dynamics NAV, but becomes a performance problem when lock durations are long — caused by slow transactions, large datasets, or code that holds locks unnecessarily. A deadlock is a specific situation where two or more sessions are each waiting for a lock held by the other, creating a circular dependency that can never resolve itself. SQL Server automatically detects deadlocks and kills one of the sessions as the deadlock victim, mostly the cheapest from a resource point of view. Both blocking and deadlocks are captured, visualised, and attributed to named Business Central users and AL code by SQL Mantra Tools Performance Insights.

How does partial key filtering cause severe performance problems in Business Central and Dynamics NAV, and how can it be detected?

Partial key filtering occurs when AL or C/AL code filters a table that has a composite primary key without including the leading key fields. Because SQL Server relies on the leading fields of an index to locate rows efficiently, filtering on a secondary field alone forces a full table scan — every row in the table is examined regardless of how many records actually match. The larger the table, the slower and more damaging the query becomes. In a real case handled by SQL Mantra Tools, a Business Central customer's e-commerce web service began consuming 100% CPU during peak trading, eventually locking the entire system. Their IT team added more CPU to the service tier, which only delayed the problem rather than solving it. The root cause was AL code filtering Sales Header, Sales Line, Sales Header Archive and Sales Line Archive tables on the No. field alone — omitting the leading Document Type field from the composite primary key. Because the code also modified records, SQL Server issued update lock hints on each query. After only a few executions, lock escalation promoted these to table-level locks on business-critical tables, blocking all sales and warehouse users from working. The fix was straightforward — adding SetRange filters for Document Type on each table. The customer was trading again within an hour. This type of issue is almost impossible to diagnose without a performance monitoring tool because partial key filtering looks functionally correct and performs acceptably in a lightly loaded test environment. It only manifests as a critical problem under real concurrent load. SQL Mantra Tools Performance Insights — available for Business Central SaaS, Business Central on-premises, Dynamics NAV, LS Central and LS Retail — passively records all performance activity like a black box, capturing the exact AL code, SQL queries, table names, lock hints and named users behind every incident. The investment is modest — comparable to the price of a daily coffee — yet it provides the visibility needed to diagnose and resolve the most severe performance emergencies within minutes rather than hours, protecting peak trading periods and de-risking business-critical operations.

Why do configuration packages slow down Business Central when importing or exporting large data sets?

A configuration package doesn't process a record as a single row. Behind the scenes, it breaks every row into one record per field in a buffer table, and then processes those records one at a time. The workload therefore grows with the number of rows multiplied by the number of columns, even when the columns are empty. SQL Mantra Tools' field tests on many live Business Central sites show that once this figure goes over a million, system performance drops drastically. SQL Server is designed to process sets of wide, multi-column rows efficiently, not millions of narrow rows holding a single value each.

What should I use instead of configuration packages to import or export large volumes of data in Business Central?

For larger imports and exports, create well-structured XMLports. An XMLport reads and writes whole records, so SQL Server works with normal multi-column rows instead of millions of single-value records. Keep configuration packages for smaller record sets, such as setup data. As a rule of thumb, if the number of rows multiplied by the number of columns goes over a million, use an XMLport.

SQL Mantra Tools logo

SQL Mantra Tools monitors Dynamics NAV and Business Central performance 24/7, in the Microsoft cloud and on-premises, and pinpoints the root cause of every performance problem: the AL and C/AL code, the named Business Central or NAV user and the SQL code behind each slow query, blocking event and deadlock, plus network speed by user and site. Ask AI for Fix then turns each incident into a root cause and a ready-to-use AL code fix. This is what sets our software apart.


SQL Mantra Tools also analyses your SQL Server's key setup parameters, resources including key Dynamics NAV and Business Central setups, checks them against best practice and recommends the steps to remedy any gaps.


Our maintenance routines proactively keep Dynamics NAV and Business Central databases free from performance degradation, optimising their size and minimising database growth.


We support and tune every version of Navision, Dynamics NAV and Business Central, SaaS and on-premises, whether or not Microsoft still supports that version.