In the world of cloud computing, Microsoft Azure has emerged as a powerhouse for hosting SQL databases. But optimizing their performance might come with an additional challenge: choosing the appropriate DTU level for your database migration. Let’s take a closer look at how to tackle this topic.
Azure SQL databases are fully managed relational storage units, that offer a wide range of options for your business. To ensure optimal performance and resource allocation, Azure SQL databases use a metric known as Database Transaction Units (DTU).
Database Transaction Units, or DTUs for short, are a blended metric that represents the overall performance capacity of an Azure SQL Database. Microsoft has designed them to simplify the process of selecting the appropriate performance level for your database, by providing a single, easy-to-understand unit to measure it. In simpler terms, DTUs encompass three key resources: CPU, memory, and data I/O.
Azure SQL Databases have various performance tiers with different DTU Levels. Your workload and performance requirements determine the right tier for your database. But how do you know which one works best for you? By doing a weighted DTU calculation, based on three components:
By selecting the appropriate DTU Level, you’ll be able to ensure optimal performance without overspending on your resources. To assist you in defining the right tier, Microsoft offers a handy Azure SQL DTU Calculator. This tool considers several key factors like the volume of transactions, query complexity, and data size.
Ready to try it out yourself? To accurately calculate the DTUs you need, we recommend setting up an SQLTest. That way, you’ll be able to simulate real-life workload, which allows for a more precise measurement.
We’ve installed the SQL test server on a virtual machine (8 vCPU, 32 GB RAM) and created a database. Right before the end of the test, we copied some large files around to generate a bit of I/O. Once completed, you can download the Command Line Utility for the Azure DTU calculation. Then extract the ZIP-file and run the sql-perfmon.cmd to start the Performance Monitor (PerfMon) counters. Press any key to start logging:
A User Defined Data Collector Set will start running in the PerfMon. Just let it do its thing and check back after 1 hour. Then look for the CSV-file in “C:\PerfLogs\Admin”. And upload it to the Azure SQL Database DTU Calculator. Enter the number of vCPUs in your machine and press “Calculate”.
Ready to look at the results?
The results show that in our case we should be good with a Standard - S4.
Want to get more details? You can view an estimation for each of the three key resources.
CPU |
Iops |
Log |
|
|
|
|
By looking at the graphs of the individual components, you can make a more detailed comparison between the different service tiers and performance levels. The overall calculations suggest a Standard - S3. But it only covers 95.28% of the utilization. So, when your workload peaks, there might be some delays. According to the more specialized graphs, a Premium - P1 would offer a better solution, covering approximately 100.00% of CPU, 99.73% of iops, and 99.19% of Log utilization.
In other words, depending on the criticality of your workload, you might want to choose between the S3 and P1 tier. So, it’s well worth taking a closer look at the entire calculation.
The Azure SQL Database DTU Calculator is a good starting point. But once your Azure SQL Database is up and running, it's essential to monitor its performance regularly. Azure provides robust monitoring tools to help you track resource usage and identify potential bottlenecks. If you stumble on performance issues or changes in workload patterns, you can adjust the DTU Level accordingly to ensure optimal resource allocation.
So, to sum things up: Azure SQL DTU calculation greatly simplifies resource provisioning by providing a unified metric for CPU, memory, and data I/O. And by understanding how these components influence database performance, you’ll be able to make well-informed decisions when selecting the appropriate performance tier for your workloads.
Need help interpreting the outcome of your DTU calculation? Or do you want to migrate your workloads to Azure? Our experts are ready to tackle all your challenges. And offer expert advice, tailored to your business needs.