SQL Server Performance for Dynamics Business Central

SQL Server Performance for Dynamics Business Central. Dynamics NAV is now Business Central and then at some point came the frustration...

Some time ago we had an inquiry about the known issues in Business Central / NAV. The SQL Server Performance for Dynamics Business Central / NAV would be no better than the updated NAV version 2013. Daily many table locks, frustrated users and an IT partner that is a bit ratless.

Our offer to use V8 Care to analyze the "ACTUAL" state of the SQL Server and Dynamics Business Central solution and collect the database bottlenecks. True to the new motto "Data is the gold of the 21st century".
To our surprise, we got the answer: "Data to analyze Dynamics Business Central problems had been collected enough." Since the prospect did not have in-house IT experts in the company, he uses specialized IT service providers to support SQL Server and Dynamics Business Central.
True, we live in a world where algorithms make the decisions in many areas about what should happen when a certain outcome occurs or a data boundary is crossed. But in many application scenarios, the human being is and remains the final authority.

Our question, what kind of data was collected? With which SQL Server monitoring tool? There are fanciful real-time SQL Server monitoring tools. Unfortunately, the SQL monitoring tools do not provide information about the Dynamics AL code.

Thus, when it comes to monitoring IT security systems, the IT admin or chief security officer is ultimately the one who makes the decisions.

For this reason, we would like to briefly discuss the possibilities offered by our analysis tool V8 Search XE .

The problem:

SQL Server is capable of handling queries from a large number of concurrent users. When SQL Server handles requests from many clients, there is a high probability that conflicts will occur because different processes will request access to the same resources at the same time. A conflict where one process is waiting for another process to release a resource is called a block. Although in SQL Server a blocked process usually resolves itself when the first process releases the resource, a process can hold a transaction lock and not release it.

Our solution:

To unblock a blocked process from Dynamics NAV / Business Central, we must first determine which process is the blocking process. And then, if possible, analyze and optimize the Dynamics NAV/BC blocking process.
In V8 Search XE, there are many different ways to identify a blocking and unblocking process, which are listed below:

1. SQL Server: Extended Events
2. SQL Server: Dynamic Management Views (DMV)
3. Windows Trace Events: SQL Trace Events
4. Business Central/NAV Server: C/AL tracing

 SQL Server: Extended Events

The goal of the Extended Events installed by V8 is to present all the information collected by the Extended Events sessions in a readable form. The SQL queries that exceed the specified threshold (we recommend starting with 10 seconds) are recorded and collected in different Extended Events sessions. The SQL scripts are used as a basis for code analysis in V8 Search XE. In V8 XE Profiler, SQL queries are recorded when they are created by the Dynamics Business Central / NAV object.

V8 Extended Events sessions:
1. blocked_process -> Table locks and deadlocks
2. long_duration -> Long running SQL queries in Business Central/NAV
. 3. v8_user_NAV_Trace -> This session allows you to view SQL queries for all statements issued by the C/AL code (Windows user name with SQL Server SPID). These are stored in the V8 XE profiler and displayed as comments.
/*
Get connection from the pool.
User: ComputerName\WindowsUsername
*/
4. V8_FullSQL_NAV_Trace -> The session allows SQL queries to be displayed for all statements issued by the C/AL code. These are collected and displayed as comments for a complete transaction in the V8 XE profiler of the SQL server.

 SQL Server: Dynamic Management Views (DMV)

"DMVs" are query structures built into SQL Server that provide details about the health and performance of servers and databases. DMVs provide a common mechanism for extracting "all things SQL" as well as Windows operating system performance data. There are several DMV categories that return configuration information and performance data.

 Windows Trace Events: SQL Trace Events

SQL Trace events track a specific set of SQL statements executed by the Business Central Server instance against the Business Central/NAV database on SQL Server.
The event data collected includes: session ID, tenant ID, the Business Central/NAV user, and the SQL statement. The listing is only the SQL portion of the Business Central Server trace events.

Important!
To collect this data, you need one V8 service per Business Central Server instance. This feature is available from version 6 of V8 Services and requires .NET Framework 4.7.2, which is not installed by default on older Dynamics NAV.

It is also possible to collect individual event IDs.

The following table lists a few Dynamics SQL trace events. For example:

IDEvent (task/opcode)What is traced
1ExecuteScalar/StartSQL statements that query a database table and return a single field from a row in the query result.
2ExecuteScalar/StopSQL statements that query a database table and return a single field from a row in the query result.
3ExecuteNonQuery/StartSQL statements that return a number of rows from a database table
4ExecuteNonQuery/StopSQL statements that return a number of rows from a database table
5ExecuteReader/StartSQL statements that return a set of rows from a database table.

This is what the SQL trace event data stored in the SQL table "V8 ETW Log Viewer" in the V8 Search XE database looks like.

 Business Central/NAV Server: C/AL tracing

Since the Microsoft Dynamics NAV 2013 version, the server has a feature that allows you to view the AL call stack for SQL commands. Full SQL Trace enables/disables tracing for all new and existing sessions per Dynamics Server instance.

This allows you to display SQL queries for all statements issued by the AL. All SQL statements between successive comments correspond to the AL statement from the first comment.
These comments correspond to events when the connection is retrieved and returned to the Microsoft DynamicsNAV Server connection query. These comments are needed to separate SQL query issues from different clients on the same SQL connection. The SQL statement that matches these comments is issued by Microsoft Dynamics NAV Server, but not by AL. Comments containing only the user name also match SQL statements issued by Microsoft Dynamics NAV Server, but not by Dyanmics AL code.

For example, Microsoft Dynamics NAV Server runs queries to calculate calculated fields that are displayed in the fact fields. These types of comments are required because Microsoft Dynamics NAV Server may run an SQL query without reconnecting to the pool, and they do not originate from Dynamics AL.

Important! To collect this data, you need one V8 service per Business Central Server instance.

Collecting and analyzing data
V8 Search XE speichert zwei Arten von Daten:

After the SQL trace has been captured, the data is stored in the SQL table. The trace was saved in the "V8 XEvents Full SQL Trace" table in the V8 Search XE database.

2. the complete AL programming code of the respective objects. The data is stored in the "V8 Performance Profiler" table in the V8 Search XE database.

The V8 Source Code Search module allows you to search your entire Dynamics NAV/BC codebase to find where specific code elements are referenced.

Conclusion
This particular data, is very difficult to capture with a real-time SQL Server monitoring tool. You need to monitor all in the Business Central Server instance in parallel.

V8 Search XE is designed to allow all administrators and developers supporting a Dynamics NAV/BC ERP solution to make a statement about the weaknesses of the SQL commands generated by Dynamics Dynamics Business Central / NAV and to document them.

In any case, it pays to have the Dynamics NAV performance checked by a third party to find out in which condition the ERP system and the SQL server are.
Afterwards one can consider, which options one has for the further proceeding then to improve the SQL server performance for Dynamics Business Central.

We will be happy to personally answer any further questions you may have on this topic.
Your dynamicsproject.com Team