2

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):

Azure

enter image description here

AWS (Same exact plan)

enter image description here

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:

enter image description here

enter image description here

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:

enter image description here

enter image description here

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?

Update 1

Per J.D's request I've posted below the CrystalMark's benchmark of the TempDB drives on each server

AWS Temp Drive Performance:

enter image description here

Azure Temp Drive Performance:

enter image description here

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.

Update 2

Yeah, J.D. is right, "Standard_E16-8ads_v5" is way better take a look:

enter image description here

Improve this question
2
  • 2
    TBH, the cardinality estimates issue in your screenshot is quite severe and to take 3 minutes even on the AWS server is indicative of a problem with your query. You should look to fix the query which you can start by providing the execution plan via Paste The Plan. It does sound like you possibly have a disk issue too, but idk what's normal in Azure, and wouldn't be surprised otherwise. Can you confirm the disk speeds outside of SQL Server with a tool like CrystalDiskMark?
    – J.D.
    Feb 17 at 23:00
  • Your right, it not the best query, I have a 1000 others like this in the same DB, I picked this one because it best demonstrates the issue, what I'm trying to solve is the performance difference between the two server, both of them generate the same plan (I will post this), for some reason the Azure one runs way slower (on what appears to be faster hardware, I will post the suggested stats) that the real issue I'm trying to solve.
    – David Rogers
    Feb 20 at 16:09

1 Answer 1

Reset to default
1

The RND4K metric is going to be the most relevant to regular (OLTP) querying performance. The fact that the results are ~1/10th the speed of the AWS server is telling.

Also, the server you chose for Azure is actually provisioned significantly slower than the AWS one (1/3rd the CPUs and Memory):

Standard_E4bds_v5 vCPU: 4, Memory 32 GiB

I'm sure the bottlenecks in your query aren't solely disk bound, and probably CPU and Memory bound too. This could probably be confirmed if you added the actual execution plan via Paste The Plan.

Again these are problems that are fundamentally wrong with the query itself and / or how your database is architected (since you mentioned this is a recurring problem). Solving the software problem with your database architecture would probably allow your queries to run fine even on the lower hardware provisioned Azure server you've chosen.

Improve this answer
2
  • Let me split out the question on the query since it's certainly relevant but not central to this question: dba.stackexchange.com/questions/323830/…
    – David Rogers
    Feb 20 at 17:34
  • This is on point, the "RND4K" is very low for anything under a E16, I've tried other server classes with NVMe too and they're terrible, in fact that's how I landed on the "Ebdsv5" family, it seems to be the most equivalent server class, and I suppose looking at cost it does match, kinda of a bummer though, I wish I could just buy the IOPS and throughput like a AWS GP3 instead of having to upgrade the entire server to have 600GB's of tempDB and a bunch of other stats (which I definite don't use).
    – David Rogers
    Feb 20 at 17:41

Your Answer

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

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