Verifying connection pooling server side with CONTEXT_INFO on

Question

I'm working with a 3 tiered application, Microsoft Dynamics AX, where the middle tier maintains connections to a SQL Server. Several clients connect to this middle tier server.

The middle tier server typically has several connections open to the SQL Server, so I'm pretty sure they are being pooled, however there is no documentation available as to how this is implemented.

Normally we cannot relate SPIDs to users or client applications, but there is an option where we can set a registry key (specific to Microsoft Dynamics AX) which makes this information available in the context_info field of sys.dm_exec_sessions.

Again, there is no documentation about how this is implemented. The only information we have on this is a vague blog entry on MSDN.

The post mentions

Adding this information has a small performance overhead.

So as we don't know any of the implementation details such as:

1. Is the information somehow included in the connection string or is this done by SET CONTEXT_INFO?
2. When are connections reused?
3. What exact impact can be expected

Is there any way to determine server side how the connection pooling is working and what the impact of the context_info is?

update:
Using this query from here

SELECT des.program_name,
       des.login_name,
       des.host_name,
--       der.database_id,
       COUNT(des.session_id) AS [Connections]
FROM sys.dm_exec_sessions des
INNER JOIN sys.dm_exec_connections DEC
        ON des.session_id = DEC.session_id
WHERE des.is_user_process = 1
--AND des.status <> 'running'
GROUP BY des.program_name,
         des.login_name,
         des.host_name
--         ,der.database_id
HAVING COUNT(des.session_id) > 2
ORDER BY COUNT(des.session_id) DESC

I can see connection pooling is used.

Answer 1

First, CONTEXT_INFO is a property of the session, not the connection. It gets reset by sp_reset_connection when the same session is reused and the first batch is executed. It seems that CONTEXT_INFO was not reset in SQL Server 2000 and possibly earlier versions, but starting with SQL Server 2005 it is definitely reset to NULL.

Part of the confusion here is that the question is specific to "Microsoft Dynamics AX" since general .NET programming wouldn't have the registry key noted in that blog article. Also, the information that Microsoft Dynamics is then storing in CONTEXT_INFO is its application session details, which have nothing to do with SQL Server SPIDs and cannot be used to infer that connection pooling is occurring since the application session will naturally span multiple connections as well as SPIDs.

The mechanism being used to set CONTEXT_INFO pretty much has to be a separate, additional query executed prior to any other queries for that session. Something along the lines of:

SqlConnection _Connection = new SqlConnection("{connection-string}");
_Connection.Open();

if(_IsConnectionContextRegistryKeySet)
{
  SqlCommand _Command = _Connection.CreateCommand();
  _Command.CommandType = CommandType.Text;

  _Command.CommandText = @"DECLARE @BinaryInfo VARBINARY(128);
                           SET @BinaryInfo = CONVERT(VARBINARY(128), @StringInfo);
                           SET CONTEXT_INFO @BinaryInfo;";

  SqlParameter _ParamInfo = new SqlParameter("@StringInfo", SqlDbType.VarChar, 100);
  _ParamInfo.Value = String.Format("{0} {1} {2}...", AXuserID, AXsessionID, ...);
  _Command.Parameters.Add(_ParamInfo);

  _Command.ExecuteNonQuery();
  _Command.Dispose();
}

Hence, the small amount of additional overhead incurred for enabling this info to be set comes from the execution of this additional query.

Second, you can test for connection pooling by using SQL Server Profiler. Select the "RPC:Completed" event in the "Stored Procedures" category, make sure that "TextData", "ClientProcessID", and "SPID" are checked for that event (at the very least, you can select other columns if you like). Then, go to "Column Filters", select "TextData", and in the "Like" condition add the following condition: exec sp[_]reset[_]connection. Now run that trace. If you see instances of exec sp_reset_connection coming through, then that is due to connection pooling being used.

Additionally, there are two side-effects of connection pooling that may or may not show up in the DMVs depending on how many connections are being requested. The following query should capture many / most of the connections that are pooled (please note that it has nothing to do with CONTEXT_INFO):

SELECT sssn.login_time,
       DATEDIFF(MILLISECOND, conn.connect_time, sssn.login_time)
                  AS [MillisecondsBetweenConnectionAndSessionStart],
       conn.*
FROM sys.dm_exec_connections conn
INNER JOIN sys.dm_exec_sessions sssn
        ON sssn.session_id = conn.session_id
WHERE conn.session_id <> conn.most_recent_session_id
OR    DATEDIFF(MILLISECOND, conn.connect_time, sssn.login_time) > 50
ORDER BY conn.connect_time;

This query looks for the following indications of connection pooling being used:

• Current session_id is not the same as the prior session_id. If these two IDs are the same then it may or may not be a connection that is using pooling, since the same SPID can be reused. But, if they are different, then that can only be the result of connection pooling.
• Time between the connection being made and time that the session starts is over 50 milliseconds (though that threshold might vary by system). Usually the first session to get created upon a connection is less than 30 milliseconds after the connection, but it "generally" shouldn't be above 50, even if executing multiple SqlCommands.

Along similar lines, it should also be possible to test for connection pooling by creating a temp table, and every few seconds, capturing both [session_id] (INT) and [connection_id] (UNIQUEIDENTIFIER) from sys.dm_exec_connections. Then, just look for rows that have the same connection_id but different session_id.

Finally, the query posted in the question is not valid for indicating connection pooling with respect to most web applications. The issue here is that typically, the properties of "program_name", "login_name", and "host_name" would be the same for all connections made by web/app server (hence the need for the per-processor / per-core licensing models instead of just having the CAL model).

Answer 2

Regarding Connection Pooling, Thomas Stringer has posted: Link

The simple thing to know is: "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."

The CONTEXT_INFO, which is stored in binary, needs to be set by the application. CONTEXT_INFO is just a small bucket available to contain useful information such as the user's Name / ID / Login. (Or whatever else you want to stuff in the small space available.)

The overhead is small, but it is an extra step to insert and extract the BINARY data in CONTEXT_INFO. (But useful for systems that run under a service account, so that you can know whose connection this is at the moment.)

Thanks to srutzky for clarifying that a pooled connection is reset, but it is not dropped. At least not immediately.

A post by Nacho Alonso Portillo offers clarification: Link

In part it says the "sp_reset_connection... does the following tasks: 1) cleans up the session context ... which includes ... resets the CONTEXT_INFO; ... 2) notifies them of the occurrence of a successful logout... 3) initiates the process of redoing the login."