A variation of this question came up a few weeks ago on a mail list that I subscribe to and it seems to come up a lot. "Can SQL Server distinguish a which connections use connection pooling?" The answer depends on how you ask the question, and I've tried to ask it in such a way that it's as ambiguous as it usually sounds. Here's what's happening.
Connection pooling is implemented almost entirely in the client stack. I'll refer to ADO.NET here (because it has some nice, client-side, perfmon counters) but this is true of OLE DB, ODBC, and JDBC as well, although ADO.NET uses different code. You can see the code with .NET reflector and trace it using the client-side trace. Connection pooling is implemented on a per-client, per-appdomain basis in ADO.NET, and all the connection string parameters must match for a pooled connection to be reused. In ADO.NET, connection pooling is on by default, unless you turn if off in the connection string.
When connection pooling opens a connection for the first time, it actually goes through the TDS message exchange to log in to the server. When the client-side code requests a connection close (or even connection dispose), the connection is not removed from the pool, log out does not occur on the server. Instead, you can think of the connection being "active" on the server, but not in use in the client's pool. When the client reuses the connection again, instead of logging on, the first batch of sQL executed causes a RESETCONNECTION bit to be set on the first TDS packet to the sent (there is also a related RESETCONNECTIONSKIPTRANSACTION bit, but that's not relevent for this discussion). This causes SQL Server to run the system stored procedure sp_reset_connection on the server's session. Running sp_reset_connection is a lot faster than redoing the entire login protocol exchange, that's the time-savings of connection pooling. If you consider sp_reset_connection as part of "connection pooling" (it's certainly essential for it to work properly), you can consider (maybe, during an argument) that part of "connection pooling" to be implemented in the server. I guess. But the client implements the pools, recycles the connections, and sends the reset bit for the server to act on.
Folks that think SQL Server can distinguish which connections use pooling in the client usually point to a the fact that the AuditLogin/AuditLogoff events in profiler indicate in the Event Subclass field whether or not a connection is pooled. And indeed they do. BTW, there is analogous information in XEvents.
So I set up a short test program that does the following 10 times (remember, connection pooling is on by default).This was run on .NET 4.0 client and SQL Server 2012 server:
Create a new SqlConnection and SqlCommand
Set the connection string
Open the connection
Execute the command
Call close (and dispose for good measure) on the connection
The first time through, on the initial connnection to the server, Profiler reports a Non-pooled connection. Each subsequent time through, when the command is issued (remember, RESETCONNECTION is a bit on the first message in the batch, not part of the login message), Profiler reports the following three messages, followed by the SQL command that you actually execute:
Logout (of a pooled connection)
Call to sp_reset_connection
Login (of a pooled connection)
So profiler reports a pooled logout/login (on a pooled connection) when we don't have a pooled connection (according to profiler) that can be logged out. A couple of other nit-picky observations:
If I execute the command the first time but only open and close/dispose the connection the 2-10th times (i.e. don't execute the command), no Logout/Login is reported.
If I don't execute the command the first time (only open and close/dispose), but do execute the command on the second pass, logout/sp_reset_connection/login still happens.
There are also some perfmon counters that show what's going on. These are:
.NET Data Provider for SqlClient (client connection pool information, per process)
SQL Server:General statistics (counters are logins/sec, logouts/sec, connection resets/sec, logical connections)
When I look at these counters while running the test, they reflect what you expect. On the first time around the loop (the "real" login), I get 1 pooled connection on the client counters, although that's the one that profiler reported as Unpooled. There is 1 login as far as the server counters are concerned, and 9 connection resets, and 1 logout (at program termination). There is never more than 1 additional logical connection produced by the program.
So, back to our original question, "Can SQL Server distinguish a which connections use connection pooling". If I HAD to answer yes or no, I'd say NO, not until the client sends the RESETCONNECTION bit. Once a connection is reset, SQL Server could *surmise* that a connection was using pooling, but not until. I haven't located a place that the server keeps such information on a per-connection basis, although sys.dm_exec_sessions, sysprocesses, and sp_who, have a session state of "Dormant" when sp_reset_connection is being/has been run.
But, if the information in Profiler and XEvents Login/Logout useful? I'd say YES, it helps distinguish "real" logins/logouts from resets. Thanks to Erland Sommarskog for his valuable input in the original discussion. Hope this was helpful.
@bobbeauch
