Sunday, March 25, 2012

32000 User Connections (According to Performance Monitor)

Hello,
I am dealing with a quite big application written in ASP communicating
directly with the database. The problem is that there is performance
problems with the DB and after investigating things a bit more I have
found that the number of User Connection are over 32000 according to
the Performance Monitor.
Yes, it would be better too have a 3-tier architecture but I am stuck
with what I have and have to do the best of the situation. I have gone
through every page to make sure that the ADO connection is closed and
that the connection object is set to nothing. I have also tried to
change the MAX Pool Size from everything from 50 upp to 500 and
Connection Lifetime I have set to 10. My conclusion is that I am
dealing with a leak somewhere (but I can not find it). My question is,
is there a way in SQL Server to clean up un-used user connection - let
them time out by them self.
Any suggestions? Anybody encountered anything similar? All ideas and
comments are welcomed.You could create a job that took the data from sp_who2 and compared the time
now to the LastBatch column. If they exceed your threshold disconnect them
with the KILL command.
Ensure your application can handle this and exit gracefully.
--
HTH. Ryan
<mathias_pettersson@.hotmail.com> wrote in message
news:1137510134.733734.249390@.g49g2000cwa.googlegroups.com...
> Hello,
> I am dealing with a quite big application written in ASP communicating
> directly with the database. The problem is that there is performance
> problems with the DB and after investigating things a bit more I have
> found that the number of User Connection are over 32000 according to
> the Performance Monitor.
> Yes, it would be better too have a 3-tier architecture but I am stuck
> with what I have and have to do the best of the situation. I have gone
> through every page to make sure that the ADO connection is closed and
> that the connection object is set to nothing. I have also tried to
> change the MAX Pool Size from everything from 50 upp to 500 and
> Connection Lifetime I have set to 10. My conclusion is that I am
> dealing with a leak somewhere (but I can not find it). My question is,
> is there a way in SQL Server to clean up un-used user connection - let
> them time out by them self.
> Any suggestions? Anybody encountered anything similar? All ideas and
> comments are welcomed.
>|||Just out of interest how much memory does your server have..?
Each connection takes around 24KB, you've got 24Kb * 320000 which is about
750Mb gone from SQL Server.
--
HTH. Ryan
"Ryan" <Ryan_Waight@.nospam.hotmail.com> wrote in message
news:uoYfth3GGHA.1032@.TK2MSFTNGP11.phx.gbl...
> You could create a job that took the data from sp_who2 and compared the
> time
> now to the LastBatch column. If they exceed your threshold disconnect them
> with the KILL command.
> Ensure your application can handle this and exit gracefully.
> --
> HTH. Ryan
> <mathias_pettersson@.hotmail.com> wrote in message
> news:1137510134.733734.249390@.g49g2000cwa.googlegroups.com...
>> Hello,
>> I am dealing with a quite big application written in ASP communicating
>> directly with the database. The problem is that there is performance
>> problems with the DB and after investigating things a bit more I have
>> found that the number of User Connection are over 32000 according to
>> the Performance Monitor.
>> Yes, it would be better too have a 3-tier architecture but I am stuck
>> with what I have and have to do the best of the situation. I have gone
>> through every page to make sure that the ADO connection is closed and
>> that the connection object is set to nothing. I have also tried to
>> change the MAX Pool Size from everything from 50 upp to 500 and
>> Connection Lifetime I have set to 10. My conclusion is that I am
>> dealing with a leak somewhere (but I can not find it). My question is,
>> is there a way in SQL Server to clean up un-used user connection - let
>> them time out by them self.
>> Any suggestions? Anybody encountered anything similar? All ideas and
>> comments are welcomed.
>|||The server has 1GB of RAM and dual 2.4Ghz processorssql

No comments:

Post a Comment