Business Central Performance Tool. With the Business Central Performance Tool V8 Search XE you can increase the performance, analyze and improve the performance of your Dynamics Business Central or NAV solution.
A typical problem in the Dynamics world...
Some time ago we had an inquiry about the known problems in Business Central / NAV. The SQL Server performance for Dynamics Business Central / NAV would not be better than the updated NAV version 2013. Daily many table locks, frustrated users and an IT partner who is somewhat clueless.
Our offer...
Using V8 Care to analyze the "ACTUAL" state of the SQL Server and the Dynamics Business Central solution and to collect the bottlenecks in the database. True to the new motto "Data is the gold of the 21st century" . To our surprise, we got the answer: "Sufficient data for the analysis of the Dynamics Business Central problems had been collected." Since the interested party did not have its own IT experts in the company, he uses specialized IT service providers who use the SQL Server and Dynamics Business Central care for. It is true that we live in a world in which algorithms make the decisions in many areas about what has to happen if a certain result occurs or a data limit is exceeded. But in many application scenarios, people are and remain the final authority.
Our question, what kind of data was collected? With which SQL Server monitoring tool? There are fantastic real-time SQL Server monitoring tools. Unfortunately, SQL monitoring tools do not provide information about 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
5. Business Central/NAV Server: Business Central Telemetry Data
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 7 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:
| ID | Event (task/opcode) | What is traced |
|---|---|---|
| 1 | ExecuteScalar/Start | SQL statements that query a database table and return a single field from a row in the query result. |
| 2 | ExecuteScalar/Stop | SQL statements that query a database table and return a single field from a row in the query result. |
| 3 | ExecuteNonQuery/Start | SQL statements that return a number of rows from a database table |
| 4 | ExecuteNonQuery/Stop | SQL statements that return a number of rows from a database table |
| 5 | ExecuteReader/Start | SQL statements that return a set of rows from a database table. |
If you are using Dynamics Business Central version 18 or higher we recommend you to read this article Watch Business Central table locks live
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 view SQL queries for any statements issued by the AL. All SQL statements between consecutive comments correspond to the AL statement starting with the first comment. These comments correspond to events when the connection is retrieved and returned to the Microsoft Dynamics NAV 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 username also correspond to 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 offers two ways to collect event data from Business Central / NAV.
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.
Business Central/NAV Server: Business Central Telemetry Data
Business Central sends telemetry data for various activities and processes in environments and applications/extensions. Up to Business Central version 14 (Role Tailored Client) there was the very helpful function of “SQL Tracing for Microsoft Dynamics Business Central / NAV” (EnableFullALFunctionTracing = true). After Business Central was converted to the web client version (from version 15), the function in the Business Central server options has changed slightly.
Monitoring telemetry data gives you insight into the activity and overall health of your environments/apps, so you can diagnose issues and analyze operations that are impacting performance. The V8 service of V8 Search XE collects telemetry data for analysis and presentation to Business Central or Dynamics NAV on-premise versions.
V8 Service Telemetriedaten: Analyse- und Präsentationsdaten bis BC Version 14 (Role Tailored Client)
V8 Service Full AL Function Tracing: Analyse- und Präsentationsdaten bis BC Version 14 (Role Tailored Client)
V8 Service Telemetriedaten: Analyse- und Präsentationsdaten ab BC Version 15 (Web Client)
Zeigt, welche „SQL SERVER SPID“ in BC welche Session ID ist. Die Information ist für die Analyse von Performanceproblemen enorm wichtig!
SQL Statements von Business Central (sehr wichtig für Tabellensperren und Deadlocks)
The telemetry data is expanded by Microsoft with each new version of Business Central in order to provide more detailed information on any performance bottlenecks.
V8 Search XE can analyze and present telemetry data up to the latest version of Business Central.
Conclusion
This specific data, is very difficult to capture with a real-time SQL Server monitoring tool. You need to monitor all the Business Central Server instances in parallel.
V8 Search XE the Business Central Performance Tool is intended to enable 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 off to have the Dynamics NAV / Business Central performance checked by a third party to find out what condition the ERP system and the SQL server are in.
After that, you can consider what options you then have for further action to improve SQL Server performance for Dynamics Business Central.
We will be happy to answer any further questions you may have on this topic in person.
Your dynamicsproject.com Team
English 
German