Re: connection pooling

Just wanted to mention that connection pooling can be just as useful with
Windows forms apps. Large enterprises using Windows forms clients and remote
server components can also benefit. It's not the type of applicaiton, it's
the architecture that determines whether you need connection pooling.

"William (Bill) Vaughn" <billvaRemoveThis@xxxxxxxxxx> wrote in message
news:%23k$U42eUFHA.3436@xxxxxxxxxxxxxxxxxxxxxxx
> 1) In a Windows Forms application connection pooling is not nearly as
> interesting (or useful). In an ASP application, it's pretty much
> essential. There are few situations that call for manual connection
> management.
>
> 2) Yes, it's often suggested that you build a separate pool for each
> application function that conflicts or competes with other functions. So
> the answer to "2" is yes...
>
>
> --
> ____________________________________
> 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:AB479E27-5E4A-47E4-8F0E-82F1316F3142@xxxxxxxxxxxxxxxx
>> 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: Random database connectivity failure
    ... We can’t find any problems with the pooling, that it uses to many resources. ... pointing to its not a pool problem. ... The server is only used for this web-application, ... white paper on handling the connection pool on my website. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Problem with VPN setup
    ... I used unassigned addresses to create a static pool. ... if I am using RRAS for VPN it would not be many anyway. ... You can see the connection under "Remote Access ... create a VPN server. ...
    (microsoft.public.windows.server.general)
  • Re: Connection Pool Issue
    ... Max pool size 10 seems low... ... My first thought would be that connection poolling is actually disabled on A ... Server A and Server B both are running Windows 2000 Server sp4, ... all the usual places for enabling connection pooling. ...
    (microsoft.public.dotnet.framework.aspnet.webservices)
  • Re: Internal connection fatal error
    ... could spend quite a bit of time working on streamlining your server, ... I also do a perf talk at the Connections conference that discusses how to ... >> 80 connections in the pool tells me this is a heavily loaded system. ... >> Yes, if an operation fails at the server, the connection can be broken. ...
    (microsoft.public.dotnet.framework.adonet)
  • System.InvalidOperationException: Timout expired.
    ... Users of our .Net application running in a MS Windows 2000 Server (v5.0.2195 ... connection from the pool. ... current web request. ... The timeout period elasped prior to obtaining a connection from the pool. ...
    (microsoft.public.sqlserver.server)