How We Diagnosed a Business Central System Outage in Under an Hour

When a Business Central customer's web service — which interfaces with their e-commerce website — recently climbed to 100% CPU and stayed there, the result was inevitable: users across sales and warehousing were unable to work. The BC system was effectively down.

Their IT team's first instinct was sensible: add more CPU to the service tier. It helped briefly, then the system hit 100% again.

By the time the case reached our senior performance tuning consultant, the customer's team had been working on it for hours. They had a theory — a recent deployment of website enhancements and new Business Central integrations was somehow responsible — but no evidence and no way to pinpoint the cause.

The problem, we discovered, was partial key filtering on composite primary key tables. But the diagnosis was far from obvious given the assumptions most BC deployment teams make related to testing and validating their solutions.

Understanding partial key filtering

Several Business Central tables — Sales Header, Sales Line, Sales Header Archive and Sales Line Archive — have composite primary keys. The primary key of the Sales Header table, for example, is defined as Document Type, No. — two fields, in that order.

When AL code filters a table with a composite primary key, it must include the leading key field. If only the secondary field is used as a filter — in this case No. — SQL Server cannot use the primary key index. It performs a full table scan instead, reading every row in the table to find the matching records.

On small tables this is barely noticeable. On a live Business Central system with years of transactional data in Sales Header Archive and Sales Line Archive, a table scan on every web service call is catastrophic.

The AL code — before the fix

This was the procedure being called from the e-commerce web service on every order request:

  Clear(SalesHeader);
  // Missing: Document Type filter on all four tables
  SalesHeader.SetRange("No.", pOrderNumber);
  if SalesHeader.FindSet() then
  repeat
    // Rest of the code
  until SalesHeader.Next() = 0;

Functionally, this code is correct — it finds the right records. Performance-wise, it forces a full table scan on four tables on every single call from the web service.

Why this caused a complete system outage

A table scan on a busy system is harmful on its own. What made this incident catastrophic was a combination of two factors happening simultaneously.