When Business Central On-Premises is sluggish: The 3 most common SQL performance killers (and how to isolate them)
It's the classic nightmare scenario for any IT administrator: The accounting department calls because the crucial month-end closing is stalled. At the same time, warehouse staff complain that order picking in Microsoft Dynamics 365 Business Central (or Dynamics NAV) is painfully slow.
A glance at the Windows server reveals that the SQL server's CPU utilization is nearing 100 percent.
Now the infamous guessing game begins. The ERP support specialist blames the database configuration. The DBA, on the other hand, is convinced that poorly written AL application code is crippling the system.
If you're regularly putting out fires in your on-premises infrastructure instead of taking proactive measures, it's usually due to the same three recurring performance killers. Learn here what they are and how to find the root cause.
Killer 1: Lock Contentions & Deadlocks (table locks)
Business Central is based on a relational SQL database. When a user initiates a large posting run, SQL locks the affected table rows to ensure data integrity.
The problem: If another user tries to modify a job in that row at the exact same moment, they have to wait. If this lock lasts too long, or if two processes block each other (deadlock), the transaction terminates with an error message.
Manual search: You need to configure SQL Server Extended Events or query the system view `sys.dm_tran_locks` while the problem is occurring.
Killer 2: Missing indices and “Missing Index Inflation”
Business Central automatically generates SQL queries from the AL code using its Object-Relational Mapper (ORM). When new table fields are added via custom extensions, the corresponding indexes are often missing at the SQL level.
As a result, the SQL Server has to scan the entire table for every query (table scan) instead of accessing the data directly. This massively increases the I/O load on the server's hard drives and CPU utilization.
Manual search: A look at the Dynamic Management Views (sys.dm_db_missing_index_details) shows you which indexes the SQL Server is sorely missing.
Killer 3: Outdated statistics and the "parameter sniffing" effect
The SQL query optimizer uses statistics to determine the fastest execution plan for a query. If data volumes in Business Central change rapidly (e.g., due to a high volume of orders on Black Friday), the old statistics are no longer accurate. The SQL Server selects an inefficient plan, and queries that took 100 milliseconds yesterday suddenly take 30 seconds.
The problem: The black box syndrome in closed networks
Manually searching for these errors using SQL Server Management Studio (SSMS) is extremely time-consuming and requires in-depth DBA expertise. To make matters worse, in highly secure on-premises or air-gapped environments (such as in the financial sector or manufacturing), sending telemetry data to an external cloud for analysis is unacceptable.
Administrators need a solution that operates 100% locally and eliminates the guesswork.
The abbreviation: Proactive monitoring instead of reactive "firefighting"
This is precisely where our The new V8 Monitor addresses precisely this critical interface . comes in. Developed as a purely on-premises solution with a "no call-home" policy, the tool monitors your infrastructure completely isolated within your own network.
Troubleshooting in seconds: If a system encounters a problem, the tool isolates the blocking SQL query and immediately correlates it with the affected Business Central service instance and the AL code line.
Take the free test:
Would you like to know which undiscovered bottlenecks are currently slowing down your ERP infrastructure? We offer you a completely free and non-binding performance audit for 14 days. Installation takes approximately 30 minutes and requires no internet connection.
We will be happy to answer any further questions on this topic personally.
Your dynamicsproject.com Team