Misconceptions around connection pooling
With this post I want to explain certain details about the way connection pooling works with SQL Server based applications. In the last months. it has been a recurrent topic I’ve had to discuss with some customers because of their misconceptions on the topic.
For the purpose of illustrating my explanations here, I’ve built a sample Windows Forms app whose only ability is that of opening and closing a unique instance of a SqlConnection object to send different statements to a given instance of SQL Server while leveraging ADO.NET’s connection pooling.
The code is pretty simple and is shown below:
private void btnConnect_Click(object sender, EventArgs e)
{
SqlConnection sqlConn = new SqlConnection(txtConnectionString.Text);
SqlCommand sqlComm = new SqlCommand(@"BEGIN TRANSACTION;
CREATE TABLE ##TablaTemporal (c1 nchar(4000));
INSERT INTO ##TablaTemporal SELECT name FROM master.sys.all_columns;
INSERT INTO ##TablaTemporal SELECT name FROM master.sys.all_columns;
INSERT INTO ##TablaTemporal SELECT name FROM master.sys.all_columns;
INSERT INTO ##TablaTemporal SELECT name FROM master.sys.all_columns;
INSERT INTO ##TablaTemporal SELECT name FROM master.sys.all_columns", sqlConn);
sqlConn.Open();
sqlComm.ExecuteNonQuery();
sqlConn.Close();
sqlConn.Open();
sqlComm.CommandText = "SELECT 1";
sqlComm.ExecuteNonQuery();
sqlConn.Close();
sqlConn.Open();
sqlComm.CommandText = "SELECT 1";
sqlComm.ExecuteNonQuery();
sqlConn.Close();
}
If I run that application, it shows a form like the following:
By clicking the Connect button, it will run its event handling function which is associated to the click event. The code of that function comprises of the following lines:
By the time it calls the Open method of the sqlConn object for the first time (the one just highlighted above), the TDS (Tabular Data Stream, which is the proprietary application protocol) layer on the client side prepares a packet which is sent to the SQL Server. Off that packet, the eight first bytes are known as the header of the packet, and are those I‘ve highlighted below:
The first byte of the header indicates what type of message this particular packet is part of. 0x12 corresponds to an incoming prelogin message. The second byte corresponds to the status of the package. That status field is the one through which the client will indicate whether or not the connection is pooled. In this case, it contains 0x1 which means an EOM (i.e. this is the last packet of the prelogin message). Notice that, with this information, SQL Server is being told by the client that this connection is not pooled, so it has to treat it as a non pooled connection. It may sound weird but it makes perfect sense. If it isn’t so clear, let me help you by describing it this way: In order for a non existing connection to become part of a pool of connections, it has to be created/established first of all, and at that point it hasn’t been part of any pool yet. That would be the point where we are at right now.
This time I won’t stop to describe what the remaining 6 bytes of the header are used for. That is useless for the purpose of explaining the topic I want to talk about today.
During the process of accepting a new connection, SQL Server receives several packets of prelogin type, containing certain information client and server must exchange prior to the login itself. After the prelogin phase has completed a login packet is received (notices that the first byte in the packet header is 0x10 which is the one value reserved to identify login packets). As with the prelogin packets, received until now, the status field (second byte of this header) is set to 0x1 (EOM). Still, no reference whatsoever to determine that this connection is participating in a pool created and maintained on the client side:
Just after the login phase has completed successfully, SQL Server checks if somebody is interested in being informed of such event (it could be through the SQL Trace event available to that effect – Audit Login – or the one offered by Extended Events – audit_event –.) In case there’s anyone interested in the outcome of such event, SQL shows whether the status field in that last TDS packet indicates that the connection was part of a pool of connections on the client side or not.
If you use the Audit Login event of SQL Trace, this fact is reflected through the value stored in the EventSubclass column (being 1 for representing Nonpooled connections, and 2 for those identified as Pooled). As you can see below, in my example, after that first connection opens, the action is identified as a successful login for a connection that wasn’t previously part of a pool:
Next line of code executed in my sample client application executes a batch consisting of the explicit initialization of a transaction, followed by the creation of a global temporary table in which thousands of rows are inserted. All that operations takes several seconds in my lab environment:
When the ExecuteNonQuery method is invoked, SQL Server receives the following TDS packet which contains the query it has to run and some metadata about that packet, embedded in its header. This time, the packet type is 0x01 which means this packet refers to a SQL batch. The second byte in the header, as has been the case up until now, just has the EOM flag enabled:
With that information, SQL Server knows it has to process that packet as the initial request to process a SQL batch. Once it has completed, it sends back a response message to the client application indicating successful completion and returning control to it.
Since my SQL Trace definition also included the BatchStarting and BatchCompleted events of the SQL category, my trace records the starting and completion of the batch received from the client application:
Now, it is time for the client application to call the Close method on the connection object.
But, as a result of that call to the Close method, SQL Server receives nothing. Once the connection was open in the first place, it was added to the pool it was part of. When the application calls its Close method, it is just letting the connection pool management layer know, the application code is not interested any further in keeping the connection open, but the connection pool manager itself is, so it doesn’t close the connection, nor tells SQL Server nothing about it. However, the connection pool manager, marks the connection as “one that has been used and then returned to the pool”.
You may be one of the developers who expect (or even believe) that it is at this point, when the connection pool manager lets SQL Server know the connection has been “logically” closed by the application and so SQL should run some cleanup code on the server side, in order to leave that connection as it was when it had been fresh created. If that is your case, I’m pleased to let you know that’s absolutely wrong. At this point, SQL Server receives nothing through this connection.
Next instruction that executes in the client side is the Open on the same connection object.
This time, the call to Open will not create a new connection to SQL Server, because the connection pool management layer already has the previously used connection idle (i.e. nobody from the application is referencing it any longer, after the close method was called).
Now, you may be among that other group of developers who believe that, it is a this point when the connection pool manager lets SQL Server know that the connection is being logically re-opened and so it has to be cleaned up, garbage collected, re-initialized or whatever term you prefer to use to describe that process. Again, I’m sorry to tell you this is not the case.
Next instruction in the client’s code sets the command text to a simple SELECT 1, and then invokes ExecuteNonQuery on the command. It is only at this point that SQL Server receives the first TDS packet after the last one it received when the previous query was run. Let’s see how that packet looks like:
This time, the message type in the header (0x01) corresponds to a SQL batch again, and we can see the SELECT 1 in the input buffer. There is a difference in the status field this time. It is not 0x01 (0001 in binary) as in previous cases, but 0x09 (1001 in binary). Bit 0x08 in that bitmask field is set this time. And what that flag tells SQL is this: because I’m reusing this connection which I picked up from a connection pool, it has been used by somebody else before me, and I don’t want to assume anything that has been done over this connection in the past, make sure you cleanup the context of this connection, and revalidate the authorization of the principal, before you process the following SQL batch.
In order to achieve that, SQL Server runs a special pseudo stored procedure implemented inside SQL Server’s executable code. That is the well known sp_reset_connection. That stored procedure does the following tasks: 1) cleans up the session context (i.e. issues a rollback on any active transaction that may have been left open through this session, drops any local temporary table which has been left behind by the previous owner of the connection, closes any open cursors and deallocates the resources it has been using, resets the CONTEXT_INFO, etc); 2) to those who might be interested, it notifies them of the occurrence of a successful logout (one that doesn’t physically closes the connection though, i.e. one whose EventSubclass column is set to 2 - Pooled); 3) initiates the process of redoing the login.
As can be observed in the SQL Trace below, that I captured during an execution of my sample application, the Audit Logout event indicates it is a logout that doesn’t tear down the connection. And that can be concluded from the value 2 – Pooled, shown in the EventSubclass column. After the Audit Logout,
Notice also that Reads (552240) and Writes (45830) accounted to the Audit Logout event are, roughly, aggregated values that sum up all the Reads/Writes consumed by the batch (405916/23338) and those consumed by the cleanup work made by sp_reset_connection (146320/22492). That doesn’t necessary is the case for Duration, because it will depend on the time elapsed between the first call to Open and the second call to ExecuteNonQuery.
Something worth knowing is that certain statistical values accounted for each session are not reset by sp_reset_connection. That is the case of Reads and Writes. While others, like CPU and Duration, are reset on every call to sp_reset_connection.
What I want to emphasize to SQL Server developers is that just because you are using connections that participate in a connection pool, you cannot forget about good programming practices. The price you have to pay for many poor programming errors is even higher when you are using connection pooling. For example, in a scenario where every connection is physically open whenever you call the Open method of the connection object, and also physically closed immediately after you call its Close method, leaving a transaction open is something that will be resolved earlier (when the connection’s Close method is invoked). If that happens with a connection that is pooled, the orphan transaction won’t be rolled back until someone else doesn’t pick up that idle connection from the pool and runs something over it.
Closing server cursors, deallocating them, dropping temporary objects or resolving your transactions is more important than ever when you use any connection pooling mechanism.
Believing that by the time your program calls Close on an open connection that is pooled, will immediately take care of cleaning up the mess you may have created on the server is simply wrong.
Also, believing that just because you have enabled connection pooling you are exempt from doing things properly, it is absolutely wrong.
And the last misconception I want to highlight is one based on something I detected in one customer a couple of weeks ago. Their Java based application created several hundred connections which were pooled. But the application never called close on them, so any new incoming request for a connection from the pool would always have to create a freshly new one. Q: Do you know how do we call that in my home town? A: A completely useless connection pool. 
Comments
Anonymous
June 05, 2012
Why set explictly set pooling to true? You get that by default.Anonymous
June 05, 2012
That is absolutely correct Jonathan. I just set it, so that I had something to highlight in the screenshot where the connection string is displayed, so that I could emphasize I was, indeed, using connection pooling.Anonymous
June 09, 2012
I was thinking that when i call the Close() method, the connection is simply returned to the pool and destroyed only after a time ( between 1 up to 10 minutes this delay depending of the load of the SQL Server or of the computer where SQL Server is running ).During this delay, the connection can be reused nearly immediately, avoiding 2 operations needing resources. A way to avoid too many connections/disconnections ... But maybe i am going wrong. Why not to set Min Pool Size to 1 and Max Pool Size to 2 ? ( Max Pool Size must always be higher to Min Pool Size )Anonymous
June 18, 2012
(This comment has been deleted per user request)