By: Esat Erkec | Updated: 2018-07-25 | Comments (1) | Related: > Azure SQL Database
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.
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.
You can stop collecting data by pressing “Ctrl+C”.
Now we will upload our performance counters csv file to the DTU calculator and fill in the number of CPUs and click Calculate.
Service Tier/Performance Level
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
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
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
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
- These tips can help about PowerShell if you faced to error when you run PowerShell Script.
- In this tip you can find details of DTU
About the author
Esat Erkec is a Microsoft certified SQL Server Database Administrator that has been working with SQL Server since 2004.
This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2018-07-25
