Connecting to a data source can be time consuming. To minimize the cost of opening connections, ADO.NET uses an optimization technique called connection pooling, which minimizes the cost of repeatedly opening and closing connections. Connection pooling is handled differently for the .NET Framework data providers.
Opening connections is a very expensive and time consuming operation. Connection pooling helps alleviate that by providing a reusable “pool” of connections that your application can share. Connection pooling is usually a good thing. Unfortunately I have found that it’s sometimes not very well understood. This blog post starts with a short story…
I was working with my developers on a performance problem recently and I posed the question, “Is the application using connection pooling?”, and the developers weren’t really sure. So we did some reading about connection pooling on MSDN, and based on the connection string we thought we were but we wanted confirmation.
One way to validate connection pooling is from the application server. There are a number of perfmon counters available to monitor connection pooling from the application server side. My problem was that I didn’t have access to the application servers. So as a DBA I went looking for a way to detect connection pooling from SQL Server, which was when I found this article that mentioned using Extended Events (near the bottom of the post).
Thank you to the SQL Server Premier Field Engineering blog for posting that. The is_cached event field is the one that identifies a pooled connection and it’s just what I needed.
This was one of the first times I have used Extended Events for something other than just tinkering around so I had to share. In the remainder of this post I will illustrate how to use Extended Events to detect connection pooling.
I setup SQLQueryStress to connect to my local database server and run a simple query against the AdventureWorks2012 database. It is set to run 10 iterations across 5 different threads. This should produce 50 individual queries.
Note: If you want to use the script to create an XE session, make sure you change the output file path to a valid path on your server.
CREATE EVENT SESSION [ConnectionPooling] ON SERVER ADD EVENT sqlserver.connectivity_ring_buffer_recorded (
ACTION(sqlserver.client_app_name, sqlserver.client_connection_id, sqlserver.client_hostname, sqlserver.context_info,
sqlserver.database_name, sqlserver.server_principal_name, sqlserver.session_id, sqlserver.sql_text)
)
,ADD EVENT sqlserver.LOGIN (
SET collect_options_text = (1) ACTION(sqlserver.client_app_name, sqlserver.client_connection_id, sqlserver.client_hostname,
sqlserver.context_info, sqlserver.database_name, sqlserver.server_instance_name, sqlserver.server_principal_name,
sqlserver.sql_text)
) ADD TARGET package0.event_file (SET filename = N'C:\SQL\MSSQL11.INST1\MSSQL\Log\ConnectionPooling.xel')
WITH (
MAX_MEMORY = 4096 KB
,EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS
,MAX_DISPATCH_LATENCY = 30 SECONDS
,MAX_EVENT_SIZE = 0 KB
,MEMORY_PARTITION_MODE = NONE
,TRACK_CAUSALITY = ON
,STARTUP_STATE = OFF
) ;
My test is setup. My XE session is ready to go. At this point I am ready to begin my test.