Need help with Azure SQL DB Plan , always 100% DTU

Question

Hi Currently working with Azure SQL DB standard S2 plan with 50 DTUS. This is a small database with minimal workload and have 3 tables with more than 500K records. Every time I run Incremental load or full load, app slows down for few tables and always reaches 100% DTU usage during the load.

I tried all the performance tuning options like Automatic tuning, rebuilding indexes and setting MaxDOP to 8.

Is going for upgrade the only option for these small data loads, Is VCore plan or DTU plan good for this scenario, please suggest , thank you

Answer 1

Hi @Rama Gilaka Thank you for posting your question on Microsoft Q&A and for using Azure services.

In standard service tier, the storage type will be the remote storage.

In Premium, customer data including data files, transaction log files, and tempdb files is stored on the local SSD storage of the machine hosting the database or elastic pool. Local SSD storage provides high IOPS and throughput, and low I/O latency.

As you have experienced the high IO issue, you can choose the premium service tier.

In general, 100 Standard DTU’s approx. equal to 1 vCore & 125 Premium DTU’s approx. equal to 1 vCore.

This document will give overview about the DTU – bases purchasing model: https://learn.microsoft.com/en-us/azure/azure-sql/database/service-tiers-dtu?view=azuresql

Resource limit using DTU purchasing model for single database: https://learn.microsoft.com/en-us/azure/azure-sql/database/resource-limits-dtu-single-databases?view=azuresql

Resource management in Azure SQL DB: https://learn.microsoft.com/en-us/azure/azure-sql/database/resource-limits-logical-server?view=azuresql

Resource limit for V-core model: https://learn.microsoft.com/en-us/azure/azure-sql/database/resource-limits-vcore-single-databases?view=azuresql

Azure SQL database calculator: https://azure.microsoft.com/en-in/pricing/details/azure-sql-database/single/

Regards,
Oury

Answer 2

Hi @Rama Gilaka ,

I would suggest to check what resources are utilized during the load operation. Please note that DTU model is mix of CPU, Memory and I/O.
vCore model offer dedicated vcores for your database. Please also note that from DTU model below S3 offer less than 1 vCore.The For CPU-intensive workloads, a service tier of S3 or greater is recommended.
https://learn.microsoft.com/pl-pl/azure/azure-sql/database/resource-limits-dtu-single-databases?view=azuresql

Answer 3

Ingesting data on Azure SQL is recommended to be performed in batches as explained here. This way you can have more control over DTU consumption.