Introducing the Azure SQL Database DTU Calculator: Part 2

The following is the second in a series of posts about the Azure SQL Database DTU Calculator.  In this post, I’ll cover some recent updates I’ve made as well as an overview of using the calculator for either a single database or a pool of databases.

Running a single SQL Server database in Azure has been supported for a few years but the concept of a Database Throughput Units (DTU) is relatively new.  The Azure SQL team first introduced the concept after some customers complained about performance when using Azure SQL Web and Business editions while other customers raved about performance.  You can guess which neighbors were the noisy ones.  In order to give all Azure customers a more predictable level of performance, the Azure SQL team introduced a new set of Service Tiers and Performance levels.  I won’t go into details regarding all the features of Service Tiers and Performance Levels but it is worth mentioning that each tier and level has an associated cost.  If you want more performance, you pay more.  If you want less…  you get the point.

Introducing tiers and levels to provide more reliable performance was a great first step and works really well for a single database which requires consistent performance.  However, many customers have more than a single database and in many cases, the performance of those databases isn’t consistent.  There are periods of high intensity followed by periods of low intensity.  Initially, developers would need to create a separate Azure SQL Database and try to manage the peaks and lulls of each database separately.  However, this type of database management was complicated and not cost effective when when working with several databases or more.

At Build 2015, Microsoft announced the preview of Azure SQL Elastic Databases to support unpredictable database demands and reduce the complexity of managing multiple databases.  The on-demand performance scaling of individual databases within an elastic database pool is possible because each database within a pool uses eDTUs from a shared set associated with the pool.  Allowing databases that require more performance to consume more DTUs while databases requiring less to consume less.

Regardless of running a single database or a pool of databases, developers migrating to Azure still need to know how their databases will perform after they migrate.  The good news is that SQL Azure allows you to scale your database up or down depending on your database needs.  The bad news is all of that happens post migration so it’s difficult to get a sense of cost without knowing the Service Tier and Performance Level required for your database workload.  That’s where the DTU Calculator can help.

Single Database or Multiple Databases

The instructions for using the DTU calculator are straightforward so I’ll try to focus on some of the things I don’t mention on the main page of the calculator.  First, you’ll need to decide if you’re going to run the calculator for a single database or multiple databases.  Running the calculator for a single database means your SQL Server only has one database and that database is what you plan to migrate to Azure.  If your SQL Server has more than one database but you still only plan to migrate one of those databases, you’ll either need to modify the PowerShell script I provide on the calculator’s main page, or understand that the calculator will likely overestimate your workload because the script is configured to capture totals for CPU, IOPs, and Log on the server– not by individual database.

If you SQL Server has multiple databases and you plan to migrate all of those databases to Azure, then you can run the PowerShell script as provided.  The calculator also supports multiple databases on multiple servers.  However, you will need to run the script on each server and then upload all the results in the calculator.

Resource Utilization

As part of the calculator, I’ve provided a PowerShell script to help capture the correct performance counters for the correct period of time.  By default, the script captures performance for the server at one second intervals for the duration of an hour.  However, you do not need to use the script as provided or at all.  Feel free to modify the script as needed or roll your own.  The only requirement for uploading results to the calculator is a CSV file with headers:

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

Note:  Make sure you run the PowerShell script as administrator.  If not, you may see errors about missing counters.

Upload Results

Once you have the performance CSV file(s), you are ready to calculate the results.  First, enter the number of cores for your SQL Server.  The calculator uses the number of cores to normalize the results against the servers running in Azure.  After specifying the number of cores, click the browse button to upload your CSV file.  If you are running the calculator for multiple databases, use the add and remove icons to add/remove multiple files to the table.  Once you have entered the number of cores for each server and uploaded all of your files, click the calculate button.

Review Analysis

After clicking the calculate button, you’ll see several charts which provide an analysis of your database resource consumption. The charts depict the percentage of time (based on your measurements) that your database’s resource consumption fits within the limits of each Service Tier and Performance Level.  You can review CPU, Iops, and Log individually as well as collectively to better understand which metrics affect the performance of your database.

Conclusion

In this post, I’ve given a little more background regarding database throughput units, Azure SQL Database, and Elastic Databases.  I’ve also  provided some instructions for using the DTU Calculator for either a single database or a pool of databases.  In my next post, I’ll cover analyzing the results.

Advertisements

52 thoughts on “Introducing the Azure SQL Database DTU Calculator: Part 2

  1. Hi Justin,
    Can you share some details on how you do the actual calculation / conversion of those metrics to DTU’s? I’ve been searching for a formula but haven’t been able to find anything useful.

    Best Regards,
    Benjamin

    1. Unfortunately, I can’t share the formula. The IP belongs to Microsoft and I haven’t been authorized to disclose it. Is there a specific use case you need to support that is outside the scope of what the DTU Calculator provides?

  2. Basically we have +350 databases which vary in size and usage – Microsofts idea was to go “trial and error” on each database, which simply does not work in the real world (maybe it does but we are not interested in investing that kind of resources for such an operation).

    Without disclosing any IP, is there like a guideline we can use for estimating (e.g. X IOPs, Y cpu cycles, Z mb of log in 5 min intervals) on the various tiers? It doesn’t have to be perfect – just “good enough” (if this makes any sense)

    Best Regards,
    Benjamin

  3. Hi Justin,

    Love your calculator, I’ve wanted something like this for a while now.

    I do have one question though. Like many DBA’s I split storage across multiple drives,especially for larger SQL instances, and the script only queries for a single drive. A typical scenario would be D; for primary data files, E: for secondary data files, I: for SQL installation, L: for log files, T: for TempDB, Y: for local backups, etc.

    Should I modify the script to add the counters all the drives together? Or add the counters for just the data & log drives? Or something completely different?

    Thanks in advance for your assistance.

    Q

  4. Hi Justin,

    First I would like to thank you for this tool.

    As I understand the tool is based on measurements using MS SQL. We have an Ora environment and I am trying to calculate the DTU if we move to Azure SQL. The metrics I can gather are in a cumulative 4 minute cycle and not exactly the same as perfmon gathers.
    Could you give me pointers how I can use the tool or calculate the DTU trough basic stats gathered with a monitoring tool.

    Thanks in advance!

    1. Depending on the OS, you just need to identify the Windows perf counter equivalent and prepare a CSV file with the following headers:
      •Processor – % Processor Time
      •Logical Disk – Disk Reads/sec
      •Logical Disk – Disk Writes/sec
      •Database – Log Bytes Flushed/sec
      Since you gathering at 4 minute intervals, I would take the average of each counter every four minutes.

      1. Hi Justin,

        Thank you for the quick response.

        I managed to get the data trough the tool!

        Kind regards,
        Roy Oltmans

  5. Hi Justin,

    This is good helpful materials. Are the values “Database – Log Bytes Flushed/sec” and “Database – Log Bytes Write/sec” interchangeable here when we come to prepare and upload our data files , as both are mentioned above / in the comments?

    (I think Database – Log Bytes Flushed/sec” counter is not available in SQL 2012 and later)?

    Thanks

    Simon

  6. Hey Justin,

    Great work on the calculator!

    We have five databases on the same SQL server which we’d like to migrate to Azure SQL. Our SQL server has the data and logs on separate volumes and it looks like the I/O is steady on the log volume but pretty low on the data volume. Of the five databases, only one of them has any significant Log Bytes Flushed/sec whereas the other four are mostly zero. However, when I run the calculator for each individual database they all recommend the Premium – P1 service tier. Does that seem reasonable, considering the vast difference in Log Bytes Flushed/sec between one database and the other four?

    Thanks so much,

    JJ

  7. Hi Justin,

    Any chance you can post a couple of example data files, so we we can test out the tool, or make our own similar data sets for non-Windows environments?

    Thanks,

    Simon.

  8. Hi Justin, your disc counters are all pointing to C:

    “\LogicalDisk(C:)\Disk Reads/sec”,
    “\LogicalDisk(C:)\Disk Writes/sec”,
    “\LogicalDisk(C:)\Disk Read Bytes/sec”,
    “\LogicalDisk(C:)\Disk Write Bytes/sec”,

    What counters should we use if say the Database is on D: and the Log Files are on E: ?

    Thanks

    Simon

  9. Hi Justing,

    Such a great job on the DTU calculator, now we can make the migration simpler than before. I have an SQL Server instance with more than 103 databases, and we want to move those to SQL Azure, in this case, I’m going to create an elastic pool for them. On the PowerShell script, we need to supply the database name to start the data capture I’m thinking if you have another PowerShell that walk-through all the databases instead of one by one?

    I now that I can adjust the PowerShell script to make this but I’m newbie on this, are you able to help me with?

    1. The database name is really only required for a single database. Since you’re going to use Elastic Pools, just pick any of your databases and run the script. Select Elastic Pools when you use the calculator.

  10. Hi Justin,

    Great, great job but please, it is really necessary to add in DTU calculator website a warning message about it PowerShell scripts, this scripts only works for English OS. I have a Spanish OS and was really painful discover that all counters are translated, counters don’t have universal notation, so for anyone that have other language maybe this steps could help.

    First at all you must identify all CounterSetName available in your system with get-counter -ListSet *, then you have to identify processor, disk and sql server counterset names and list all available option to get correct counter. Those are for Spanish:

    (get-counter -listset “Información del procesador”).paths
    (get-counter -listset “Disco lógico”).paths
    (get-counter -listset SQLServer:Databases).paths

    And corrects counters for spanih SO are:

    $counters = @(“\Información del procesador(*)\% de tiempo de procesador”,
    “\Disco lógico(e:)\Bytes de lectura de disco/s”,
    “\Disco lógico(e:)\Bytes de escritura en disco/s”,
    “\Disco lógico(f:)\Bytes de lectura de disco/s”,
    “\Disco lógico(f:)\Bytes de escritura en disco/s”,
    “\SQLServer:Databases($DatabaseName)\Bytes de registro vaciados/s”)

    Regards

    PD: also the Spanish documentation on technet (https://technet.microsoft.com/es-ES/library/dd367892.aspx) is in French.

    1. The PowerShell script isn’t required to use the calculator. The script only helps capture the counter metrics the calculator requires. Thanks for posting the spanish version!

  11. If client has a requirement “1vcpu, 1,5gb ram, 150gb for db” what Azure SQL plan would be equal to that?

  12. So the databasename is not neccessery if we use a elastic pool? It´s just needed for the powershell script?
    Is it possible to run the monitor for 24h ? Will the calculator take care of the this?

  13. I am unable to run this on a windows server 2008 environment – This cmdlet can be run only on Microsoft Windows 7 and above.
    Can you advise how to export-counter appropriately as this cmd is not supported in PS V3. (unable to update PS on server just now)

    Regards
    David

  14. Hi Justin,

    I’m new to SQL DBA and need some help. I’m getting the following error when running the script:

    “Get-Counter : The specified object was not found on the computer.
    At C:\Users\Administrator\Desktop\sql-perfmon.ps1:42 char:1
    + Get-Counter -Counter $counters -SampleInterval 1 -MaxSamples 3600 |
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : InvalidResult: (:) [Get-Counter], Exception
    + FullyQualifiedErrorId : CounterApiError,Microsoft.PowerShell.Commands.GetCounterCommand”

    I am running poweshell as admin.

    Any help please?

    1. Try opening PowerShell ISE. Before opening ISE, select ‘run as administrator’ regardless of you being an admin on the machine. Once you have PowerShell ISE open, browse to the DTU Calculator script, and run it from ISE.

      1. Hi Justin, I am having this issue too. I did open Powershell ISE as an Admin and ran the script from within ISE, but still getting the same error. Any more tips?

      2. Thanks for the quick response. Figured it out, the counter names are different on my instance. My SQL counter was: \MSSQL`$SQL2012WEB:Databases(_Total)\Log Bytes Flushed/sec.

  15. Hi Justin, thanks for the script. I have been using and doing demos of the script for several months now and recently noticed that some changes were made to the DTU Calculator. No longer do we specify the drives our files are on or have to provide a database name. Is this intended for both DTU Calculaor for a single database and elastic database or just elastic database? it appears the scripts for both are the same.

    1. Hey Tim,

      If you want to isolate a single drive you can modify the script but the script will work for both a single database or a pool. If your database server is running more than a single database, there isn’t a great way via PowerShell to isolate just one of those databases. The accuracy of the single database calculation really depends on your database server running a single database. Otherwise, the calculator will over estimate.

  16. If I have a sql server with 10 databases, but I only want to move 1 of them to azure, won’t these calculations be for the whole instance? That is assuming all the databases are on the same disk?

    1. That’s correct. As written, the perf collector doesn’t isolate a single database. You’d need to modify the collector to isolate your database. I believe this can be done using SQL DMV’s but I’m not sure.

    1. Hey Bart – There isn’t a way using the script or console app to isolate a single database. You’ll need to use SQL DMVs to isolate the DB. Unfortunately, I don’t have any examples of how to do that.

  17. Apologies if this appears twice…

    We’re trying to run the PowerShell script on a server in a clustered environment, and are having problems with the “SqlServer:Databases(_Total)\Log Bytes Flushed/sec”.

    I’ve seen various similar posts and comments on other sites, and as a result:

    1. I’m running the script as Administrator
    2. I’ve tried changing the “SqlServer” to my instance name

    but no luck. I’ve also run Perfmon to see which counters are available, and the “Log Bytes Flushed/sec” counter isn’t there.

    What am I missing?

  18. Hello Justin. Good job with the calculator !
    Somebody posted the same question I will post. If you do not mind, I would like to get a more extensive answer from you.

    I have a database server where my clients run reports. These reports are huge and we don’t know when my clients are going to run it. Therefore I would like to run the data collection for more than the 3600 seconds (1 hour) you specify in your powershell script.

    Is there any problem with that ? In your response before you stated that one “should not run it for more than an hour” … is there a reason ? I specifically need to run it by a work shift of 8 hours (28,800 seconds). I just want to make sure your calculator will run fine. Or, if otherwise it was built to work only with 1 hour data collections. Thanks !

  19. Hi Justin,

    We are in the process of gathering metrics as we prepare to migrate our 8000+ DB’s into Azure Elastic pools. I have ran both the PowerShell and command line tools multiple times. Each time I can see the tools gathering the metrics, only once was an output file generated and when I tried to upload the results I get..

    Error! One or more headers missing from file. Ensure the following are present: ‘% Processor Time’, ‘Disk Reads/sec’, ‘Disk Writes/sec’, ‘Log Bytes Flushed/sec’

    1) Do you have any idea why the output file isn’t generated 99% of the time when I have tried both PowerShell and command line multiple times (Run as admin).

    1. In older versions of the PowerShell and command line, there was an issue with the header row being added multiple times to the output file. Can you verify the header exists only once in the file? Can you verify the header names match the names coming from the error?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s