Using Performance Monitor to monitor pooled connections

Question

I'm investigating this error from a MVC3 application that is failing under load:

"The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached."

The application is using the Repository pattern and Entity Framework, and my hunch is that it's not closing off connections properly. I want to be able to monitor the number of pooled connections on the SQL Server. Searching around leads me to believe that I can use these counters in Perfmon:

.NET CLR Data
.NET Data Provider for SQLServer

However both of them show and being disabled / grayed out.

I am running Perfmon directly on the server, and both ISS and SQL Server are running on the server. Any ideas why these counters would not be available?

I've also tried using SQL Profiler to monitor pooled connections, but the EventSubClass column isn't available for AuditLogin.

The second counter I believe is the correct one. What do you mean when you it's grayed out? As in you cannot add those monitors? Do you have admin access to the machine? — DavidN, Aug 16 '13 at 10:15
Yes, if I try to add those monitors, nothing happens. And I'm an admin on the machine — VincentH, Aug 16 '13 at 10:46

score 7 · Answer 1 · edited Sep 25 '19 at 17:12

7

You can run this from a SQL query windows to get a count and the details of current connections and session running on your SQL server.

select * FROM sys.dm_exec_sessions AS es  
INNER JOIN sys.dm_exec_connections AS ec  
ON es.session_id = ec.session_id

I've had trouble with pooled connections. They're hard to control. Explicitly closing them never seemed to work since they're under the control of .NET. The biggest reason we've run out of connections is uncommitted transactions. If a transaction is left uncommitted or rolled back for some reason, the connection, instead of being re-used, get's stuck in limbo, forcing .NET to open yet another connection to continue processing.

edited Sep 25 '19 at 17:12

Michael

8,362
6
61
88

answered Aug 16 '13 at 10:50

Brian

3,653
1
22
33

This looks useful - but does it tell me whether a connection is pooled or not? – VincentH Aug 16 '13 at 11:22
There doesn't seem to be a dynamic, queriable way of finding if a live connection is pooled. All the examples I saw relied on a) creating a table and a trigger to store connection data and b) using login event data (which contains IsPooled) to populate the table. There's an example here:http://www.sqlservercentral.com/Forums/Topic798425-146-1.aspx#bm798660 – Brian Aug 16 '13 at 11:55

score 1 · Answer 2 · edited Oct 10 '15 at 16:29

From the SQL side, the only viable way to review this is by setting up a login event trace. The "Event Subclass" column will tell you if the event happened using a pooled connection or not. With that, you can correlate the host, login, and application names and continue digging.

Audit Login Event Class

On the application side you can use the performance counters of ".NET DATA PROVIDER FOR SQL SERVER" on perfmon.

Performance Counters in ADO.NET

Using Performance Monitor to monitor pooled connections

2 Answers2