This is a continued blog from the one I wrote earlier today about Multiple Active Result Sets (MARS) causing locks on SQL Server.
In this blog I will show what happens on SQL Server if you keep result sets open during long transactions and we will discuss a possible workaround that I need your feedback on.
How to reproduce this?
Together with the project team we’ve reproduced this issue on the latest versions of Business Central, also with a relatively small database.
The examples shown in this blog are from a Business Central 14 system running on a SQL Azure database scaled as General Purpose – Serverless: Gen5, 1 vCore.
The SQL Statements to display the contents of the Active Result Sets are the IP of someone else, but I was given permission to share them on Yammer where most Business Central partners have access.
The database size used here is 77GB with Page Compression and the G/L Entry table has 14.641.928 (14.5 million) rows.
The code examples can be found on my GitHub.
The code that we will execute is the following:
var GLAcc: Record "G/L Account"; GLEnt: Record "G/L Entry"; begin GLEnt.SetRange(Amount, 100, 110); GLEnt.FindSet(); repeat GLAcc.SetRange("No.", GLEnt."G/L Account No."); GLAcc.FindSet(); Sleep(1000); until GLEnt.Next() = 0; Message('Finished'); end;
We create a loop searching for G/L Entries and for each entry we read the G/L Account and pauze for 1 second.
Imagine in real life, this is a business process. For example calculating Item Values for each Item Ledger Entry posted.
If you look at SQL Server you will see that it is keeping the dataset in memory for the Service tier to fetch the next 50 records.
SQL Server will keep the dataset in memory and try to manage it as long as the transaction takes.
I am very reluctant to call this a fix but here is what we tried:
var GLAcc: Record "G/L Account"; GLEnt: Record "G/L Entry"; GLEntTemp: Record "G/L Entry" temporary; begin GLEnt.SetRange(Amount, 100, 110); GLEnt.FindSet(); repeat GLEntTemp := GLEnt; GLEntTemp.Insert(); until GLEnt.Next() = 0; GLEntTemp.FindSet(); repeat GLAcc.SetRange("No.", GLEnt."G/L Account No."); GLAcc.FindSet(); Sleep(1000); until GLEntTemp.Next() = 0; Message('Finished'); end;
You can see that we put the first result set in a buffer table, in memory of the service tier. This will allow SQL Server to close the result set and the client to continue the process.
The Performance WIN!
In the tests that we’ve executed we noticed the process to be up until 2.5 or 3 times faster just by closing the dataset and reduce the pressure on SQL Server.
Business Central SAAS
This is what the difference is between MARS and Temporary Tables on Business Central SAAS
How about the Service Tier?
We debated how this would move the problem from the SQL Server to the Service Tier.
Based on the blog from Erik Haugaard we also tried a List object and a Dictionary object. This was however, slower than using a temporary table…
And what about the locking?
That will be discussed in the third and final part of this blog series…