Lesson Learned #342: Slow communication between our applications and Azure SQL

Microsoft

Mar 30, 2023

We used to have service requests that our customers reported slow communications between their applications and db servers. Following I would like to share with you several details to detect where might be the issue that is causing this issue.

The first thing that we need to review 3 things:

Connection Policy that I'm using: Proxy or Redirect. Redirect should be a better improvement in terms having less latency. If you are using Private Link all the connections, currently, will be Proxy.
Connection Pooling: It is very important to use a connection pooling to reuse previous connection used and reduce the latency to open a new port every time that I'm connecting to Azure SQL Database. Review this video and link Lesson Learned #38: Which is the impact using connection pooling in my application - Microsoft Community Hub

To measure the connectivity: I would like to suggest to review this Lesson Learned #240: Checking connection and execution latency in Azure SQL DB and Managed Instance - Microsoft Community Hub if you need to run using Cloud Shell please review this script Lesson Learned #236: What? .... How much time you said? (CloudShell version) - Microsoft Community Hub

Once we have how much is the latency about the connection and we followed the best practices, for example, using for, example, accelerated networking in Virtual Machine that your application is running Lesson Learned #223:Hands-On-Labs: How to improve a bulk insert process - Microsoft Community Hub the next step is to understand if the latency is executing the queries, for example, SQL Server Profiler in Azure Data Studio plugin to review the execution of every one.

Finally, once we checked all these details, the next step is to review the wait stats, we have many articles about it: