Re: connection pooling

Tech-Archive recommends: Fix windows errors by optimizing your registry

Hi Bill,

Thanks for the explanation, I think it makes a lot of sense. I just have two
general questions:

1. When (if ever) would you consider turning connection pooling off?

2. Would it help in some cases if the application where to be architected to
use multiple connection pools, each pool dedicated to a process? For example,
would it make sense to dedicate a pool to a critical but low volume process,
so it won't be affected by a high-volume/less critical process overloading
its connection pool?




"William (Bill) Vaughn" wrote:

> A web server has a finite capacity. That is, when a page is loaded it opens as many connections as it needs at the same time. Due to the single-threaded nature of these application (pages), a single connection is usually enough. Generally, the page opens the connection just in time and closes it as soon as it's no longer needed. This assumes that the connectionstring does not change for each page. Using this approach, the connection pool reuses the connection for each instance of the page as needed--it's rarely closed and released by the pooling mechanism.
> If you're finding that the number of connections in the pool (and I assume you have a single pool--although there could be more than one), is growing over time, there is something wrong.
> a.. You're doing more processing in the instance than the server can handle before another instance of the page is loaded with an additional request.
> b.. You're not closing the connections so when your instance ends, the connection remains open in the pool and unavailable for other instances.
> c.. You're not committing or rolling back the transaction used by the connection which has the same result--the pool overflows.
> d.. You have TSQL debugging enabled. This is an issue that causes the pools to overflow.
> The first two issues are fairly common. The first can be solved by tuning the application to be more efficient. This might mean fetching fewer rows, improving the performance of the stored procedures, adding or removing indexes, or simply beefing up the hardware and RAM. The second is typically caused by sloppy connection management--especially when you're using DataReaders. If you don't make sure the Connection associated with the DataReader is closed, you're pooched.
>
> We've seen a lot of heavily used web sites use 20-50 connections with a powerful hardware system. This single system can handle several hundred users with ease if the code is written efficiently. Once you exceed the capacity of the hardware, things fall apart quite rapidly.
>
> We've discussed this many times before and I've written several whitepapers over the years on this subject. See www.betav.com for this content.
> 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.
> __________________________________
>
> "Val P" <ValP@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:BFCAAC75-62D9-4193-AF51-56DAA33C3F4D@xxxxxxxxxxxxxxxx
> >
> >
> >> The only definitive answer you will find will be through testing. Use a
> >> web stress/performance tool (Application Center Test may get you
> >> started, but there are more sophisticated tools) that allows you to
> >> simulate 5000 simultaneous connetions, and test with various settings of
> >> the connection pool, while monitoring performance counters to see
> >> resource usage.
> >
> > Yes, we have developed in-house tools to stress the application. We are
> > running out of connections in the pool. We could tune to accomodate the 5000
> > connection goal, but what would that cost us in that 80% of the time when we
> > only have 10 connections at a time? I am talking about impact on the insides
> > of ado.net and other things that are not easy to monitor (internal table
> > lookups, management overhead, transient memory usage, etc)
> >
> > Would it make more sense to do without connection pooling for this type of
> > load?
> >
> >
.

Relevant Pages

  • Re: Close and Dispose argument
    ... > fact that even Microsoft is not clear on the issue. ... > connections from the pool is still a debate. ... > method removes the conneciton from the connection pool". ... > Your issue was forwarded to us here at Microsoft Courseware Support. ...
    (microsoft.public.dotnet.framework.adonet)
  • Close and Dispose argument
    ... here is an email conversation between me and Microsoft ... connections from the pool is still a debate. ... Programming with Microsoft ADO.NET" module 2, p 18: "Calling the Dispose ... method removes the conneciton from the connection pool". ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: MinPoolSize behaviour
    ... Min Pool Size, The first time you open a connection we will open the 1 ... > Is the pool created forever? ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Q:Multithreaded ADO.Net Connections Are Non Pooled?
    ... different you get a new pool. ... > the process/app domain differences, does running on a thread created by ... If you're using SSPI, each connection must be ... >> connect to the SQL Server machine, ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Connection pooling and changed password issue
    ... There is no "clear the pool" or "drop the pool" until ADO.NET 2.0. ... > Hi - so a previously made connection with the old credentials will still ... > "William Vaughn" wrote: ... >>> username/password then the connection pool will be used and the same ...
    (microsoft.public.dotnet.framework.adonet)