Database insights in C/AL and AL debuggers

  • Article

Important

This content is archived and is not being updated. For the latest documentation, see Microsoft Dynamics 365 product documentation. For the latest release plans, see Dynamics 365 and Microsoft Power Platform release plans.

Note

These release notes describe functionality that may not have been released yet. To see when this functionality is planned to release, please review What's new and planned for Dynamics 365 Business Central. Delivery timelines and projected functionality may change or may not ship (see Microsoft policy).

Debugging SQL behavior

Traditionally, debugging AL has been about examining behavior of the language runtime—for example, looking into the content of local variables at a breakpoint. As of the Business Central April 2019 release, the C/AL and AL debuggers also offer you the capability to examine the impact that your AL code has on the Business Central database. In the variables box of the debugger, expand the <Database statistics> 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, as well as insights into the most recent SQL statements executed by the server.

The following insights are part of the database statistics:
Current SQL latency (ms) When the debugger hits a breakpoint, the server will send a short SQL statement to the database and measure how long it takes. The value is shown in milliseconds.
Number of SQL Executes This shows the total number of SQL statements executed in the debugging session since the debugger was started.
Number of SQL Rows Read This shows the total number of rows read from the Business Central database in the debugging session since the debugger was started.

Database insights also make it possible for you to peek into the most recent and the latest long running SQL statements executed by the server. To get a list of these in debugger, expand either the <Last Executed SQL Statements> or the <Last Long Running SQL Statements> node.

The following insights are part of the SQL statement statistics:
Statement The SQL statement that the AL server sent to the Business Central database. You can copy this into other database tools, such as SQL Server Management Studio, for further analysis.
Execution time (UTC) The timestamp in UTC of when the SQL statement was executed. You can use this to infer whether the SQL statement was part of the AL code between the current and last breakpoint (if set).
Duration (ms) The duration in milliseconds of the total execution time of the SQL statement measured inside the Business Central Server.

You can use this to analyze if you are missing indexes (Business Central keys), or to experiment with performance of database partitioning and/or compression.
Approx. Rows Read This shows the approximate number of rows read from the Business Central database by the SQL statement.

You can use this to analyze whether you are missing filters.

The number of SQL statements tracked by the debugger can be configured in the Business Central Server. The default value is 10.

Tell us what you think

Help us improve Dynamics 365 Business Central by discussing ideas, providing suggestions, and giving feedback. Use the Business Central forum at https://aka.ms/businesscentralideas.