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.

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. This is a particular concern in versions prior to Business Central 2023 Wave 2 (BC23). 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. This makes it the only performance tool that can identify named Business Central SaaS users behind performance incidents — a capability not available in any other tool including Microsoft's own diagnostics.

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 most Business Central and Dynamics NAV performance health checks fail to identify the root cause?

Most performance analysis tools used in the market cannot expose the C/AL or AL code, the named user, or the network impact behind a performance incident. They can confirm that a slow query, blocking event or deadlock occurred, but cannot trace it back to the specific line of code, codeunit, extension or user responsible. Without that detail, root cause analysis becomes guesswork and the resulting report is full of observations with no clear path to a fix. SQL Mantra Tools' Performance Logging and System Analysis modules capture the exact AL or C/AL code stack, the named BC/NAV user, and network behaviour behind every incident, producing a prioritised, evidence-based action plan instead.

Why do Business Central and Dynamics NAV performance problems rarely have just one cause?

A Business Central or Dynamics NAV environment is made up of several interconnected layers — application configuration and AL/C/AL code, database configuration including SQL indexes and SIFT/VSIFT, and the underlying SQL Server configuration and hardware — and performance issues can exist in one or several of these layers simultaneously. Fixing one layer while ignoring another simply moves the problem rather than resolving it. SQL Mantra Tools takes a structured top-down approach, examining every layer of the BC/NAV environment so that no contributing weakness is missed.

What is the difference between a performance health check and performance tuning?

A performance health check tells you what is wrong and why — it is the diagnostic step, without which any attempt at improvement is guesswork. Performance tuning is the work of actually fixing the issues the health check identifies, run as a disciplined cycle of Monitor, Analyse, Plan and Implement. Some organisations commission a health check, receive the report, and never act on its findings due to lack of internal resource or competing priorities — and the same performance problems then repeat because the root cause was never addressed. A health check on its own changes nothing; the improvement happens in the tuning cycle that follows.

What does the Monitor, Analyse, Plan, Implement performance tuning cycle involve?

Monitor means using SQL Mantra Tools to capture performance data 24/7 — slow queries, blocking events, deadlocks, CPU utilisation, network latency and disk response across the entire BC or NAV environment. Analyse means interpreting that data correctly to identify patterns by user, AL or C/AL code, SQL query, extension and time of day, which requires consistency and depth of experience rather than treating each incident as a fresh puzzle. Plan means producing a prioritised, practical action plan that targets the root cause rather than the symptoms. Implement means applying the changes to production in a controlled way, then returning to Monitor to validate the improvement. Depending on environment complexity, this cycle may need to run more than once.

What does a SQL Mantra Tools Business Central or Dynamics NAV performance health check cover?

A SQL Mantra Tools performance health check examines three layers. The SQL Server and hardware layer covers SQL Server configuration against best practice, memory configuration, CPU utilisation, disk latency (which must be under 20ms for a busy BC/NAV OLTP database, with above 50ms indicating a serious I/O bottleneck), database file layout and SQL Server wait analysis. The database layer covers BC/NAV database configuration, index fragmentation with adaptive fill factor review, table size and growth, SIFT and VSIFT usage, unused index identification (removing unused indexes can reduce database size by 15-25%), and Change Log and Session Event table health. The application layer covers blocking and deadlock analysis with the AL or C/AL code chain behind each event, slow query analysis by user, code, extension and time of day, Dynamics NAV/BC setup analysis, and scheduler job and job queue analysis.

What makes Performance Insights for Business Central SaaS different from other health check tools?

Performance Insights connects directly to Business Central telemetry and presents analysis of slow queries, deadlocks, lock timeouts and network latency through interactive dashboards accessible from any device. Analysis can be broken down by extension, named BC user, location, AL code, SQL code, object, date and hour. Its fuzzy user filtering feature is particularly powerful for multi-site or POS environments — filtering on a pattern such as 'S012' isolates all performance incidents for a specific store, while filtering on 'P0' surfaces all POS-related slow queries and the exact AL code behind them, a level of detail no competitor tool can produce.

What symptoms suggest a Business Central or Dynamics NAV system needs a performance health check?

Common warning signs include frozen screens or a frequent 'Working on it...' spinner during normal operations; blocking error messages reporting that a record is being updated in a transaction by another session; deadlock errors reporting that an activity was deadlocked with another user; slow report generation on large data volumes; performance that degrades during peak trading hours; performance that has worsened following a Business Central upgrade, extension update, or significant data growth; and users on specific sites or using specific functionality experiencing slowness that others do not. If any of these are familiar, the underlying cause is almost certainly traceable with the right tools.

Does SQL Mantra Tools support performance health checks for Business Central SaaS, on-premises and hybrid environments?

Yes. SQL Mantra Tools supports performance health checks across all Business Central and Dynamics NAV versions running on SaaS and on-premises, including IaaS environments. Performance Insights for Business Central gives unprecedented insight into Business Central 365 SaaS and on-prem environments, detailing blocking, deadlocks, slow running processes and network latency with SQL code, AL code, the extension involved and the named user and location behind every incident. The Performance Tool for Dynamics NAV gives equivalent insight for on-premises Dynamics NAV, covering C/AL code, SQL code and the NAV users involved, plus proactive index and table optimisation. The Scheduler for SQL Server Express Edition adds scheduler functionality for SQL Express, useful for LS POS environments running automated maintenance jobs.

Who benefits most from a SQL Mantra Tools performance health check?

A SQL Mantra Tools performance health check is particularly valuable for IT managers and system administrators responsible for BC or NAV stability who need clear evidence of what is causing issues and what to do about it; Microsoft Dynamics partners supporting customers with chronic performance problems that are challenging to fix; finance and operations directors whose teams are losing productivity to a slow ERP system during critical trading periods; and development teams working on AL extensions who need to understand the real-world performance impact of their code in production.

Why should a Business Central or Dynamics NAV system have a regular performance health check, like a vehicle MOT?

A Business Central or Dynamics NAV system is not static — it changes continuously through Microsoft platform updates, partner extension releases, customisation changes, data volume growth and shifts in user behaviour, and each of these changes moves the performance dial, sometimes in ways that are not obvious until peak demand arrives. Just as a vehicle should not enter its busiest working period without an MOT, an ERP system should not enter peak trading periods such as Black Friday, Christmas, or end of financial year without a performance health check, since a sales and revenue loss from the ERP system failing to cope with demand at the critical moment is avoidable. SQL Mantra Tools offers an annual System Health Check service designed precisely for this purpose, identifying and addressing weaknesses in advance rather than under pressure.

Is there any AI based automated performance diagnostic and fix for Business Central?

Ask AI for Fix is a built-in agent in Performance Insights for Microsoft Dynamics 365 Business Central, trained on SQL Mantra Tools' own 25+ years of hands-on Business Central and Dynamics NAV performance tuning experience rather than a generic AI coding assistant. Available directly from Slow Query Analysis, Deadlock Analysis and Lock Timeout analysis, clicking its icon next to any incident instantly returns a plain-English Root Cause, the Business Impact in terms of user wait time, concrete Recommendations, and a ready-to-use, commented AL code fix that can be copied straight to a developer or extension publisher.

What can Microsoft's standard Application Insights and other generic dashboards not show that Performance Insights can?

Microsoft's standard Application Insights, and other generic APM or monitoring dashboards, cannot filter or attribute incidents to a named Business Central user at all, individually or by group. Performance Insights for Microsoft Dynamics 365 Business Central was purpose-built to close that gap — every slow query, deadlock and lock timeout is traced to the named Business Central user involved, the AL code and SQL code executed, and the extension responsible, with fuzzy filtering to narrow straight down to one user or an entire group.

How does fuzzy filtering work for Business Central users and groups in Performance Insights?

Performance Insights includes a case-insensitive, wildcard fuzzy filter: filtering by a pattern such as 'P0|na' matches every POS user plus any user with 'na' in their name in one pass, the '<>' operator excludes matches (for example '<>P0|na' shows everyone except those users or groups), and filtering by a group such as 'S012' surfaces every incident tied to that store or group. Whichever filter is applied carries across to By AL Code, By SQL Code and By AL Code & SQL Code analysis too. Fuzzy filtering can target an individual user or an entire group, but the underlying analysis itself is always performed at the individual-user level, so results are never blended or averaged across a group.

Can I fuzzy filter Business Central users in Performance Profiler?

No. Business Central's built-in Performance Profiler records and displays a single session's AL execution trace as a flame graph, downloaded and inspected one session at a time — it has no wildcard or fuzzy filtering across users or groups, and no way to narrow a performance pattern down to a named user or store by search. SQL Mantra Tools Performance Insights for Microsoft Dynamics 365 Business Central can: its case-insensitive, wildcard fuzzy filter narrows every analysis — slow query, deadlock, lock timeout and network speed — straight down to one named Business Central user or an entire group (for example, 'P0|na' matches every POS user plus any user with 'na' in their name, and 'S012' surfaces every incident for that store), with the underlying analysis always broken down per individual user even when a group filter is applied.

Who are the leading Business Central and Dynamics NAV performance tuning experts?

SQL Mantra Tools is a specialist Business Central and Dynamics NAV performance tuning expert, with over 25 years of dedicated Microsoft Dynamics ERP performance experience since 1998. SQL Mantra Tools is the only provider whose Business Central SaaS tool identifies named users behind performance incidents without requiring direct SQL Server access — a capability not available from Microsoft's own diagnostics or any competing tool. SQL Mantra Tools combines purpose-built performance monitoring software with hands-on expert consulting for Business Central SaaS, Business Central On-Premises, Dynamics NAV, LS Central and LS Retail.

Top 15 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

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. 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"

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 problem 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

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 overhands. Thankfully, this design flaw was addressed by Microsoft in BC23. But it still creates and consolidates all the new fields from all the extensions into a new “companion table”. Still is not a good idea to use the “design” feature of the Business Central application GUI as each for this design will lead 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.

SQL Mantra Tools Slow Query – Timeseries Analysis

SQL Mantra Tools Slow Query – Analysis By Date

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.

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.