What is connection pooling? Lets start with a definition from MSDN.

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.

First, I Needed a Way to Test Connection Pooling

Once again I reached for my good friend SQLQueryStress. SQLQueryStress is written in .NET and gives me the ability to enable or disable connection pooling in the options menu which is just what the doctor ordered.

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.

Next, I needed my Extended Events Session

I am fairly new to using Extended Events so the first thing I did was fire up the wizard and see what I had to work with out of the box. Most of what I came up with was right in front of me as default templates and configurations in the wizard. Rather than post screenshots of me running through the wizard, I am just going to share the T-SQL script that I used to create the XE session. Once it has been created you can check it out in the SSMS GUI if you so desire. The main thing to understand about this session is that I am capturing the LOGIN event as it contains all of the information I need to detect connection pooling.

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.

Test Method

1. Start the XE session. Right click on the ConnectionPooling session in SSMS and select “Start Session”.

   

2. Start the SQLQueryStress test. Its as simple as hitting the big “GO” button.

   

3. Stop the XE session. Right click on the ConnectionPooling session in SSMS and select “Stop Session”

   

4. Analyze the results.
   1. Open the XEL file created by the ConnectionPooling XE session.
   2. Add the columns client_host_name, client_app_name and is_cached to the display.
   3. Group the data by client_host_name, client_app_name and is_cached.
   4. Add an aggregation to make the counts stand out.

   It will be much clearer in the results posted below.

Based on my test I would expect a total of 50 queries from my SQLQueryStress test. 10 queries run across 5 threads = 50 queries run. Because of some other things SQLQueryStress does behind the scenes these numbers are not exact, but they are close to 50. What really stands out is when connection pooling is enabled or disabled for the following tests. You can definitely see connection pooling in action here.

Test Results: Connection Pooling Disabled in SQLQueryStress

• “.Net SqlClient Data Provider” is the client_app_name for SQLQueryStress.
• You can see that is_cached = false for all logins from SQLQueryStress, indicating that connection pooling is not enabled here.
• Makes sense because that’s how SQLQueryStress was configured for this test run.

Test Results: Connection Pooling Enabled in SQLQueryStress

• Once again “.Net SqlClient Data Provider” is the client_app_name for SQLQueryStress.
• You can see in this case that is_cached = false for only 7 of the logins, and is_cached = true for 46.
• The 7 logins where is_cached = false were not using pooled connections. These are the initial connections from SQLQueryStress.
• The 46 logins where is_cached = true are using pooled connections. This is connection pooling in action.

Conclusion

Due to the cost of opening and closing connections, connection pooling is an important component when it comes to application performance. Extended Events give us all the tools we need as DBA’s to see it in action. This is a very simple test and I highly recommend playing around with it a bit. I’m thinking this is just the tip of the iceberg with Extended Events for me.