Did you know there is a very easy way to check if your core D365 database are performing OK. Use the tool ‘Run performance test‘.

There are no need to enable every test. Just focus on the insert of 1000 records.

What is shown is the number of milliseconds it takes to insert 1000 records. (You can go higher or lower to get better average). And remember to run it a few times to get a feeling of the average.

If your PROD performance on 1000 records is

less than 2000ms – You are good, and have great Azure SQL performance. I prefer to see 1000ms, but depending on load on your system

2000ms-3000ms – OK performance, but you should check that you don’t have AOS crashes resulting in SQL failovers. This is also the typical performance of a Tier-2 environment.

Above 3000ms – If it remains steadily above 3000, then something is probably wrong, and you should open a support case to have telemetry looked at.

You can also see the performance test in trace parser, and here is how it looks when doing 10.000 inserts in a OK performing PROD environment. exclusive average and 0.78ms/insert is quite ok.

The code executed is basically just a loop inserting some fields in a table named PerformenceCheckTable.

The reason why this test is OK, is because it is only measuring the core performance of the Azure SQL. There are no additional code, complex queries, index problems etc.

When I performance test, I first do this validation to check that the base performance is stable, and that I have a well functioning platform. If this is OK, then I can do deeper and analyse performance on specific functionality covering queries, indexes and algorithms.

One reason I have seen of why the core SQL performance is below “good-performance”, is when there are customized code or ISV that actually crash the AOS. If the crashes happens too often, it seams to me that some disaster recovery mechanism is kicking in and this results in a different Azure SQL SKU or in a different cluster, that may have a lower performance. (We don’t have full insights to this)

So “ping” test your inserts if you wonder if the underlying SQL platform is acting a bit slow.