How to use the Azure SQL Database DTU Calculator

By:   |   Updated: 2018-07-25   |   Comments   |   Related: More > Azure

Problem

Azure SQL Database DBaaS (database-as-a-service) offers incredible opportunities, but when you decide to migrate your on-premises database to Azure SQL, we have to answer two questions: Which service tier and performance level is proper for me? and How many Database Transaction Units (DTUs) do I need?

Because these decisions directly affect your Azure SQL performance and Azure bills, Microsoft announced a new metric for Azure SQL Database called the Database Transaction Unit (DTU). This metric can be defined as a blended measure of CPU, memory, reads, and writes or a DTU can be defined as horse power for Azure SQL.  In short, before the migration process we have to find answers to two essential questions: How many DTU are required? Which service tier and performance level is right for me?

Solution

Microsoft announced a web-based calculator called the DTU Calculator. This calculator helps us determine the number of DTUs and helps us to decide the service tier of Azure SQL Database.  The main idea of this calculator is capturing your on-premises instance resource utilization in a time period and upload this utilization file to the calculator. This calculator estimates your number of DTUs and service tier requirements. At this point, we needed to be aware of one thing about the DTU calculator. The calculator recommendation results may not find the optimum performance requirements. So, you may need to increase or decrease the number of DTUs or change the service tier. But Azure SQL provides flexibility to change the number of DTUs and the service tier. This setting change does not cause any downtime on Azure SQL.

I want to add this note again about Azure SQL, the number of DTUs and performance level selection directly affects the charges on your Azure bill.

How to Use DTU Calculator

In this section, we will cover usage of the DTU Calculator for a single database.

In the first step, we need to collect several performance counter metrics and then we will upload this file to the DTU calculator. The metrics are:

  • Processor - % Processor Time
  • Logical Disk - Disk Reads/sec
  • Logical Disk - Disk Writes/sec
  • Database - Log Bytes Flushed/sec

The DTU calculator will recommend some decisions about Azure SQL. Also, the DTU calculator supports this recommendation with charts. We can find two options to collect performance counter values:

  • Command Line Utility
  • PowerShell Script

Go the DTC Calculator web page and download either method to collect the on-premises database performance counter utilization.  In this demonstration we will use the PowerShell Script, by using the Download PowerShell Script option.

dtu calculator

Before running the PowerShell script, you have to ensure there isn’t any other workload on your SQL Server instance, because this extra workload might cause incorrect calculations and also incorrect estimation results. Another suggestion is to run this script at peak times of your workload. The purpose of this suggestion is to find out the maximum DTU requirement.

We will copy this script to PowerShell and run.

Tip for PowerShell Script: Counters are collected at 1 second intervals for 1 hour or 3600 seconds. If you want you can change these values and the log file will be created as C:\sql-perfmon-log.csv. If you want to change these parameters you can use the PowerShell ISE to change the settings shown below.

dtu calculator powershell script
dtu calculator powershell script

You can stop collecting data by pressing “Ctrl+C”.

run powershell script

Now we will upload our performance counters csv file to the DTU calculator and fill in the number of CPUs and click Calculate.

dtu calculator upload file

Service Tier/Performance Level

dtu calculator charts and recommendations

After completing the previous step, we will start to analyze the results of the DTU Calculator. The Service Tier / Performance Level donut chart illustrates which service tier can meet the on-premises workload requirement in Azure SQL. In this chart, we can find a percentage distribution of service tiers and performance levels and on the right side recommendations of service tier detail. For the above image, the Standard service tier and S9 performance level can meet performance requirements of the current on-premises instance in Azure, because the Standard Service tier covers 94.07% of our workload utilization. If you ask my opinion, repeat this calculation in different time periods and evaluate the results all together.

DTUs Over Time

dtu calculator charts and recommendations

The DTU’s Over Time chart illustrates the DTU requirement in the duration of your monitoring period. For DTU’s Over Time chart, we can decide on a bit higher than average value for the DTUs.  The above chart shows that the value can be 1000 DTUs. 

Service Tier/Performance Level of CPU

dtu calculator charts and recommendations

In the View More Details section you can find three donut charts.  These detailed charts illustrate solely CPU, IOPS and Log utilization. The advantage of these charts is that we can figure out which individual workload (CPU, IOPS and Log) utilization type needs how much DTU and which service tier.

Service Tier/Performance Level for Cpu, Iops & Log

dtu calculator charts and recommendations

The last chart illustrates how much of your workload’s CPU, IOPS, and Log is covered by a Service Tier/Performance Level.

In the above chart, 87.45% of CPU, 74.14% of IOPS, and 74.84% of Log utilization requirements can be met by the Standard-S2 service tier-performance level.

Next Steps


Last Updated: 2018-07-25


get scripts

next tip button



About the author
MSSQLTips author Esat Erkec Esat Erkec is a Microsoft certified SQL Server Database Administrator that has been working with SQL Server since 2004.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.






download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools