SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Calculating DTU's for Azure SQL Database

By Ganapathi varma Chekuri,

One of my clients asked to calculate the DTU’s required for an existing database before migrating to Azure SQL Database. Pricing Azure SQL Database is different from traditional SQL Server licensing. Azure SQL Database pricing depends on the database service tier options ,such as Database Transaction units and the maximum size of the database.

Azure SQL Database offers Basic, Standard, Premium, and Premium RS service tiers for both single databases and elastic pools. The cost of Azure SQL Database directly relates to which tier and performance level you are using. To determine service tier and performance level and database throughput units (DTUs) with the help of Azure database DTU calculator.

Requirements

Basic

Standard

Premium

 Premium RS

Target workload

Development and production

Development and production

Development and production

Workload that can tolerate data loss up to 5-minutes due to service failures

Uptime SLA

0.9999

0.9999

0.9999

N/A while in preview

Backup retention

7 days

35 days

35 days

35 days

CPU

Low

Low, Medium, High

Medium, High

Medium

IO throughput

Low

Medium

Order of magnitude higher than Standard

Same as Premium

IO latency

Higher than Premium

Higher than Premium

Lower than Basic and Standard

Same as Premium

Columnstore indexing and in-memory OLTP

N/A

N/A

Supported

Supported

For more information on check this link: https://azure.microsoft.com/en-us/pricing/details/sql-database/

This calculator will help you determine the number of DTUs being used for your existing on-prem SQL Server database(s) as well as a recommendation of the minimum performance level and service tier that you need before you migrate to Azure SQL Database. It does this by using performance monitor counters.

How to Use the DTU Calculator

A ‘DTU’ is a ‘Database Throughput Unit’. It’s a calculation of the CPU and IO resource requirements for a given workload on a given server. The exact criteria used by the DTU Calculator to formulate a suggested Performance Level isn’t explained, but it’s calculated using the Performance Counters below:

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

I have SQL server 2014 instance with OLTP databases running on a virtual machine with 16 cores and 48 GB RAM. We have configured above parameters using Performance Monitor or using a PowerShell script (both attached in the Resources section). Here is how to did this.

Step 1

Create a new data collector set. Click the Windows Start menu and search for "performance". Select performance counters and save the data collector set. In the below screenshot, we have configured the perfmon counters using the perfmon tool.

Step 2

Run the data collector set. in the below screenshot, we can start the report to collect the data.

Step 3

Once the script executes, copy the .csv file generated by the script and upload the csv file to find the recommended DTU’s and size of Azure SQL Database for your workload using the DTU calculator. In the below screenshot, the CSV file is uploaded and number of cores for server is set to 16.

Step 4

Click Calculate to view recommended Service Tier/Performance Level and DTUs. In the below screenshot, it recommended that we migrate our SQL Server workload to a Standard S2 tier.

In the below screenshot, the database utilization has been converted to Azure SQL Database DTU’s for the duration of the workload.

The results will help you to choose the service tier for your workload.  The Standard tier is more for your common workloads while Premium is designed for high transactional volume where I/O performance is much more important to you.

Ganapathi varma Chekuri
Lead SQL DBA, MCP
Linkedin

 

Resources:

sql-perfmon-ps.zip | sql-perfmon-cl.zip
Total article views: 504 | Views in the last 30 days: 504
 
Related Articles
FORUM

How to Create an SQL Database on SBS 2003 Premium

How to Create an SQL Database on SBS 2003 Premium

ARTICLE

SQL Azure Performance Benchmarking

An evaluation of the SQL Azure Database Performance Levels recommended by the DTU Calculator service...

FORUM

Measuring "workload" in SQLServer

What metric(s) best define "workload" in a SQLServer environment?

BLOG

Power BI Premium, Report Server, Apps and API

Announced today are some really cool new Power BI features: Power BI Premium Previously available ...

BLOG

Azure SQL Database – Optimizing For Ad-hoc Workloads

Today I found out that it is now possible to enable the setting optimize for ad-hoc workloads at the...

Tags
azure sql database    
dtu    
 
Contribute