Calculating SQL Database DTU for Azure SQL DB Using PowerShell

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.

#region variables
$SampleInterval = 1
$MaxSamples = 3600
$DatabaseName = 'AdventureWorks'
$LogicalDriveLetter = 'C'
$ComputerName = 'SQLDB01'
#endregion

#Get number of processors cores
$processors = get-wmiobject -query 'select * from win32_processor' -ComputerName $ComputerName
$Cores = 0
Foreach ($processor in $Processors)
{
  $Cores = $numberOfCores + $processor.NumberOfCores
}

#region collect perf counters
$counters = @('\Processor(_Total)\% Processor Time', "\LogicalDisk($LogicalDriveLetter`:)\Disk Reads/sec", "\LogicalDisk($LogicalDriveLetter`:)\Disk Writes/sec", "\SQLServer:Databases($DatabaseName)\Log Bytes Flushed/sec")

$arrRawPerfValues = Get-Counter -Counter $counters -SampleInterval $SampleInterval -MaxSamples $MaxSamples -ComputerName $ComputerName
$arrPerfValues = @()
Foreach ($item in $arrRawPerfValues)
{
  $processorTime =$item.CounterSamples[0].CookedValue
  $diskReads = $item.CounterSamples[1].CookedValue
  $diskWrites = $item.CounterSamples[2].CookedValue
  $logBytesFlushed = $item.CounterSamples[3].CookedValue
  $properties = @{
  diskReads       = $diskReads
  diskWrites      = $diskWrites
  logBytesFlushed = $logBytesFlushed
  processorTime   = $processorTime
  }
  $objPerf = New-Object -TypeName psobject -Property $properties
  $arrPerfValues += $objPerf
}
#endregion

#region Calculate DTU
#construct web API JSON parameter
$apiPerformanceItems = ConvertTo-Json -InputObject $arrPerfValues

#Invoke the web API to calculate DUT
$DTUCalculationResult = Get-AzureSQLDBDTU -Core $Cores -apiPerformanceItems $apiPerformanceItems
#endregion

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

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

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