Azure SQL DB around the globe and latency - An architecture talk

Azure SQL Databases can be created in multiple regions around the globe and this is great

https://azure.microsoft.com/en-us/global-infrastructure/services/?products=sql-database

 

*Image from 2019-08-09

 

But you need to be aware of that when designing the architecture of your application. Working on a case, a customer complained that one specific query was too slow. After some analysis on Azure SQL DB we found the issue was not on Azure SQL DB itself but on the network, and not a failure in the network, but latency.

 

Find below some tests I made connecting to Azure SQL DBs (General Purpose: Gen5, 2 vCores) from my own computer and also from Azure VM, using script similar to this connection test script: 

https://github.com/FonsecaSergio/ScriptCollection/blob/master/Powershell/AzureSQL%20-%20Test%20SQL%20Connection.ps1

Full script at end of this article

 

Test 1 - FROM NOTEBOOK TO AZURE

• Source: SQL Client (Client in Portugal)
• Target: Azure SQL DB (West Europe)
• SIMPLE SELECT (Select 1)

AVG latency for 10 executions (ms): 45.8

Test 2 - INSIDE SAME DATA CENTER

• Source: SQL Client (Azure VM on West Europe)
• Target: Azure SQL DB (West Europe)
• SIMPLE SELECT (Select 1)

AVG latency for 10 executions (ms): 4

Test 3 - BETWEEN AZURE DATA CENTERS

• Source: SQL Client (Azure VM on West Europe)
• Target: Azure SQL DB (West US)
• SIMPLE SELECT (Select 1)

AVG latency for 10 executions (ms): 145.2

 

 

Test (1/2/3)

Just running a simple query "SELECT 1" got latency decreased in ~41 ms when running workload from inside azure. And and a total increase of ~140 ms running from Azure to Azure (West Eu to West US)

 

 

 

Lets see how this affect a BIG QUERY

Test 4 - FROM NOTEBOOK TO AZURE

• Source: SQL Client (Client in Portugal)
• Target: Azure SQL DB (West Europe)
• SELECT (289536 rows)

AVG latency for 10 executions (ms): 1207.5

Test 5 - INSIDE SAME DATA CENTER

• Source: SQL Client (Azure VM on West Europe)
• Target: Azure SQL DB (West Europe)
• SELECT (289536 rows)

AVG latency for 10 executions (ms): 641.4

Test 6 - BETWEEN AZURE DATA CENTERS

• Source: SQL Client (Azure VM on West Europe)
• Target: Azure SQL DB (West US)
• SELECT (289536 rows)

AVG latency for 10 executions (ms): 1513.3

 

 

Test (3/4/5)

*For this test we open and close the connections, that would not be good practice, because It will also increase the connection time and we could get benefit of using connection pool

 

Running a simple query "SELECT * FROM TEST" got an decrease on execution time of ~566 ms (per execution) running from inside Azure and and a total increase of ~872ms (per execution) running from Azure to Azure (West Eu to West US)

 

Conclusion

This will happen because even though data is traveling inside Azure Network (from Azure to Azure) the data need to go around the globe and this takes time and can get worse depending on the data size that you are returning. 

 

When developing to Azure always plan to put your client near your server.

 

You can also use geo replication to have read replicas near your clients

https://docs.microsoft.com/en-us/azure/sql-database/sql-database-active-geo-replication

 

------------------------------------------------

Below the sample script used

 

 

 

 

 

 

Clear-Host

$serverName = "SERVER.database.windows.net"
$databaseName = "sandbox"
$Username = "FonsecaSergio"
$Password = "xxxxxxxxxxxxxx"
$query = "SELECT 1"
$LoopCount = 10




########################################################################################################
$ignore1execforAvg = $true
$ConnectionTimeout = 15
$connectionString = "Server=tcp:$($serverName);Initial Catalog=$($databaseName);Persist Security Info=False;User ID=$($Username);Password=$($Password);MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=$($ConnectionTimeout)"
$connection = New-Object -TypeName System.Data.SqlClient.SqlConnection($connectionString)
$connection.StatisticsEnabled = 1

Write-Host "Connection to Server ($($serverName)) / DB ($($databaseName)) / UserName ($($Username))"
[System.Collections.ArrayList]$arrayLatency = @()

Try{
$command = New-Object -TypeName System.Data.SqlClient.SqlCommand
$command.CommandTimeout = 60
$command.CommandText = $query
$command.Connection=$connection

$aux = 0
while ($aux -le $LoopCount)
{

$stopwatch = [system.diagnostics.stopwatch]::StartNew()

$connection.Open() 
$result = $command.ExecuteScalar() 
#Write-Host "Query success. Server currenttime ($($result))"

$connection.Close() 

$stopwatch.Stop()

if($ignore1execforAvg -and $aux -eq 0)
{
write-Output ("FIRST EXECUTION WILL BE IGNORED for 1 - AVG - Stopwatch latency (ms): $($stopwatch.ElapsedMilliseconds)")
}
else
{
$arrayLatency.Add($stopwatch.ElapsedMilliseconds) | Out-Null

write-Output ("Exec ($($aux)) Stopwatch latency (ms): $($stopwatch.ElapsedMilliseconds)")

$data = $connection.RetrieveStatistics()
write-Output "-------------------------"
write-Output ("NetworkServerTime (ms): " +$data.NetworkServerTime)
write-Output ("Execution Time (ms) : " +$data.ExecutionTime)
write-Output ("Connection Time : " +$data.ConnectionTime)
write-Output ("ServerRoundTrips : " +$data.ServerRoundtrips)
write-Output ("BuffersReceived : " +$data.BuffersReceived)
write-Output ("SelectRows : " +$data.SelectRows)
write-Output ("SelectCount : " +$data.SelectCount)
write-Output ("BytesSent : " +$data.BytesSent)
write-Output ("BytesReceived : " +$data.BytesReceived)
write-Output "-------------------------" 
}

$connection.ResetStatistics()

$aux = $aux + 1
}

$LatencyAvg = ($arrayLatency | Measure-Object -Average).Average
write-Output ("Stopwatch AVG latency for $($LoopCount) executions (ms): $($LatencyAvg)")


}
catch [System.Data.SqlClient.SqlException]
{
$_.Exception.Errors[0] | Out-Host

$ExceptionMessage = "SQL Exception: ($($_.Exception.Number)) / State: ($($_.Exception.State)) / $($_.Exception.Message)" 
Write-Error $ExceptionMessage
Write-Host "ClientConnectionId($($connection.ClientConnectionId))"
Write-Host "Exception.ClientConnectionId ($($_.Exception.ClientConnectionId))"
}
Catch
{
Write-Error $_.Exception.Message
}

 

 

 

 

You can also check avg latency with expected statistics on document below

https://docs.microsoft.com/en-us/azure/networking/azure-network-latency