I've been facing constant data I/O throttling issues with my Azure SQL Database, even though I'm paying for 8 vcores in an elastic pool, which costs about $1600 monthly. The settings allow me to allocate all the vcores to a single database, and I usually run my tests on just one DB during off-hours to pinpoint the issues. However, I'm consistently hitting I/O throttling at around 2000 IOPS, which feels quite low. On-premises, we never dealt with these kinds of disk throttling issues, and I expected CPU throttling before disk in the cloud environment. I'm particularly frustrated because the query optimizer seems oblivious to my throttling, often selecting plans that don't consider my actual disk bandwidth. I can mitigate this with hints or tweaking joins, but it's still a hassle. How should I approach this disk throttling in cloud databases, and is there a way to improve the situation?
5 Answers
Have you checked if your elastic pool supports General Purpose V2? In that version, you can set the IOPS separately, which might give you better performance.
What tier are you using? If it's General Purpose, that could explain the limits you're facing. The log I/O caps can be pretty low, especially at 50MiB/s for General Purpose compared to Business Critical at 96MiB/s. Each offers different IOPS depending on your cores—like 2500 IOPS for General Purpose. You might benefit from looking at the specific SKU docs for more insights.
I totally feel your pain! Transitioning from on-prem to the cloud has its challenges, especially with resource management. It's tough to adjust expectations, but many have successfully optimized I/O by tweaking queries and configs.
We've dealt with similar issues and switched to the hyperscale elastic pools. They have significantly better I/O performance at a reasonable price, although it caps at 25 databases. It could be worth considering that option!
You might want to look at the kinds of operations you're doing. If you're running operations that involve deleting or bulk inserting 10k rows across multiple sessions, it can definitely strain the I/O. These operations can be heavy and might be hitting those limits pretty quickly. Have you considered optimizing further, like reducing the number of indexes or not validating foreign keys during inserts? That approach worked well for us on-prem, but seems tougher in the cloud.
Exactly! And I was curious about how you handle disk throttling in cloud databases since the IOPS limits feel shockingly low compared to what you might expect with 8 vcores.
We're on 8 vcores in General Purpose. That makes sense! Seems like they designed it this way to push customers towards the Business Critical tier.