Sample Header Ad - 728x90

Verifying connection pooling server side with CONTEXT_INFO on

7 votes
2 answers
2284 views
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? 1. When are connections reused? 1. 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.
Asked by Tom V (15752 rep)
May 12, 2015, 01:28 PM
Last activity: Mar 18, 2023, 12:00 PM