too many connections with .NET  
Author Message
JacekKolonko





PostPosted: Mon May 01 14:09:32 CDT 2006 Top

SQL Server Developer >> too many connections with .NET

Using SS2000 SP4 and .NET 1.1. We have a .NET application that has a small
number of users - maybe 15 or so. It seems like as soon as the first person
logs in we have a little over 100 connections (about 108 - as viewed in SQL
Server EM) and the connections never go away. If we reboot, we get about 100
connections right away. All of the connections have a login time within a
second of each other. On Friday we rebooted and about 90 connections had a
login time of 09:28:00 (about the time the server rebooted) and another 15 or
so had a login time of 09:28:01.

We know that the programmers didn't close all of their connections. I think
that could explain why the connections don't disappear but I don't think it
explains why so many connections open all at once. Even if all 15 users
logged in as soon as the system rebooted, could they all have login times so
close to one another?

Any idea what would cause this and how to fix it?

Thanks,
--
Dan D.

SQL Server170  
 
 
Erland





PostPosted: Mon May 01 14:09:32 CDT 2006 Top

SQL Server Developer >> too many connections with .NET
> Using SS2000 SP4 and .NET 1.1. We have a .NET application that has a
> small number of users - maybe 15 or so. It seems like as soon as the
> first person logs in we have a little over 100 connections (about 108 -
> as viewed in SQL Server EM) and the connections never go away. If we
> reboot, we get about 100 connections right away. All of the connections
> have a login time within a second of each other. On Friday we rebooted
> and about 90 connections had a login time of 09:28:00 (about the time
> the server rebooted) and another 15 or so had a login time of 09:28:01.
>
>
> We know that the programmers didn't close all of their connections. I
> think that could explain why the connections don't disappear but I don't
> think it explains why so many connections open all at once. Even if all
> 15 users logged in as soon as the system rebooted, could they all have
> login times so close to one another?
>
> Any idea what would cause this and how to fix it?

It's sort of normal that connections don't disappear at once, because
of connection pooling. That is, when the code says disconnect, ADO .Net
lingers to the connection for another 60 seconds, before it closes the
connection to SQL Server. If there is a request for an connection in
this time frame, the connection is reused.

This pool is per client. Thus, if the application is a two-tier application,
each user has his connection pool. On the other hand if the clients
connects to a middle layer on an intermediate server, the pool will be
common to all users.

All this said, the behaviour you describe sounds abnormal to me, and
I think it's a buggy application, or a poor design. Sounds to me like
someone is rolling his own connection pool.

microsoft.public.dotnet.framework.adonet is probably a better
place to get help with connection pooling in ADO .Net.

--


Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
 
 
DanD





PostPosted: Mon May 01 14:30:02 CDT 2006 Top

SQL Server Developer >> too many connections with .NET These are connections that don't EVER go away - until another reboot that is.
There is something going on that locks up the app and restarting IIS or
rebooting the IIS server fixes it. But, the 100 or so connections come back
within a few minutes and stay until the next reboot.

Thanks. I'll check the adonet site.
--
Dan D.





> > Using SS2000 SP4 and .NET 1.1. We have a .NET application that has a
> > small number of users - maybe 15 or so. It seems like as soon as the
> > first person logs in we have a little over 100 connections (about 108 -
> > as viewed in SQL Server EM) and the connections never go away. If we
> > reboot, we get about 100 connections right away. All of the connections
> > have a login time within a second of each other. On Friday we rebooted
> > and about 90 connections had a login time of 09:28:00 (about the time
> > the server rebooted) and another 15 or so had a login time of 09:28:01.
> >
> >
> > We know that the programmers didn't close all of their connections. I
> > think that could explain why the connections don't disappear but I don't
> > think it explains why so many connections open all at once. Even if all
> > 15 users logged in as soon as the system rebooted, could they all have
> > login times so close to one another?
> >
> > Any idea what would cause this and how to fix it?
>
> It's sort of normal that connections don't disappear at once, because
> of connection pooling. That is, when the code says disconnect, ADO .Net
> lingers to the connection for another 60 seconds, before it closes the
> connection to SQL Server. If there is a request for an connection in
> this time frame, the connection is reused.
>
> This pool is per client. Thus, if the application is a two-tier application,
> each user has his connection pool. On the other hand if the clients
> connects to a middle layer on an intermediate server, the pool will be
> common to all users.
>
> All this said, the behaviour you describe sounds abnormal to me, and
> I think it's a buggy application, or a poor design. Sounds to me like
> someone is rolling his own connection pool.
>
> microsoft.public.dotnet.framework.adonet is probably a better
> place to get help with connection pooling in ADO .Net.
>
> --

>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>
 
 
Jim





PostPosted: Mon May 01 15:10:41 CDT 2006 Top

SQL Server Developer >> too many connections with .NET If the application is making 100 database calls when a user hits the
application, then each of them may be opening their own database connection.
All 100 of these connections could remain active in the connection pool and
get reused on each subsequent call.

Does this number increase by 100 with every user, or stay at 100 no matter
how many users?

Have you tried limiting the number of connections used by connection pooling
(to 50 maybe)?

You say the developers are not closing their connections, but I have to
wonder what this means. I would expect that they create a connection in
.Net, then close it, and connection pooling keeps it open. The developer
closes the connection in .Net, thus freeing up resources and .Net saves that
connection so it can reuse it later on. Check with your developers and see
what they are doing.




> These are connections that don't EVER go away - until another reboot that
is.
> There is something going on that locks up the app and restarting IIS or
> rebooting the IIS server fixes it. But, the 100 or so connections come
back
> within a few minutes and stay until the next reboot.
>
> Thanks. I'll check the adonet site.
> --
> Dan D.
>
>

>

> > > Using SS2000 SP4 and .NET 1.1. We have a .NET application that has a
> > > small number of users - maybe 15 or so. It seems like as soon as the
> > > first person logs in we have a little over 100 connections (about
108 -
> > > as viewed in SQL Server EM) and the connections never go away. If we
> > > reboot, we get about 100 connections right away. All of the
connections
> > > have a login time within a second of each other. On Friday we rebooted
> > > and about 90 connections had a login time of 09:28:00 (about the time
> > > the server rebooted) and another 15 or so had a login time of
09:28:01.
> > >
> > >
> > > We know that the programmers didn't close all of their connections. I
> > > think that could explain why the connections don't disappear but I
don't
> > > think it explains why so many connections open all at once. Even if
all
> > > 15 users logged in as soon as the system rebooted, could they all have
> > > login times so close to one another?
> > >
> > > Any idea what would cause this and how to fix it?
> >
> > It's sort of normal that connections don't disappear at once, because
> > of connection pooling. That is, when the code says disconnect, ADO .Net
> > lingers to the connection for another 60 seconds, before it closes the
> > connection to SQL Server. If there is a request for an connection in
> > this time frame, the connection is reused.
> >
> > This pool is per client. Thus, if the application is a two-tier
application,
> > each user has his connection pool. On the other hand if the clients
> > connects to a middle layer on an intermediate server, the pool will be
> > common to all users.
> >
> > All this said, the behaviour you describe sounds abnormal to me, and
> > I think it's a buggy application, or a poor design. Sounds to me like
> > someone is rolling his own connection pool.
> >
> > microsoft.public.dotnet.framework.adonet is probably a better
> > place to get help with connection pooling in ADO .Net.
> >
> > --

> >
> > Books Online for SQL Server 2005 at
> >
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> > Books Online for SQL Server 2000 at
> > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
> >


 
 
Erland





PostPosted: Mon May 01 17:22:05 CDT 2006 Top

SQL Server Developer >> too many connections with .NET
> These are connections that don't EVER go away - until another reboot
> that is. There is something going on that locks up the app and
> restarting IIS or rebooting the IIS server fixes it. But, the 100 or so
> connections come back within a few minutes and stay until the next
> reboot.

Well, if the 100 connections are reused frequently enough, I would
not expect them to go away. But, then again, if you make sure that
IIS and the app is idle, they should go away, if the application is
properly implemented.

But the mere fact that it appears to grab 100 connection almost directly
makes me think that "properly implemented" is not applicable here. Looks
like someone grabs 100 connections, and runs his own connection pooling
rather than relying on ADO .Net. Certainly not a good idea.

--


Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
 
 
DanD





PostPosted: Tue May 02 09:11:03 CDT 2006 Top

SQL Server Developer >> too many connections with .NET It proved to be good advice to post on the adonet group. Someone there told
me to check the "min pool count" setting in the connection string and it was
set to 100. So that's where they are all coming from. We'll do some testing
and adjust this count.

Thanks Erland,
--
Dan D.





> > These are connections that don't EVER go away - until another reboot
> > that is. There is something going on that locks up the app and
> > restarting IIS or rebooting the IIS server fixes it. But, the 100 or so
> > connections come back within a few minutes and stay until the next
> > reboot.
>
> Well, if the 100 connections are reused frequently enough, I would
> not expect them to go away. But, then again, if you make sure that
> IIS and the app is idle, they should go away, if the application is
> properly implemented.
>
> But the mere fact that it appears to grab 100 connection almost directly
> makes me think that "properly implemented" is not applicable here. Looks
> like someone grabs 100 connections, and runs his own connection pooling
> rather than relying on ADO .Net. Certainly not a good idea.
>
> --

>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>
 
 
DanD





PostPosted: Tue May 02 09:40:03 CDT 2006 Top

SQL Server Developer >> too many connections with .NET Someone on the adonet group told me to check the "min pool count" setting in
the connection string and it was set to 100. So that's where they are all
coming from. We'll do some testing and adjust this count.

Thanks,
--
Dan D.




> If the application is making 100 database calls when a user hits the
> application, then each of them may be opening their own database connection.
> All 100 of these connections could remain active in the connection pool and
> get reused on each subsequent call.
>
> Does this number increase by 100 with every user, or stay at 100 no matter
> how many users?
>
> Have you tried limiting the number of connections used by connection pooling
> (to 50 maybe)?
>
> You say the developers are not closing their connections, but I have to
> wonder what this means. I would expect that they create a connection in
> ..Net, then close it, and connection pooling keeps it open. The developer
> closes the connection in .Net, thus freeing up resources and .Net saves that
> connection so it can reuse it later on. Check with your developers and see
> what they are doing.
>
>


> > These are connections that don't EVER go away - until another reboot that
> is.
> > There is something going on that locks up the app and restarting IIS or
> > rebooting the IIS server fixes it. But, the 100 or so connections come
> back
> > within a few minutes and stay until the next reboot.
> >
> > Thanks. I'll check the adonet site.
> > --
> > Dan D.
> >
> >

> >

> > > > Using SS2000 SP4 and .NET 1.1. We have a .NET application that has a
> > > > small number of users - maybe 15 or so. It seems like as soon as the
> > > > first person logs in we have a little over 100 connections (about
> 108 -
> > > > as viewed in SQL Server EM) and the connections never go away. If we
> > > > reboot, we get about 100 connections right away. All of the
> connections
> > > > have a login time within a second of each other. On Friday we rebooted
> > > > and about 90 connections had a login time of 09:28:00 (about the time
> > > > the server rebooted) and another 15 or so had a login time of
> 09:28:01.
> > > >
> > > >
> > > > We know that the programmers didn't close all of their connections. I
> > > > think that could explain why the connections don't disappear but I
> don't
> > > > think it explains why so many connections open all at once. Even if
> all
> > > > 15 users logged in as soon as the system rebooted, could they all have
> > > > login times so close to one another?
> > > >
> > > > Any idea what would cause this and how to fix it?
> > >
> > > It's sort of normal that connections don't disappear at once, because
> > > of connection pooling. That is, when the code says disconnect, ADO .Net
> > > lingers to the connection for another 60 seconds, before it closes the
> > > connection to SQL Server. If there is a request for an connection in
> > > this time frame, the connection is reused.
> > >
> > > This pool is per client. Thus, if the application is a two-tier
> application,
> > > each user has his connection pool. On the other hand if the clients
> > > connects to a middle layer on an intermediate server, the pool will be
> > > common to all users.
> > >
> > > All this said, the behaviour you describe sounds abnormal to me, and
> > > I think it's a buggy application, or a poor design. Sounds to me like
> > > someone is rolling his own connection pool.
> > >
> > > microsoft.public.dotnet.framework.adonet is probably a better
> > > place to get help with connection pooling in ADO .Net.
> > >
> > > --

> > >
> > > Books Online for SQL Server 2005 at
> > >
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> > > Books Online for SQL Server 2000 at
> > > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
> > >
>
>
>
 
 
Erland





PostPosted: Tue May 02 11:04:49 CDT 2006 Top

SQL Server Developer >> too many connections with .NET
> It proved to be good advice to post on the adonet group. Someone there
> told me to check the "min pool count" setting in the connection string
> and it was set to 100. So that's where they are all coming from. We'll
> do some testing and adjust this count.

Oops! Thanks for taking the time for reporting back, Dan!

--


Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx