Home Insights

Azure SQL DTU Calculation

Azure SQL DTU calculation: how to? | Arxus
blog

How do you calculate DTUs for your Azure SQL database?

In the world of cloud computing, Microsoft Azure has gradually emerged as one of the key hosting platforms for SQL databases. However, to ensure they perform optimally, you often face an additional challenge: choosing the right DTU level for your database migration. Curious about how we tackle this? Maarten Ballière, our Cloud Solutions Architect, explains it to you.

Mon, 29 April 2024

Maarten Ballière | Arxus

Maarten Ballière

Cloud Solutions Architect, Arxus

Azure SQL databases are fully managed relational storage units, offering a range of options for your business. To ensure peak performance and optimal resource utilization, Azure SQL databases operate with their own metric: Database Transaction Units (DTU).

DThuh?

Database Transaction Units, or simply DTUs, are a combined measure that give you a complete picture of the performance capacity of your Azure SQL Database. Microsoft designed these practical units to make it much easier for you to select the right performance level for your database. In short, DTUs cover three key resources: CPU, memory, and data I/O.

Why is a DTU calculation important?

Azure SQL databases offer various performance tiers, each with different DTU levels. Your workload and performance requirements determine which tier is most suitable for your database. But how do you know which one is the best fit for you? By conducting a nuanced DTU calculation based on three components:

  • CPU: The processing power your database requires to execute queries and transactions.
  • Memory: The amount of RAM you need to efficiently cache and process data.
  • Data-I/O: The input/output operations per second (IOPS). This is what you need to access and retrieve data from your storage.

By choosing the right DTU level, you can optimize performance without consuming too many resources. To assist you in this decision, Microsoft has designed a handy Azure SQL DTU Calculator. This tool takes into account various important factors, such as your transaction volume, the complexity of your queries, and the size of your data.

Get started with the DTU Calculator

To calculate exactly how many DTUs you need, we recommend setting up a SQLTest environment. This will allow you to simulate a real-life workload and make the most accurate measurement possible.

We have installed the SQL test server on a virtual machine (8 vCPU, 32 GB RAM) and created a database. Before the test concluded, we quickly copied several large files to generate some I/O. Once this is done, you can download the Command Line Utility for the Azure DTU calculation. You then extract the ZIP file and run the sql-perfmon.cmd to start the Performance Monitor (PerfMon) counters. After that, press any key to start logging:

Now you can see that a User Defined Data Collector Set is running in PerfMon. Just let it do its thing and come back in an hour. After that, look for the CSV file in "C:\PerfLogs/Admin". And upload it to the Azure SQL Database DTU Calculator. Now just enter the number of vCPUs of your machine and press "Calculate".

Ready to view the results?

Analyze the outcome

In the case of our test, the results indicate that we are on the right track with a Standard - S4.

Final result DTU calculation | Arxus

CPU, IOPS and Logs | Arxus

Would you prefer some more details? You can consult an estimated calculation for each of the three main resources as well.

When you look at the charts of the individual components, you can make a more in-depth comparison between the different service and performance levels. According to the general calculations, our databases are best off with a Standard - S3. However, that only covers 95.28% of the total usage. So when your workload peaks, some delay may occur. The more specific charts, on the other hand, suggest that a Premium - P1 would be a better fit, as it covers nearly 100.00% of the CPU, 99.73% of the iops, and 99.19% of the log usage.

In other words, depending on the size of your workload, you could choose between the S3 and P1 tier. So it's definitely worth it to thoroughly analyze the entire calculation.

Check and adjust your DTU's

The Azure SQL Database DTU Calculator is a good starting point. However, once your database is up and running, we recommend that you regularly check the performance. Azure provides robust monitoring tools for this purpose to help you keep track of your resource consumption and identify potential bottlenecks. If you encounter performance issues or if your workload changes, you can always adjust the DTU level to maintain optimal resource allocation.

In summary: thanks to the uniform measurement unit for your CPU, memory, and data I/O, an Azure SQL DTU calculation makes your resource provisioning much easier. And by understanding how these components affect the performance of your database, you can always choose the right performance tier for your workloads.

Questions for us?

Need help interpreting the outcome of your DTU calculation? Or do you want to migrate your workloads to Azure? Our experts are fully prepared to tackle all your challenges. And provide expert advice, tailored to your company.

Our experts help you calculate your DTUs | Arxus

Want more? Read on!