Re: Connection pool count too high?

Thanks for your response, now I know where to start looking for problems.

I will be at VSlive Orlando and would love to hear you speak on this topic.
What session will cover this?

"William (Bill) Vaughn" wrote:

> I've written about this many times so search the archives for more details.
> Yes, the counts do look high. Each of these numbers can be evaluated in a
> number of ways. If the number of pools looks high (and it does) I would tend
> to think that the conditions that require a new pool are changing. It's not
> just the connection string that determines if a new pool is needed. It's
> also the transaction enlistment and the process ID. Each application domain
> (app domain) gets its own pool, but within that scope each transaction gets
> a new pool. These pools are not destroyed until they are empty for some time
> and the app domain is gone.
> If the number of concurrent users exceeds the number of pooled connections,
> then yes, I expect there is a leak or your system is unable to keep up with
> the load. As a new user arrives, if the system is not finished with the
> previous user a new connection has to be created to deal with it. The
> additional load (of the subsequent user) exacerbates the problem. This is
> not an issue of closing connections in code, it's not closing the connection
> in time--before it's needed again. Make sense?
>
> I'm giving an ADO.NET workshop in Orlando (Oct 13th) and in Sydney the week
> of the 28th of November. I'll discuss these issues there.
>
> --
> ____________________________________
> William (Bill) Vaughn
> Author, Mentor, Consultant
> Microsoft MVP
> www.betav.com/blog/billva
> www.betav.com
> www.sqlreportingservices.net
> Please reply only to the newsgroup so that others can benefit.
> This posting is provided "AS IS" with no warranties, and confers no rights.
> __________________________________
>
>
> "Dan" <Dan@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> news:6823E40C-7F4A-4E30-B5F9-E97B1C8E2D9D@xxxxxxxxxxxxxxxx
> >I am trying to determine if the # of connection pools our app is using is
> > 'normal'/acceptable, or if there is a problem.
> >
> > Our web application is all asp.net, using ADO.NET and SQL Server 2000 STD.
> > The app is run on a Windows 2003 Server Web Edition and IIS 6. The
> > application is broken up into 2 webs in IIS. One that contains only one
> > Application Virtual Directory, and the other that is broken up into 7
> > Application Virtual Directories.
> >
> > Every Sql Command uses the same connection string. All data access opens
> > and
> > closes its own connection immediately before and after the command is run.
> >
> > We normally have about 45 - 60 concurrent users of the app at any given
> > time. And every page runs several sql commands every time they load.
> >
> > Below are the performance counters from both the web and sql servers that
> > I
> > thought were important for this research.
> >
> > 142 connection pools and 429 pooled connections seems very high to me. I
> > know that it is hard to determine what should be 'normal' without knowing
> > more about the app, but at a very high level, does this seem normal or
> > would
> > this indicate there is a problem?
> >
> >
> > Connection string
> > -----------------
> > "User ID=<uname>;Password=<pword>;Initial Catalog=<dbname>;Data
> > Source=<ip>;"
> >
> >
> > Web Server Perf Counters
> > ------------------------
> > .NET CLR DATA _global_
> > SqlClient Current # connection pools: 142
> > SqlClient Current # pooled and nonpooled connections: 429
> > SqlClient Current # pooled connections: 429
> >
> > Web Service
> > Current Connections: 46
> > Maximum Connections: 158
> > Maximum Anonymous Users: 91
> > Get Request/ sec: <10 - 20 avg>
> >
> > SQL Server Perf Counters
> > ------------------------
> > SQLServer:GeneralStatistics
> > User Connections: 86
> >
> >
> >
>
>
>
.

Relevant Pages

  • Re: SQL Statement Class Helper request
    ... SQL Server will do this as well for regular queries issued ... > connection (there is also the notion of "preparing" a query for use multiple ... One advantage of using Java for the connection pool - this doesn't seem ... > I agree with you that there are ways you might want to filter the query. ...
    (microsoft.public.dotnet.languages.csharp)
  • 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)
  • Timeout - max pool size reached
    ... I got the following message on my Sql Server 2000: ... The timeout period elapsed prior to obtaining a connection ... in use and max pool size was reached. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Connection pool count too high?
    ... >> just the connection string that determines if a new pool is needed. ... >> and the app domain is gone. ... >>> Our web application is all asp.net, using ADO.NET and SQL Server 2000 ...
    (microsoft.public.dotnet.framework.adonet)