Timeout on Connection Pool

[Steve Harris]

I'm getting the following error message in an ASP.NET application:

Time Expired. 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.

I've read several KB articles on this and examined the server through the
performance monitor while this error was occurring.

SqlClient: _global_ Current # connection pools: 6
SqlClient: _global_ Current # pooled & nonpooled connections: 14
SqlClient: _global_ Current # pooled connections: 14

We do have six different apps running each with its own connection string.
The connection strings contain only the server, database uid and pwd
keywords. So I think I understand the Current # connection pools.

What I do not understand is how I'm running out of connections when there is
only 14 being used/pooled when the default is 100? What am I missing here?

Also, as a side note, does anyone know what the "SqlClient: Total # failed
commands" represents? This number was in the thousands on the performance
monitor but I cannot see anything in our log file. We do capture and log
every failed execution for review and there was nothing reported.

Thanks in advance.

Steve

 

[Miha Markic [MVP C#]]

Steve,

Are you closing connections?

 

[William \(Bill\) Vaughn]

First, the ADO.NET SQL Client performance counters are very unreliable so we
can't really depend on them. You can use the SQL Server pef counters
(general) to monitor the number of connections.

Remember that these timeouts occur for several reasons:
1) The amount of time that each connection takes exceeds the processing
power of the system given the number of user requests. This means that
during testing when the load is low, the server can handle the operations
and free a connection for reuse by the time another connection is requested.
Under production loads (when a lot of users are trying to get in), if the
processing required takes too long or the server is busy with other stuff,
the connection pooling mechanism has to generate another connection in the
pool to handle the additional user. If this trend continues the pool
eventually fills and no further connections will be permitted.Note that as
more users are added, the amount of time that it takes to process each
individual user might (just might) go up. If it does, this exacerbates the
situation. You might need smarter/faster code or more horsepower under the
hood, more RAM or a better data access strategy.
2) The connections are not getting closed by the application when the
processing is stopped. This is a very common mistake--especially when your
code handles the Open and Close on its own (as when using the DataReader).
This problem is especially common when you pass a DataReader between code
scopes and don't use CommandBehavior.CloseConnection.
3) An operation you execute is waiting for some resource that's held by
another connection. This "deadly embrace" situation can easily fill the
connection pool.

I also wrote a whitepaper about this and discuss it in my book. See
www.betav.com for details.

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

 

[Sahil Malik]

This is a fantastic answer, and really hits it on the head. Typical Bill
Vaughn Quality.

I just have one more thing to add --

This is why proper data access layer, and proper data access architecture is
so important. And really it takes a lot to know about ADO.NET to come up
with the perfectly right architecture. In my experience, I have seen more
screwed up architectures, than good ones. The mostest screwed up
architecture I saw was when this company insisted on a pure datareader only
data access theme - because they felt those were faster. Complete nonsense
in my opinion. Faster or slower depends on what you are gonna use it for. I
am trying to give this real good coverage in my upcoming book.

- Sahil Malik
http://codebetter.com/blogs/sahil.malik/

 

[Steve Harris]

Thanks Bill for your great response.

One question on Point (2), I thought that CommandBehavior.CloseConnection
would close the connection when the DataReader is closed. If this is not the
case, how is the connection closed?

Regards,
Steve

 

[Kevin Yu [MSFT]]

Great answer, Bill.

Hi Steve,

What you think is correct. In point 2, Bill means that the connection is
not closed if we open a DataReader on a connection and we didn't set the
CommandBehavior to CloseConnection. HTH.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

 

[William \(Bill\) Vaughn]

Setting CommandBehavior.CloseConnection does nothing if you don't close the
DataReader when you're done with the data.

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________