Connection Pooling for the SQL Server DBA

 

There are a handful of questions that DBAs get in the wild that aren’t necessarily under the jurisdiction of the typical DBA.  One of those aspects is connection pooling.  Far too often application teams, or network admins, or <insert non-DBA professional here> approach the DBA with questions regarding connection pooling, and whether or not it is functioning correctly or even happening.

The answer that enterprise DBAs need to be giving to these inquiries is that it is provider-specific. In other words, it is on the client/application side that connection pooling is handled.  I will show an example of this below using the .NET Data Provider for SQL Server (the System.Data.SqlClient namespace), but the ideas proposed should propagate to other popular providers used today.

An application utilizes a provider to make connections to an instance of SQL Server.  In my case, I’m going to use a PowerShell process to mimic this behavior.  I’ll start off by creating a connection string that my application will use to connect to the default instance on SQLBOX1:

 

$ConnectionString = "data source=sqlbox1; initial catalog=master; trusted_connection=true; application name=ConnPoolTest"

 

The above connection string is rather simple, but I am specifying the Application Name parameter so that we can easily parse sys.dm_exec_sessions in a below query to further prove connection pooling.  Now what we’re going to do is create five System.Data.SqlClient.SqlConnection objects:

 

$SqlConnection1 = New-Object System.Data.SqlClient.SqlConnection($ConnectionString)

$SqlConnection2 = New-Object System.Data.SqlClient.SqlConnection($ConnectionString)

$SqlConnection3 = New-Object System.Data.SqlClient.SqlConnection($ConnectionString)

$SqlConnection4 = New-Object System.Data.SqlClient.SqlConnection($ConnectionString)

$SqlConnection5 = New-Object System.Data.SqlClient.SqlConnection($ConnectionString)

 

All this does is instantiate the SqlConnection objects utilizing the same connection string that we defined above.  Note: Connection Pools are going to be defined by the connection strings that are passed. Different connection strings, different connection pools.  Now we want to open these connections.  I put a two second sleep in between each connection opening just to see a gradual increase in the pooled connection count with the PerfMon counter NumberOfPooledConnections for all instances of the .NET Data Provider for SqlServer object:

 

$SqlConnection1.Open()

Start-Sleep -Seconds 2

 

$SqlConnection2.Open()

Start-Sleep -Seconds 2

 

$SqlConnection3.Open()

Start-Sleep -Seconds 2

$SqlConnection4.Open()

Start-Sleep -Seconds 2

 

$SqlConnection5.Open()

 

Looking at the aforementioned counter in PerfMon, we see that the number of pooled connections goes from zero to five within the ten second duration.

 

 

We can also see this from sys.dm_exec_sessions, by filtering on the application name that I specified in the connection string:

 

select  session_id,program_name

from sys.dm_exec_sessions

where program_name = 'ConnPoolTest';

 

 

Now, there is nothing new here.  There have been five connections, and there are five sessions that show this.  But connection pooling comes into play and really flexes its muscles when these connections are closed, and even disposed:

 

$SqlConnection1.Close()

$SqlConnection2.Close()

$SqlConnection3.Close()

$SqlConnection4.Close()

$SqlConnection5.Close()

$SqlConnection1.Dispose()

$SqlConnection2.Dispose()

$SqlConnection3.Dispose()

$SqlConnection4.Dispose()

$SqlConnection5.Dispose()

 

Write-Host "Connection1 State: $($SqlConnection1.State)" -ForegroundColor Green

Write-Host "Connection2 State: $($SqlConnection2.State)" -ForegroundColor Green

Write-Host "Connection3 State: $($SqlConnection3.State)" -ForegroundColor Green

Write-Host "Connection4 State: $($SqlConnection4.State)" -ForegroundColor Green

Write-Host "Connection5 State: $($SqlConnection5.State)" -ForegroundColor Green

 

The last five lines of code show the status of each of the connections’ state, and this should have the output like the following:

 

Connection1 State: Closed

Connection2 State: Closed

Connection3 State: Closed

Connection4 State: Closed

Connection5 State: Closed

 

But, re-executing the sys.dm_exec_sessions query above we still see that the same five sessions are alive and well:

select     session_id, program_name

from sys.dm_exec_sessions

where program_name = 'ConnPoolTest';

 

 

This is connection pooling. PerfMon also shows us that these connections are indeed in the pool for later reuse:

 

 

Now when the provider needs to open up a new connection with a connection string correlated to an existing pool it’ll just take one of the inactive pooled connections so that the overhead of establishing a connection to the instance isn’t incurred.

So throughout the above information, I’ve been hammering the point that connection pools are unique to the connection strings themselves.  Let’s see what it looks like with a different connection string. Modifying only the application name in the connection string, I create a new one and specify this for two more connections:

 

$ConnectionString2 = "data source=sqlbox1; initial catalog=master; trusted_connection=true; application name=ConnPoolTest2"

$SqlConnection6 = New-Object System.Data.SqlClient.SqlConnection($ConnectionString2)

$SqlConnection7 = New-Object System.Data.SqlClient.SqlConnection($ConnectionString2)

$SqlConnection6.Open()

Start-Sleep -Seconds 2

 

$SqlConnection7.Open()

 

$SqlConnection6.Close()

$SqlConnection7.Close()

 

$SqlConnection6.Dispose()

$SqlConnection7.Dispose()

 

Glancing back at the PerfMon counters (I have now added the NumberOfActiveConnectionPools counter), we see that these two new connections are in a separate connection pool due to a different connection string:

 

 

The green line is the NumberOfActiveConnectionPools counter.  It is one for the first five connections, as they all use the same connection string.  Specifying a different connection string for two additional connections show us that the five original connections cannot be reused, so another connection pool is created for this new connection string, and now holds the two new connections.

Looking again at what SQL Server sees (with a slightly modified WHERE clause to include the “new” application name):

 

select session_id, program_name

from sys.dm_exec_sessions

where program_name like 'ConnPoolTest%';

 

 

We can see above that the two connection pools’ connections are indeed there and pooled for later use, even though the connections that they originated with have been closed and disposed.

 

One of the biggest considerations with connection pooling is to ensure that proper disposal or closure of the SqlConnection object(s) is taking place.  What happens is that when clients use and don’t close or dispose the connection object, it will continue to consume the pooled connection instead of releasing it back to the pool for reuse.  Let’s take a look at a [scaled-down] example of this.  Notice in my connection string that I use the “Max Pool Size” parameter to cap this off at a value of five pooled connections, instead of using the default 100 max pool size.  A list of connection string parameters, including those related to connection pooling, can be found on the MSDN reference for the SqlConnection.ConnectionString property.  Below we specify “Max Pool Size” as five, and notice what happens when we attempt to open a sixth connection with the same connection string:

 

$ConnectionString3 = "data source=sqlbox1; initial catalog=master; trusted_connection=true; application name=ConnPoolTest; max pool size=5"

$SqlConnection1 = New-Object System.Data.SqlClient.SqlConnection($ConnectionString3)

$SqlConnection2 = New-Object System.Data.SqlClient.SqlConnection($ConnectionString3)

$SqlConnection3 = New-Object System.Data.SqlClient.SqlConnection($ConnectionString3)

$SqlConnection4 = New-Object System.Data.SqlClient.SqlConnection($ConnectionString3)

$SqlConnection5 = New-Object System.Data.SqlClient.SqlConnection($ConnectionString3)

$SqlConnection6 = New-Object System.Data.SqlClient.SqlConnection($ConnectionString3)

$SqlConnection1.Open()

$SqlConnection2.Open()

$SqlConnection3.Open()

$SqlConnection4.Open()

$SqlConnection5.Open()

# five connections opened with no issues

$SqlConnection6.Open()

# the above attempt for SqlConnection.Open() fails

 

When I made the call $SqlConnection6.Open(), I received the following error, describing quite well what probably happened (and in this case, did happen):

 

Exception calling "Open" with "0" argument(s): "Timeout expired. The timeout period elapsed

prior to obtaining a connection from the pool. This may have occurred because all pooled

connections were in use and max pool size was reached."

 

This is a common pitfall for web applications, and the above example illustrates why this can be a problem. Say a web application uses the default max pool size (100), and there are currently 100 active and consumed pooled connections.  Because they may not have been properly closed or disposed, the 101th attempt will result in a similar error and behavior.  Common ways to handle this are in the finally block of a try/catch/finally call, or in C# implement a using block, which automatically calls the IDisposable.Dispose() method at the end of the block.

 

Even though this may very well be client-side technology and functionality, there are ways that we can keep an eye on connection pooling and “see” it happening within SQL Server. Like with many diagnosing and monitoring requirements, we can see connection pooling (or the lack thereof) with SQL Trace or Extended Events.  For SQL Trace, the events to capture are Audit Login, Audit Logout, and RPC:Completed (with a filter on ObjectName like “sp_reset_connection”).  You would see similar results:

 

There are a few interesting aspects of the above example.  First off, the initial login for a different connection string and/or PID would result in a nonpooled connection (notice the EventSubClass indicates whether it is Pooled [2] or Nonpooled [1]).  But when that connection is released back to the pool (logout) then the subsequent SqlConnection.Open() calls can reuse the pooled connection (provided there is one available).  Another thing to notice is that the sp_reset_connection is called for pooled connections that are attempting to be reused.  What this does is ensure just that:  The connection’s context is reset (things like transactions being reset) so that there isn’t unwanted propagation from the previous connection.

As said above, this can also be monitored with Extended Events by capturing the login, logout, and the rpc_completed events (again, filtered on the object_name event field for the “sp_reset_connection” stored procedure).  This would look similar to the following:

 

We get virtually the same data we did in the SQL Trace, but in this case we have an event field named is_cached that will tell us whether the particular event is correlated to a pooled connection (much like above, the first isn’t a pooled connection and all subsequent logins were, with calls to sp_reset_connection in between).  Note: for both SQL Trace and XEvents, monitoring the login and logout events can extremely noisy and chatty, so consider filtering events.

Another document to refer to is the MSDN reference on SQL Server Connection Pooling (ADO.NET). There is great information on that article that wasn’t expanded on in this blog post.

I hope this post has shed some light on what connection pooling is, where and how it is handled, and how it is seen from the client and the SQL Server instance.  So now the next time you, the DBA, are approached with questioning on connection pooling, you know the basic underlying constructs of how it works, and can answer appropriately instead of assuming it is server-side functionality.

 

Thomas Stringer – SQL Server Premier Field Engineer

Twitter: @SQLife

Comments

• Anonymous
  October 08, 2013
  Awesome, awesome post! Wish I'd had this when I was starting off, great stuff!

• Anonymous
  October 08, 2013
  Thank you, Stephen!

• Anonymous
  November 17, 2013
  Hi, Under connection string if I dynamically set Workstation ID from application then  SQL Pooling mechanism will work or sql will create new connection as connection string is different to each other because of dynamic workstation ID

• Anonymous
  November 19, 2013
  nice one yar

• Anonymous
  February 09, 2014
  hie frds can any one suggest ....if interview ask wht is ur day to day activities..... as a dba...im hving 3 years of experience can any one frame best answer for this

• Anonymous
  November 04, 2014
  Great post, Thomas. I have a quick question,  so until when the pooled sessions are held in sql server for a given connection pool? Do they get timed out and cleared after a certain period when there are no incoming connections from the client?

• Anonymous
  September 01, 2015
  The comment has been removed

• Anonymous
  September 30, 2015
  The comment has been removed

• Anonymous
  November 04, 2015
  Check this post for a lot of detail information on exactly how connections and Connection pooling work.  Understand that Connection Pooling is not a SQL Server feature.  It is a client side feature implemented in various providers (e.g. ADO.Net).  Edward lays it out nicely @ www.simple-talk.com/.../improving-the-quality-of-sql-server-database-connections-in-the-cloud

• Anonymous
  October 02, 2016
  Great post! Very clear and demonstrative ~~Thank you :)

• Anonymous
  November 16, 2017
  Great info, thanks so much

• Anonymous
  February 07, 2018
  This post is magnificent.Superb!