Dynamics 365 Business Central: Get the amount of SQL rows read and the amount of SQL statements executed on the session via AL – SessionInformation Data Type

Hi, Readers.
Today I want to share a mini tip about Business Central. How to get the amount of SQL rows read and the amount of SQL statements executed on the session via AL.

As you might, As of Business Central April 2019, the AL debugger also offers the capability to examine your AL code’s effect on the Business Central database.
In the VARIABLES pane in debugger, expand the node to get insights such as the current network latency between the Business Central Server and the Business Central database, the total number of SQL statements executed, the total number of rows read, and locks held, as well as insights into the most recent SQL statements executed by the server. The following insights are part of the database statistics:

Insight	Description
Current SQL latency (ms)	When the debugger hits a breakpoint, the Business Central Server will send a short SQL statement to the database, and measures the time it takes. The value is in milliseconds.
Number of SQL Executes	This number shows the total number of SQL statements executed in the debugging session since the debugger was started.
Number of SQL Rows Read	This number shows the total number of rows read from the Business Central database in the debugging session since the debugger was started.

In fact, we can also get Number of SQL Executes and Number of SQL Rows Read in AL. This time we just need to use SessionInformation Data Type.

SessionInformation Data Type: Is a complex data type for exposing Session information into AL.

Version: Available or changed with runtime version 4.0. (BC15 2019 release wave 2)

The following methods are available on the SessionInformation data type.

Method name	Description
SqlRowsRead()	Gets the amount of SQL rows read on the session, since the session started.
SqlStatementsExecuted()	Gets the amount of SQL statements executed on the session, since the session started.

Their return value is a BigInteger type.

Let me do a simple test.

Souce Code:

pageextension 50123 ZYCustomerListExt extends "Customer List"
{
    trigger OnOpenPage()
    var
        Msg: Label '\Number of SQL Executes: %1 \\ Number of SQL Rows Read: %2';
    begin
        Message(Msg, SessionInformation.SqlStatementsExecuted, SessionInformation.SqlRowsRead);
    end;
}

Very simple, give it a try!!!😁

END

Hope this will help.

Thanks for reading.

ZHU