Microsoft Dynamics 365 Business Central – AL Codes impact on DB Performance – New datatype learning

Scenarios:

New BC developers or learners usually find it difficult to understand how their code development or customization are performing. Any logic in AL Code can be achieved but these new application logics (or codes) can be performance impacting. How to test or trace them?

Applicable to: Business Central (application version 4.0 or above)

Solutions:

• There is a performance toolkit provided by BC on its setup. Such toolkit uses a new datatype introduced from application version 4.0. Datatype is SessionInformation.

https://docs.microsoft.com/en-us/dynamics365/business-central/dev-itpro/developer/methods-auto/sessioninformation/sessioninformation-data-type

Details about sessioninformation on Microsoft’s portal

• Now, coming to datatype’s details. There are two methods provided (I reckon there should be more methods in future and then dev team don’t need to check more details on SQL Profilers or monitor on SQL on SaaS.
• Methods:
  • SqlStatementsExecuted – This is number of SQL statements that were executed during the sessions. Good! Now we now why certain users are in trouble or getting locks when they are testing new functionality in BC. You can track it by putting these details in Telemetry on your apps or use Microsoft BC’s Performance Toolkit.
  • SqlRowsRead – Similar to above method, this method returns SQL rows read from starting of session.
• If you are experienced person, you would / should have one question:

I see the same details during debugging, why use this datatype?

Although, while writing this blog, I also understand that you would avoid debugging on customer’s environment where there are real time data and process occurring.

• Above SQL statistics can be also seen while debugging. This is during development phase of project, although you may not have guarantee when real business data comes on your Apps.

Examples:

• There are good, self-explanatory on Microsoft’s portal and its usage is already in BC Performance Toolkit. Although, still the developers who need to use this below are some examples:

var
    SqlRowsRead := BigText;

begin

    SqlRowsRead := SessionInformation.SqlRowsRead();
    Message(Format(SqlRowsRead));
end;

var
    SqlStatementsExecuted : BigText;

begin

    SqlStatementsExecuted := SessionInformation.SqlStatementsExecuted();
    Message(Format(SqlStatementsExecuted));
end;

Real time usage in BC, is Microsoft Business Central Performance Toolkit. You can check on codeunit 'BCPT Line' which has major codes based on this datatype.

References:

• https://docs.microsoft.com/en-us/dynamics365/business-central/dev-itpro/developer/methods-auto/sessioninformation/sessioninformation-sqlrowsread-method
• https://docs.microsoft.com/en-us/dynamics365/business-central/dev-itpro/developer/methods-auto/sessioninformation/sessioninformation-sqlstatementsexecuted-method