If you ask a random Microsoft partner about their worries with Business Central there is a fair chance “Performance” is in their top three.
It probably depends if this partner has a background with NAV. If this is the case it’s a guarantee it’s in the list.
As long as I remember I’ve been doing some form of performance tuning. First with the old Native Navision database, later with SQL Server and now on Azure SQL.
It’s not really a surprise that when I joined QBS a few weeks ago it was just a matter of time before the first performance cases started rolling my way.
Performance Tuning in the cloud is a different animal, that’s for sure, but the essence of the job does not change. Get as much telemetry as you can and deduct root cause from this.
Microsoft to the Rescue
If you have a history with NAV that goes back more than ten years you’ll probably remember that the first versions were not exactly efficiently leveraging SQL Server.
This is probably the reason why a lot of legacy NAV partners are affraid of the cloud because they think they loose control over what they perceive as a monster that they had just contained.
Fortunately this is not correct and Business Central in basic has a very good performance in the cloud and Microsoft offers a lot of ways to get telemetry.
The teams in Lyngby and Fargo have done a tremendous job in the last years to bridge the gap between Business Central and Azure SQL and this is helped by the fact that C/Side is no longer there and is not holding the team back when it comes to innovation.
Also Azure SQL has come a long way and offers insights that back in the days we could only dream of.
I got a lot of help from Kennie Pontoppidan in the last period where he guided me through all the documentation and sample dashboards that Microsoft has published and in exchange I promissed him to write a blog about what I’ve learned.
A Blog Series!
Because it is simply too much to put into one blog it will be a series covering different topics and while writing the series I will probably learn even more along the way.
I’ve always found that writing blogs helps organising my thoughts while documenting your learnings. It’s quite frequent that I end up on my own blog via Google if I run into a problem I have solved before in the past.
Azure vs. The Hardware Stack
This entire series will be about Business Central hosted in the cloud running on SQL Azure, either controlled by Microsoft (BC Saas) or on an instance you host yourself.
The latter would not be something I’d recommend unless you have a valid excuse. Setting up an environment on Azure for Business Central is not exactly super easy and then you don’t get things like CDS intergration for free.
Running on Azure essentially elliminates the ever lasting discussion about how the hardware is setup and how the virtualisation layer (most often VMWare) is configured.
This was always the biggest nightmare in the past.
Azure makes things a lot easier and it’s even easier if you just select the “Hosted by Microsoft” option and don’t have to worry about it.
NOTE: Microsoft calls everything that is not managed by them “OnPrem”. Even if you run on your own Azure SQL database. This can be confusing.
It’s all about Application Insights
Performance tuning is about telemetry and understanding the application. While the latter takes time, the telemetry is now easily available for everyone.
We’ll spend a lot of time in the blog series on what you can do with Azure Application Insights and how to set it up with examples and best practices.
I’ll also spend some time on showing what you can get out of the Query Store that you get with every Azure SQL Database. Currently this is only for instances that you host yourself but who knows what we will get in the future.
Start Reading and Learning today
There is a lot of information already out there and I’ll probably not cover anything new in my blog other than the pitfalls I ran into, which were quite a few so I expect things to be worth the time and effort.
Here is a list of resources
https://aka.ms/bctelemetrysamples
Using SQL Query Store
Marije Brummel Blog | Business Central, Azure and more... 
There are several perfomance issues which could be solved:
1. Slow document List-pages are often caused by non optimal FlowField Sums in Document headers. Changing the FlowField to sum only relevant records (Records with Type “” and No. ”) speeds up Posted Sales Invoices List from minutes to open to milliseconds for our customers (depends on usage. Customers using much text in Documents will have a benefit)
2. Avoid using Flags to check if you have to do something f.e.”Recalculate Invoice Disc.” in document lines. If you try to understand the code (which s realy diffcult), you see that the Flag is often set without requrement to be shure that discount is recalculated. Removing the complete Code (codeunit 56,66) and optimizing codeunit 50 and 60 has no problems with 150 document lines and more to calculate online Invoice discount correctly and not only 10 and wrong, which was not realy usefull for our customers.
3. Avoid complex code, this is in most cases the cause for a non performat solution. But this requires knowledge and time
LikeLiked by 1 person
I cant wait to see more of this series. I want my engineering group to switch our solutions to Azure. We’re hosting our software epaCUBE in the Amazon cloud right now but we’re losing customers left and right for performance issues and also because of mistakes in how the software calculates based on product rankings that make it almost unusable. I think Azure might be the way to get people excited again. It is hard to run so many queries and if I had it to do all over again I wouldn’t have built the software the way we did, but water under the bridge. Our company founder Gary had a great vision and in hindsight we should have stuck with his plan but live and learn. Thanks for this series.
LikeLike