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 Commu...
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:
We are continuosly working on other lessons learned about slow communication between our applications and db servers.
Enjoy!
