Lesson Learned #279: Slow between calls to Azure SQL Elastic Database Pool

It is not the first time, that we used to have this type of service requests that our customers reported slow connections in an Elastic Database Pool. Following I would like to share some details about my lessons learned about this topic.

Besides following the best practices about connection and command retry-logic, connection pooling and redirection, we need too know several topics:

• Connection Pooling definition: as Connecting to a data source can be time consuming. To minimize the cost of opening connections, drivers uses an optimization technique called connection pooling, which minimizes the cost of repeatedly opening and closing connections. This is cool but we need to understand that the connection pooling is a combination between connection string plus application/process that is opening the connection. What does mean this?
  
  • As you know, Azure SQL Database is not possible to change the database once you have already established the connection to the databases. This means that you are going to have a connection pool per this combination connection string plus application/process.
    
  • The second part is how many process your application service has, for example, I saw several applications that they have 20 process/workers of the same application to attend concurrent requests.
    

Knowing this, if your application is running in a single node with 20 process and those could connect to any database of your elastic database pool, for example, 80, we could see this:

• 160 connection poolings with a potential number of connections at the same time (for example, in .NET we have 100 connections by default). So, we could have 16.000 connections at the same time in a single node.
  
• Besides that this could cause a posible port exhaustion and high resource usages in terms of memory and threads in the application node, also, could cause a different behaviour when a new process/threads is opening a new connection after first request, reporting our users that the connection is taking more time to the database.
  
• Also, we saw that when the process/thread is idle for a specific timeframe, this process is removed and new process will be created if needed, creating again a new connection pooler and taking a few miliseconds more per first connections.
  

In this situation, defining a specific nodes per customer or group of customers depending on business needs or/and using a proxy server that maintains the connection to the database, our users could not have this behavior. For more information, you could read this link

In another hand, in terms of performance, remember that an elastic database pool is a SQL Server instance that are running all the databases sharing same resources, any additional workload that one database will have could impact in the performance in others one on the same elastic database pool.

Enjoy!

Continue reading...