1

This is moved from the programming site:

Part of performance testing in azure, I have a query that creates 400 transactions, each transaction running an update on a table. Running this query on the development server on premise takes around 100 ms, versus over 500 ms on Azure. I ran some statistics and RESERVED_MEMORY_ALLOCATION_EXT came up higher in azure than in dev, here is the wait stats I got https://i.stack.imgur.com/H9Vhx.jpg.

I ran the same query using one transaction and 100/400/800 rows updated in that transaction and that is faster in Azure. Basically running 1 transaction that updates 100/400/800 rows or 10 transactions with 100/400/800 rows is faster in Azure. Running 100/400/800 transaction with one row updated or 10 rows updated in that transaction is slower in Azure.

We are using a SQL cluster on an Azure VM. The storage solution for the log files in dev is Dell PowerVault ME4024. Disks were just upgraded to the fastest Azure can provide. I also tried turning on the legacy cardinality estimator and there was no change. I am trying to get an actual execution plan but first time I tried the query ran for 30 minutes and then SSMS crashed.

Any suggestions on how to troubleshoot further?

2
  • According to your earlier paragraphs, your tests on Azure finish running. So what query are you now stuck on trying to get an execution plan for?
    – J.D.
    Nov 6, 2021 at 4:07
  • 2
    If you're running 400 update statements, that's 400 execution plans, and it's no surprise SSMS is crashing. How many tables are you updating? Is it 400 single row updates to the same table? Can you change your query to do one update per table?
    – AMtwo
    Nov 6, 2021 at 11:58

1 Answer 1

2

I ran the same query using one transaction and 100/400/800 rows updated in that transaction and that is faster in Azure. Basically running 1 transaction that updates 100/400/800 rows or 10 transactions with 100/400/800 rows is faster in Azure. Running 100/400/800 transaction with one row updated or 10 rows updated in that transaction is slower in Azure.

Unless you application (SSMS in this case) is in a VM or a service already in Azure, you're going to get a round trip penalty for each submitted batch due to physics (as in it takes time to send data across the internet and then come back and we haven't broken any laws of physics that allow data to move faster). This is why submitted a single batch is faster than submitting 100 individual batches, in terms of overall time.

If you run your tests again from a VM in the same region with SSMS, you should see similar performance. If your applications aren't going to be migrated then any cloud will be piss poor in terms of performance against a server that has good infrastructure and sitting in the same datacenter. You either go all in on cloud or you take the performance hit and pay a bunch more money for the benefit of being in the cloud.

Once you remove the network latency there will still be differences but it should be much more minimal than current differences (assuming like hardware and performance limits between environments).

3
  • In SSMS turn on Query-->Include client statistics. Look at time statistics. Nov 6, 2021 at 18:07
  • What we do in that script is something like this: get current date; do a while until we reach 400; execute a transaction with an update inside that while; at the end grab current date and do a datediff with a millisecond datepart and return it; So the difference can't be the network latency since we grab the date on the server. Nov 8, 2021 at 18:58
  • I ran the query on both servers but with just one transaction. The execution plans seems identical, except for the times it took to run the first part of the query, which is way longer in Azure. This is the dev query brentozar.com/pastetheplan/?id=ByaukQPPK and this is the Azure one brentozar.com/pastetheplan/?id=S124gQPDF Nov 8, 2021 at 22:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.