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.

Partial Key Filtering Issue in Business Central

Introduction to performance tuning in Dynamics NAV and Business Central

Recently, one of our customers called our support desk asking for emergency help in identifying a severe performance issue with their live Business Central site. The web service was consuming 100% CPU, and once it hit that level, it remained at 100%, eventually locking to the extent that no one was able to use the system at all.

The customer’s infrastructure team allocated more and more CPU to the service tier machine and discovered that this only delayed the web service reaching 100% CPU rather than solving the issue. As a result, they could not trade. Their system was down during their peak trade. Due to the severity of the problem, the case was escalated to one of our senior performance tuning experts.

The customer and their IT team were clueless about the root cause of the issue. The only thing they could recall was that they had recently deployed quite a substantial enhancements to their website, as well as changes to the way it integrates with their Business Central system.

The customer’s CEO stated that they tested all these changes in a test environment and everything worked fine. They said they had never seen such bad performance issues and questioned what had happened.

Our senior consultant replied "this is quite common. You are never able to properly stress test (performance test) a system in a test environment, as it is not realistic to simulate the concurrent workload that a live system goes through. That is why having a proper performance monitoring tool, such as SQL Mantra Tool’s Performance Insights performance monitoring tool, is essential for all businesses".

In this case, having SQL Mantra Tool paid off for the client. SQL Mantra Tool had been passively recording system activity and worked like a "black box"., capturing all the necessary details for a performance tuning exercise.

Many of you may be interested to know what the issue was and how we fixed it. Let’s dive into the problem.

We turned to SQL Mantra Tool’s automated performance monitoring feature, and thanks to its unique ability to capture application code (A/L and C/AL), we managed to diagnose the issue with pinpoint accuracy in no time.

The issue was caused by the way tables such as Sales Header, Sales Header Archive, and Sales Line Archive were filtered. Their e-commerce website was calling a web service in Business Central, passing information such as the sales order number along with other data. In Business Central, the following code was used to process that request:

                
                  local procedure ProcessOrder(pOrderNumber: Text)
                  var
                    SalesHeader: Record "Sales Header";
                    SalesLine: Record "Sales Line";
                    SalesHeaderArchive: Record "Sales Header Archive";
                    SalesLineArchive: Record "Sales Line Archive";
                  begin
                    SalesHeader.Setrange("No.", pOrderNumber);
                    If SalesHeader.Findset() then
                    Repeat
                      // Processing Code
                      SalesLine.Setrange("Document No.", SalesHeader."No.");
                      If SalesLine.Findset() then
                      Repeat
                        // Processing Code
                      Until SalesLine.Next = 0;

                      SalesHeaderArchive.Setrange("No.", SalesHeader."No.");
                      If SalesHeaderArchive.Findset() then
                      Repeat
                        // Some Processing Code
                        SalesLineArchive.Setrange("Document No.", SalesHeaderArchive."No.");
                        If SalesLineArchive.Findset() then
                        Repeat
                          // Some Processing Code
                        Until SalesLineArchive.Next = 0;
                      Until SalesHeaderArchive.Next = 0;
                    Until SalesHeader.Next = 0;
                  end;

There is no functional issue with the above code; however, it is not written with performance in mind. The Sales Header, Sales Line, Sales Header Archive, and Sales Line Archive tables all have composite primary keys.

When filtering tables that have composite primary keys, it is important to filter on fields that are part of the primary key, especially the fields at the beginning of the key. For example, the primary key of the Sales Header table is defined as "Document Type, No.". When filtering the Sales Header table to retrieve an order, it is essential to filter on the "Document Type". field as well as the "No.". field.

The same applies to the Sales Line, Sales Header Archive, and Sales Line Archive tables. If only one part of a composite primary key is filtered, especially when the leading key field is not included, the system will perform a table scan and will not utilise the index. The larger the table, the slower the query becomes.

Partial key filtering issue occurs when a table with a composite primary key is filtered without including the leading key fields, forcing SQL Server to perform a table scan instead of using an index in Business Central and Dynamics NAV system.

In this processing code, records are updated based on business logic. As a result, the second and subsequent SQL queries are issued with update table lock hints. After a few executions, SQL Server begins lock escalation, and very quickly, business critical tables such as Sales Header and Sales Line will have table level locks. This severely affects system concurrency, leaving users especially in sales and warehousing unable to work.

Once the problem was correctly diagnosed (thanks to SQL Mantra Tool), the fix was simple. We added a few additional Setrange commands (highlighted below). Within an hour, the customer was able to trade again, handle their peak workload, and start shipping goods to meet delivery deadlines.

Before this incident, the CEO had questioned the need for a performance monitoring tool and tried to save a few pounds (roughly the price of a coffee). After this experience, he was convinced it was worth the investment to de risk key business operations and it may well have saved his job.

Please find the tuned code below. Changes are highlighted in bold.

                
                  local procedure ProcessOrder(pOrderNumber: Text)
                  var
                    SalesHeader: Record "Sales Header";
                    SalesLine: Record "Sales Line";
                    SalesHeaderArchive: Record "Sales Header Archive";
                    SalesLineArchive: Record "Sales Line Archive";
                  begin
                    SalesHeader.Setrange("No.", pOrderNumber);
                    SalesHeader.Setrange("Document Type", SalesHeader."Document Type"::Order); 
                    If SalesHeader.Findset() then
                      Repeat
                        // Processing Code
                      SalesLine.Setrange("Document No.", SalesHeader."No.");
                      SalesLine.Setrange("Document Type", SalesHeader."Document Type");
                      If SalesLine.Findset() then
                      Repeat
                        // Processing Code
                      Until SalesLine.Next = 0;

                      SalesHeaderArchive.Setrange("No.", SalesHeader."No.");
                      SalesHeaderArchive.Setrange("Document Type", SalesHeader."Document Type");
                      If SalesHeaderArchive.Findset() then
                      Repeat
                        // Some Processing Code
                        SalesLineArchive.Setrange("Document No.", SalesHeaderArchive."No.");
                        SalesLineArchive.Setrange("Document Type", SalesHeaderArchive."Document Type");
                        If SalesLineArchive.Findset() then
                        Repeat
                          // Some Processing Code
                        Until SalesLineArchive.Next = 0;
                      Until SalesHeaderArchive.Next = 0;
                    Until SalesHeader.Next = 0;
                  end;

Partial Key Filtering Issue in Dynamics NAV and Business Central

Introduction to performance tuning in Dynamics NAV and Business Central