Troubleshooting connection pool issue between .Net

Connection pooling helps users manage connections between .NET and Microsoft SQL Server instances. This approach reuses database connections to improve application performance and promote efficient resource use.

This article examines some common database connection pooling issues and how to troubleshoot them to make the most of your connections. To follow along, use your preferred development environment and a performance monitor such as SQL Server Profiler.

What is connection pooling?

The connection pooling technique optimizes how users create and manage database connections. When you make a database request, an application typically creates a new database connection over several steps, like parsing the connection string and creating a connection object.

This process is time-consuming and resource-intensive for your application, especially when it makes frequent database requests. Connection pooling solves this problem by enabling many requests to use the same connection.

When an application requests a database connection, the connection pooling system checks if the pool has an available connection. If it finds one, the connection pooling system returns that connection to the application. If it doesn’t find one, the system makes and adds a new connection to the pool.

Benefits of connection pooling

.NET enables connection pooling by default, facilitating improved application performance and efficient resource usage.

By reusing existing connections, the application improves performance and avoids the overhead of establishing a new connection every time. This approach uses system resources — such as memory and processors — more efficiently, without multiple connections vying for resource use and degrading performance.

Identifying connection pooling issues

Although database connection pooling offers numerous benefits, developers must employ it correctly to avoid issues with application performance and user experience.

This section will identify and review solutions for common connection pooling issues between .NET and SQL Server: connection leaks, connection timeout, and pool saturation.

Connection leaks

When an application doesn’t close a connection properly, the open connection is unavailable for reuse. The application then generates new connections, using more resources.

The following code example causes a connection leak:

using Microsoft.Data.SqlClient;

string connectionString = "Server=<your-server>;Database=<your-database>;User Id=<your-Id>;Password=<your-password>;TrustServerCertificate=true"; 

for (int i = 0; i < 10; i++) 
{ 
 // create a connection 
 SqlConnection connection = new SqlConnection(connectionString); 

 // open the connection 
 connection.Open(); 

 Thread.Sleep(10); 
 Console.WriteLine("connection opened " + i); 
} 

In this example, the application establishes a new connection every time the for loop executes.

Tools like SQL Server Profiler and performance counters can monitor connection usage and identify these excess connections. Here, SQL Server Profiler shows multiple login events in the EventClass row. Consequently, the example code made a new server connection 10 times.

To avoid connection leaks like in the code below, use Close() to terminate your connections.

for (int i = 0; i < 10; i++) 
{ 
 // create a connection 
 SqlConnection connection = new SqlConnection(connectionString); 

 // open the connection 
 connection.Open(); 

Thread.Sleep(10); 
Console.WriteLine("connection opened " + i); 

 // close the connection 
 connection.Close(); 
} 

It’s easy to forget to close your connections in large projects. The best approach is to open connections with the using statement. Then, the connection closes automatically after use:

for (int i = 0; i < 10; i++) 
{ 
 // create a connection 
 using (SqlConnection connection = new SqlConnection(connectionString)) 
 { 
 // open the connection 
 connection.Open(); 

 Thread.Sleep(10); 
 Console.WriteLine("connection opened " + i); 
 } 
} 

You don’t need to close the connection with Close(). Instead, the connection closes when everything inside the using statement code block is complete.

SQL Server Profiler shows that this technique resolved the connection leak problem. Instead of creating 10 new connections, the sample program uses the pool’s existing one.

Connection timeouts

You may also face connection timeout issues when using connection pooling. The connection times out when the application has waited longer than necessary for the server to connect. These timeouts cause performance problems and a poor user experience.

When you experience a timeout error, it’s most likely a SQL query or command timeout.

Identifying connection timeout

A connection timeout error means the connection timed out before the application could connect. Usually, you’ll receive this error if your connection takes longer than 15 seconds.

First, simulate a connection timeout by connecting to the wrong server. To do this, change the server address in the connection string:

using Microsoft.Data.SqlClient; 

// change the server address to a wrong address 
string connectionString = "Server=<wrong-server-address>;Database=<your-database>;User Id=<your-Id>;Password=<your-password>;TrustServerCertificate=true"; 

for (int i = 0; i < 10; i++) 
{ 
 // create a connection 
 using (SqlConnection connection = new SqlConnection(connectionString)) 
 { 
 // open the connection 
 connection.Open(); 
 Thread.Sleep(10); 
 Console.WriteLine("connection opened " + i); 
 } 
} 

When you execute the code with the wrong server address, you get an error like this:

SqlConnection.Open() in the error stack shows that the timeout happened before the application could establish a connection. This is a connection timeout issue.

If you encounter a connection timeout error, you can troubleshoot by:

• Ensuring there are no mistakes in the connection string
• Increasing the connection timeout setting beyond the default 15 seconds

For example, set the connection timeout to 30 seconds:

using Microsoft.Data.SqlClient; 

string connectionString = "Server=<your-server>;Database=<your-db>;User Id=<your-userId>;Password=<your-password>;TrustServerCertificate=true;connection timeout=30"; 

 // create a connection using (SqlConnection connection = new SqlConnection(connectionString)) 
 { 
  // open the connection 
 connection.Open(); 
 
  } 

The connection timeout=30 setting increases the time it takes for the connection to time out.

Identifying SQL query or command timeout

An SQL query or command timeout error indicates that the server timed out before the SQL query command could finish. The default command timeout value is 30 seconds.

This example code below creates a table and has a WAITFOR DELAY of 30 seconds. Now, simulate a timeout error:

using Microsoft.Data.SqlClient; 

string connectionString = "<your-server>;Database=<your-db>;User Id=<your-userId>;Password=<your-password>;TrustServerCertificate=true"; 

 // create a connection 
 using (SqlConnection connection = new SqlConnection(connectionString)) 
 { 
 // open the connection 
 connection.Open(); 

 // execute sql query 
string createTable = "WAITFOR DELAY '00:00:30'" + "CREATE TABLE Users (Name char(50), age int)"; 

 using (SqlCommand command = new SqlCommand(createTable, connection)) 
  { 
 command.ExecuteNonQuery(); 
  Console.WriteLine("Table created"); 
  } 
}

This code generates an error stack that looks like the following screenshot:

You have a command timeout when you find SqlCommand in the error stack. Typically, this type of error occurs when the code’s queries take too long to complete. Fix this error by increasing the command timeout value:

// create a connection 
using (SqlConnection connection = new SqlConnection(connectionString)) 
 { 
  // open the connection 
 connection.Open(); 

 // execute sql query 
 string createTable = "WAITFOR DELAY '00:00:30'" + "CREATE TABLE Users (Name char(50), age int)"; 
 using (SqlCommand command = new SqlCommand(createTable, connection)) 
 { 
 command.CommandTimeout = 60; // increase the timeout value to 60 seconds 
 command.ExecuteNonQuery(); 
 Console.WriteLine("Table created"); 
 } 
}  

You can also set your SQL server’s connection timeout and CommandTimeout values.

Connection pool saturation

A connection pool becomes saturated when all the connections are in use and applications can’t add new connections. This means the connection pool is full, as its default maximum is set to 100 connections.

Connection pool saturation occurs for many reasons, including numerous database requests from increased traffic, long-running queries, and poor connection management, including failure to close connections. Connection pool saturation severely impacts the application’s performance.

The database refuses requests for new connections when the connection pool is full. This impacts performance and may cause the application to crash.

To identify connection pool problems, use tools such as performance counters and SQL Server Profiler to monitor connection usage and identify open connections for an extended time.

Then, to improve performance, change the Max Pool Size value in the connection string like this:

// increase pool size 
string connectionString = "Server=127.0.0.1,1433;Database=MyDB;User  
Id=sa;Password=edoller@80>;TrustServerCertificate=true;Max Pool Size=1000"; 

// create a connection 
using (SqlConnection connection = new SqlConnection(connectionString)) 
{ 
 // open the connection 
 connection.Open(); 

 // execute sql query 
}  

The Max Pool Size value increases the number of connections that applications can add to the connection pool. A higher number prevents the connection pool from becoming saturated and improves application performance.

Summary

Understanding connection pooling is essential for managing connections between .NET applications and SQL Server. This approach improves application performance and resource usage by reusing connections.

You can now troubleshoot connection leaks, timeouts, and pool saturation. When you adjust your connection pool settings and monitor connections, you’re optimizing your .NET applications and SQL Server instances for the best possible performance.

Learn how Site24x7 optimizes your database connections by monitoring your SQL servers and network.