Lesson Learned #187: How to measure the connection latency in Azure SQL DB/Managed Instance

[Jose_Manuel_Jurado]

We often receive service requests where our customers asked about how to improve/measure the connection between my application and Azure SQL Database and/or Azure SQL Managed Instance, in this new post, I would like to share with you some of them. 

Best Practices

• Has a connection pooling size and configuration depending on your workload: We used to have service requests where our customers reported a connection issue due to the application reached the limit of the connection pooling you. See this URL:  Lesson Learned #155: Timeout period elapsed prior to obtaining a connection from the pool. - Microso...
• Open and close the connection as soon as posible and try to avoid idle connection. 
• Have a connection retry-logic.
• Enable Accelerated networking in your Azure Virtual Machine.

How the measure the latency

• In this github you could find a useful information about how to measure the connection latency for your database, for example, the PowerShell script called SQLConnectivityTest.ps1. Running this you are going to have a very useful information about the latency for multiple performance counters that be part of a connection/execution: ConnectionTime, ExecutionTime, NetworkServerTime based on this URL

Enjoy!