Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Sleeping process...


Sleeping process...

Author
Message
Anand S-265483
Anand S-265483
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 19
Hi Brian,

I am facing a similar problem in my project - I don't see the issue in development, but when we move to production the number of sleeping connections grow rapidly and sql server starts refusing new connections (the software is pretty useless at this stage).

You mention that after closing, sql server holds the connection for a period of time (1 min or so). Is there any documentation that gives this information and also any idea whether this number is tunable/Changeable ?

thanks

Anand
Hans Lindgren
Hans Lindgren
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1204 Visits: 366
Anand S (5/8/2009)
Hi Brian,

I am facing a similar problem in my project - I don't see the issue in development, but when we move to production the number of sleeping connections grow rapidly and sql server starts refusing new connections (the software is pretty useless at this stage).

You mention that after closing, sql server holds the connection for a period of time (1 min or so). Is there any documentation that gives this information and also any idea whether this number is tunable/Changeable ?

thanks

Anand

Yes, there is documentation. You need to check the specific one for you development language/environment. The connection pooling is on your application side and not on your DB side... In most languages you can turn connection pooling off...

(Maybe can be worth checking into your connection strings, that they are the same over the whole application. Constantly changing configuration strings makes connection pooling almost useless).

Not using a DAL approach might also negate the use of connection pooling...



Anand S-265483
Anand S-265483
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 19
Hi,

I am using Asp.net webservice to invoke the connections. In this case does .Net provide connection pooling. I thought the db creates and manages the connection pool.

We are creating the connection string using default values, so connection pooling is true. We use one single connection string , so the connections are for the same string.

Not using a DAL approach might also negate the use of connection pooling...
>> We are using a data access layer approach by having the connection,sql in a separate class

The transactions are committed and disposed. Connections are closed on success (Also rollbacks, connection close on exceptions).

The connections continue in "sleeping" status even after the close. What would cause that? and how is the time configurable - i.e. the time that sqlserver maintains a connection in active status after it is closed by .net ?
naga.rohitkumar
naga.rohitkumar
SSChasing Mays
SSChasing Mays (632 reputation)SSChasing Mays (632 reputation)SSChasing Mays (632 reputation)SSChasing Mays (632 reputation)SSChasing Mays (632 reputation)SSChasing Mays (632 reputation)SSChasing Mays (632 reputation)SSChasing Mays (632 reputation)

Group: General Forum Members
Points: 632 Visits: 1378
It may happen even if the user who executed the query in sql server may not closed the query window from long time once if the query window closed then you can't see the spid in the activity monitor

Thanks
Naga.Rohitkumar
reddysiri517
reddysiri517
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 17
Hello All,

Good day, please confirm sleeping sessions or stale session in sql server will use CPU.
If we kill sleeping sessions,CPU will come down. Please elabrate on this topic, thanks a ton in advance.

Regards,
Sireesha.
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47357 Visits: 44392
reddysiri517 (5/28/2015)
Good day, please confirm sleeping sessions or stale session in sql server will use CPU.
If we kill sleeping sessions,CPU will come down.


No. Sleeping sessions are doing nothing. Since they're doing nothing, they can't be using CPU.


Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


reddysiri517
reddysiri517
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 17
Thank you.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search