SQL Server DBA

ASP.NET Connection Pooling, a DBA’s Perspective

Error: 18056, Severity: 20, State: 29.
The client was unable to reuse a session with SPID 56, which had been reset for connection pooling.


On any normal day, we receive this error with regular frequency. Our standard response is to register it and move on. This error would not fall under the umbrella of a typical DBA, but if it is important enough to be logged by SQL Server then it is important to me to understand it. By understanding the events that lead to the error I can make an informed decision as to how to deal with this based on whether-or-not it impacts the performance of SQL Server.



Establishing a connection to SQL Server is an expensive process, it requires the creation of a physical channel, an initial handshake between the server and application, parsing of the connection string and subsequent authentication. To appreciate how long this can take, jump into an interactive Python session and establish a connection to SQL Server:

import pyodbc
sqlConn = pyodbc.connect(r'DRIVER={SQL Server};Server=MSSQLSERVER;Database=master;Trusted_Connection=yes;')

In the interactive session you can get a real appreciation for how long this can take; a successful connection will take up to a few seconds, where an unsuccessful attempt may take quite a few seconds. Now imagine yourself browsing your favourite website, and imagine how frustrating it would be if every time you make a request for data the application had to establish, maintain and finally close a database connection. Now multiply this many times over to account for the other 300 users who are also browsing your favourite website. Thankfully, it doesn’t have to be this way.

In the real-world most application use only a small number of connections to the SQL Server. So while you are browsing your favourite site, the web server is maintaining a small number of available connections to SQL Server. This is known as a connection pool. When you make a request for data, the connection manager will provide you with an available live connection, and when you are finished this connection is returned to the pool for re-use. In this way, the application doesn’t need to go through the expensive process to establish and tear-down connections every time a request is made. A connection pool is associated with a single connection string, so if you had multiple connection strings then you could maintain an equivalent number of connection pools.

The pool of in-use and available connections is maintained by the connection pooler. By default, if a connection has been idle for ~ 5 minutes it will be released from the connection pool and this session returned to SQL Server. SQL Server may also cut a connection, in which case the connection pooler will be advised of this the next time it attempts to communicate with SQL Server throwing a “the client was unable to reuse the session…” exception.

Connection Pooling is important for applications, as it reduces overhead and streamlines the user experience. However, maintaining open sessions comes with a memory and resource cost to SQL Server. Connection Pools can become fragmented, where you have ‘orphaned’ or ‘phantom’ connections that still hold resources but can not be used. Fragmented connection pools are common when an application accumulates many connection pools (perhaps through fine-grained security policies for individual users) or when unique connection strings (and hence connection pools) are used for each individual database.

The long and the short of this is that the above error is part of the normal, healthy interaction between SQL Server and applications that make use of connection pools. The error indicates that the connection pool no longer has ownership of a session which has been severed by SQL Server. From the application perspective, there will be a small delay while the connection pool processes this response and assigns the request another available connection. From the DBA perspective, SQL Server’s precious resources are being preserved and maintained effectively helping to maintain the health of our critical server.

There is a great blog post written by Thomas Stringer, that demonstrates connection pooling in-use, how to query connection pools from within SQL Server and how to monitor this with extended events.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s