Long Running SQL Queries

[Arthurvdv]

The Dynamics 365 Business Central Usage Analytics app reports some Long Running SQL Queries.

While it's running on the backup/replica database of Business Central, I'm quite confident it doesn't impact the general performance of Business Central.

If I look into an example the SQL query that is executed, it seems kinda straightforward.

SELECT TOP 1 NULL
FROM "SQLDATABASE".dbo."currentcompany$g_l entry$437dbf0e-84ff-417a-965d-ed2bb9650972" "17" WITH(readuncommitted)
WHERE  ( "17"."timestamp" > @0 )
OPTION(optimize FOR unknown, fast 50) 

What I could think of

• Somehow create a Index (Key) for the timestamp field on the table?
• Switch from the timestamp field (0) to the SystemModifiedAt (2000000003) field?
• Refactor the ADLSE Seek Data report?
• Apply the Record instance isolation level (while with the DataAccessIntent on ReadOnly already I'm not sure it this makes any difference
• ...

[Bertverbeek4PS]

Well creating a key on timestamp isn't possible if I'm correct and for every table that the user can select not sure if that is the best solution.

ALso setting the DataAccessIntent would make allot of difference. Because it is already reading it uncomitted.

Maybe using the SystemModifiedAt? But there is no key for that also.

[Bertverbeek4PS]

Thinking again. Maybe setting databaseaccessintent is a nice extra and look of it performed better.

[Arthurvdv]

While the report already has DataAccessIntent = ReadOnly the Dynamics 365 Business Central Usage Analytics app shows these as ReadWrite.

Database Access Intent List Default behavior show use the setting from the object.

Dynamics 365 Business Central Usage Analytics app Show these were executed with Database Access ReadWrite.

Not sure on what's going wrong here. Could be the RecordRef handling or the fact that is running inside a Session.StartSession()? No idea howto test or debug this.

report 82560 "ADLSE Seek Data"
{
    ProcessingOnly = true;
    DataAccessIntent = ReadOnly;

    dataset
    {
        dataitem(Number; Integer)
        {
            trigger OnAfterGetRecord()
            begin
                if OnlyCheckForExists then
                    Found := not CurrRecRef.IsEmpty()
                else
                    Found := CurrRecRef.FindSet(false);

                CurrReport.Break();
            end;
        }
    }
    ...