Calculating SQL Database DTU for Azure SQL DB Using PowerShell

Written by Tao Yang

over the last few weeks, I have been working on a project related to Azure SQL Database. One of the requirements was to be able to programmatically calculate the SQL Database DTU (Database Throughput Unit).

Since the DTU concept is Microsoft’s proprietary IP, the actual formula for the DTU calculation has not been released to the public. Luckily, Microsoft’s Justin Henriksen has developed an online Azure SQL DB DTU Calculator, you can also Justin’s blog here. I was able to use the web service Justin has developed for the online DTU Calculator, and I developed 2 PowerShell functions to perform the calculation by invoking the web service. The first function is called Get-AzureSQLDBDTU, which can be used to calculate DTU for individual databases, the second function is called Get-AzureSQLDBElasticPoolDTU, which can be used to calculate DTU for Azure SQL Elastic Pools.

Obviously, since we are invoking a web service, the computer where you are running the script from requires Internet connection. Here’s a sample script to invoke the Get-AzureSQLDBDTU function:

Note: you will need to change the variables in the ‘variables’ region, the $LogicalDriveLetter is the drive letter for the SQL DB data file drive.

The recommended Azure SQL DB service tier and coverage % can be retrieved in the ‘Recommendations’ property of the result:

image

the raw reading for each perf sample can be retrieved in the ‘SelectedServiceTiers’ property of the result:

image

Lastly, thanks Justin for developing the DTU calculator and the web service, and pointing me to the right direction.

Leave a Reply