High Physical Reads/Temp DB utilization On Azure SQL DB VS On Premise DB

Question

I have an application which performance is fine on premises SQL Server. Same DB is hosted on Azure (business critical 16 Cores) but its performance is not as good as on premise when executed using JMeter performance test suite.

I figured out using query store that physical reads on Azure DB is much higher then on premise DB. Though Azure DB is having more memory then on premise DB. temp DB utilization is also quite high on Azure DB.

Execution plan is same and I have updated stats as well. Database configuration is mostly same.

OP Execution Plan - https://www.brentozar.com/pastetheplan/?id=rJQ_ox39D

Azure DB Plan - https://www.brentozar.com/pastetheplan/?id=ry1Sieh5D

Just to clarify, application is sending millions of small queries to server that's why its difficult to pin point any one particular query.

Answer 1

Your 16 cores are physical and dedicated to your SQL Server. They might be idle most of the time. If hyper-threading is enabled, you might have 32 logical cores for added compute capacity for some workloads. You might feel guilty for allocating the money for resources you don't need, but it is better to have more than less because more could require a new server.

An Azure host might have 32 cores with hyper-threaded enabled. The 64 logical cores are not going to be idle. You get 16 vCPUS. If these are provided by 8 hyper-threaded physical cores, I would expect a performance drop of the 20-30% you see for most loads. For light loads you will not notice much difference. For heavy loads, it might be much worse. You might regret not spending the money for resources you need, but it's easy to fix.

Forget apples and oranges. How many cats does it take to run a 16 dog process?

I think v2 is from the time a core was a core.

https://learn.microsoft.com/en-us/azure/virtual-machines/sizes-memory?toc=/azure/virtual-machines/linux/toc.json&bc=/azure/virtual-machines/linux/breadcrumb/toc.json