| Groups | Blog | Home |
dotnet ado.net : Connection pool count too high?
'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
Connections stay in the same pool only if the ConnectionString are exactly
the same: the same password, the same user name... [quoted text, click to view] "Dan" <Dan@discussions.microsoft.com> wrote in message news:6823E40C-7F4A-4E30-B5F9-E97B1C8E2D9D@microsoft.com... >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 > > >
Sorry for the bluntness, but that doesn't even really relate to my question.
[quoted text, click to view] "Norman Yuan" wrote:
> Connections stay in the same pool only if the ConnectionString are exactly > the same: the same password, the same user name... > > "Dan" <Dan@discussions.microsoft.com> wrote in message > news:6823E40C-7F4A-4E30-B5F9-E97B1C8E2D9D@microsoft.com... > >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 > > > > > > > >
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. __________________________________ [quoted text, click to view] "Dan" <Dan@discussions.microsoft.com> wrote in message news:6823E40C-7F4A-4E30-B5F9-E97B1C8E2D9D@microsoft.com... >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 > > >
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? [quoted text, click to view] "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@discussions.microsoft.com> wrote in message > news:6823E40C-7F4A-4E30-B5F9-E97B1C8E2D9D@microsoft.com... > >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 > > > > > > > >
I'm doing a "Hitchhikers' Guide to Connecting" (extracted from my book) that
covers it. So does the post-con ADO.NET best practices workshop. Be sure to tell me you're there (and sit up front so you can see the demos). -- ____________________________________ William (Bill) Vaughn Author, Mentor, Consultant Microsoft MVP INETA Speaker 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. __________________________________ [quoted text, click to view] "Dan" <Dan@discussions.microsoft.com> wrote in message news:C1F2996B-8269-41DF-94B3-AEEE649E1A58@microsoft.com... > 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@discussions.microsoft.com> wrote in message >> news:6823E40C-7F4A-4E30-B5F9-E97B1C8E2D9D@microsoft.com... >> >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 >> > >> > >> > >> >> >>
Don't see what you're looking for? Try a search.
|
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
December 2007
January 2008
February 2008
March 2008
April 2008
| home · blog · groups | Questions? Comments? Contact the dn |