Ok, so I have a series of AWS VM's, mostly using the z1d.3xlarge server class with built in SQL Server 2019, these servers each contain a NVMe of various size. (Not surprisingly) I've placed my SQL Server TempDB drive on this drive, works great, no issue.
Now I'm trying to do exactly the same thing on Azure, in this case I'm using the "Standard E4bds v5" class with a SQL Server 2022 dev licence, same deal, attached NVMe, high performance (verified by "atto" bench-marking it, it actually preforms better), SHOULD work fine, except it doesn't...
As an example, I have a query that does a large TempDB spill when run on either server (generates the same query plan on both):
AWS (Same exact plan)
Same query, same plan, same tempDB spill, on the AWS server, 3 minutes, on the Azure server 11 minutes. Why? well allot of messing around with various wait stats and perfmon, it appears when the TempDB spill occurs on the Azure server, it sort of sites at 25MB/per sec:
Where if you run the same thing on the AWS server you will see spikes up to 900MB/per sec. Looking at this I would believe that whatever is leading to the reduced IO performance of the TempDB drive is also leading to the extended run times. I've looked at the following factors:
CPU: Generally low on both server, less than 10% most of the time
Memory: This query only causes SQL server to take about 1GB or so, it's using very little memory, that's only 12% memory used on the server, I've disabled paging and enabled locked memory, no effect
Other Disk IO: Everything is a Premium SSD, benchmarks great, looks good in Perfmon, I can see in Resource Monitor most of the usage is focused on the "D" drive (tempDB):
Activity Monitor: Just shows a bunch of reads when it's running, small amount of BufferIO waits:
What is the mostly likely cause of this poor performance, what would bottleneck TempDB speed(in this case but not in other queries), how would I determine the difference between the two servers that is contributing to the query speed difference?
Per J.D's request I've posted below the CrystalMark's benchmark of the TempDB drives on each server
AWS Temp Drive Performance:
Azure Temp Drive Performance:
For some reason this benchmark shows poorer performance on the Azure drives vs the AWS one's (this is different result from the ATTO benchmark which showed the opposite). Maybe this is what is going on, I've been focused on the "Ebsv5 series" so far, I'm going to try getting a much larger server which in theory should grant me more "IOPS/MBps" on all drives(not sure if this affects the NVMe's...).
I will also benchmark a larger server and see if it performs better, I will post the results here.
Yeah, J.D. is right, "Standard_E16-8ads_v5" is way better take a look: