Finding information in AL Code

Last updated 11 November 2020.

Finding information in AL Code

IMPORTANT pre-requisites:

Only a system administrator/local host administrator is able to perform all checks or functions.

DISCLAIMER:
This code and information are provided "AS IS" without warranty of any kind, either expressed or implied. Furthermore, the author or Olav Treffurt IT Consulting shall not be liable for any damages you may sustain by using this information, whether direct, indirect, special, incidental or consequential, even if it has been advised of the possibility of such damages.

Search for references in the Dynamics C/AL code

Problem
SQL Server is able to handle requests from a large number of concurrent users. If SQL Server handles requests from many clients, there is a high probability that conflicts will occur because different processes are requesting access to the same resources simultaneously. A conflict in which one process is waiting for another to release a resource is called a block. Although in SQL Server, a blocked process typically resolves itself when the first process releases the resource, a process might hold a transaction lock and not release it.

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

Pos.

Description

Version

"SQL Server: Extended Events"

The intention of the Extended Events installed by V8 is to present all 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 the basis for code analysis in V8 Search XE. In the V8 XE Profiler, SQL queries will be recorded as they are created by the Dynamics Business Central / NAV object.

V8 Extended Events:

blocked_process -> Table locks and deadlock
long_duration -> Long-running SQL queries in Business Central/NAV
V8_User_NAV_Trace -> It allows you to display SQL queries for all statements issued by the C/AL code.
These are stored in the V8 XE Profiler are displayed as comments. In this session, the name of the Windows user is logged with the SPID of the SQL Server.
/*
Get connection from the pool.
User: ComputerName\WindowsUsername
*/
V8_FullSQL_NAV_Trace -> The session allows SQL queries to be displayed for all statements issued by the C/AL code. These are displayed as comments in the V8 XE Profiler. In this session all actions for a transaction of the SQL Server are collected.

2.0

"SQL Server: Dynamic Management Views (DMV)"

“DMVs” are query structures built into SQL Server that deliver details about server and database health/performance. DMVs provide a common mechanism to extract “all things SQL” as well as Windows OS performance data. There are multiple DMV categories that return configuration information and performance data.

2.0

"Windows Trace Events: SQL Trace Events"

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

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

There is also the possibility to collect single event ID.

For more information, see Settings Service - V8 Search XE - Product Documentation.html.

The following table lists the SQL trace events.

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.
6	ExecuteReader/Stop	SQL statements that return a set of rows from a database table.
7	ReadNextResult/Start	SQL statements that return the next result from a database query.
8	ReadNextResult/Stop	SQL statements that return the next result from a database query.
9	ReadNextRow/Start	SQL statements that return the next row in database table.
10	ReadNextRow/Stop	SQL statements that return the next row in database table.
11	BeginTransaction/Start	SQL statements that start a database transaction.
12	BeginTransaction/Stop	SQL statements that start a database transaction.
13	Prepare/Start	SQL statements that create a prepared version of the command on an instance of SQL Server.
14	Prepare/Stop	SQL statements that create a prepared version of the command on an instance of SQL Server.
15	OpenConnection/Start	SQL statements that open connection to the database from the connection pool.
16	OpenConnection/Stop	SQL statements that open connection to the database from the connection pool.
17	Commit/Start	SQL statements that commit a database transaction.
18	Commit/Stop	SQL statements that commit a database transaction.
19	Rollback/Start	SQL statements that cancel the changes in a pending database transaction.
20	Rollback/Stop	SQL statements that cancel the changes in a pending database transaction.

The SQL trace event the data is stored in the SQL "V8 ETW Log Viewer" table in the V8 Search XE database.

For more information, see Event Trace Data.

2.0

"Business Central/NAV Server: C/AL tracing"

Since the Microsoft Dynamics NAV 2013 version, the server has a function that enables you to display the AL call stack for SQL commands. Full SQL Trace enables / disables tracing for all new and existing sessions per Dynamics Server instance. It allows you to view SQL queries for all statements issued by the AL. All SQL statements between consecutive 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 problems from different clients on the same SQL connection. The SQL statement that matches these comments is issued by Microsoft Dynamics NAV Server but is not issued by AL. Comments that contain only the user name also correspond to SQL statements issued by Microsoft Dynamics NAV Server but not by AL. For example, Microsoft Dynamics NAV Server executes queries to calculate calculated fields that are displayed in the fact fields. These types of comments are required because Microsoft Dynamics NAV Server might execute a SQL query without reconnecting to the pool and are not from AL.

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

Collect and analyze data
V8 Search XE stores two types of data:

1. After the SQL trace is captured, the data is stored in the SQL table.
The trace was saved in the table "V8 XEvents Full SQL Trace" in the V8 Search XE database.
The data must be imported using the function Dashboard -> System -> Extended Events Import XE -> Import Full SQL Trace XEvents.

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

2.0

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

Using the "V8 Source Code Search" module, you can search your entire Dynamics NAV/BC code base to find the locations where specific code elements are referenced.

Requirements

Installing

Licensing

Configuring V8 client

Configuring V8 services

SQL Server instance for V8

General

SQL Server

SQL Database

System

Dashboard

Ribbon UI

TSQL Analyze

XE Profiler

DockPanels

Dialogs

Analysis graph

Dynamics NAV/BC

Troubleshooting

Release notes and
other versions

Finding information in AL Code

Finding information in AL Code

Search for references in the Dynamics C/AL code

Requirements

Installing

Licensing

Configuring V8 client

Configuring V8 services

SQL Server instance for V8

General

SQL Server

SQL Database

System

Dashboard

Ribbon UI

TSQL Analyze

XE Profiler

DockPanels

Dialogs

Analysis graph

Dynamics NAV/BC

Troubleshooting

Release notes and other versions

Finding information in AL Code

Finding information in AL Code

Search for references in the Dynamics C/AL code

Release notes and
other versions