Home » SQL ServerRSS

Process with status SLEEPING.

Hi all,

We have a web application with multiple users connecting to SQL Server 2005 database.

Once the users start using the system, I could see in the Process Info from the activity monitor with a several process with status as sleeping  and command as Awaiting Command.

I have few questions regarding this for which simple answers would help me ( I searched and couldnt understand from the pages that explained the process information )

My questions are,

1. Does the processes with status sleeping and command as Awaiting Command are needed for the application even after the operations specific to them are complete?

2. Does these process affect the performance ?

3. Does killing these process would affect the application connectivity ?

4. Why do we get several process with status sleeping and command as Awaiting Command? Is this because of the front end programming errors ?

5. What should we do to avoid the increasing number of process with status sleeping and command as Awaiting Command?

Any help would be appreciated.

Thanks in advance,

DBLearner.

 

15 Answers Found

 

Answer 1

Hi DBLearner,

1. Does the processes with status sleeping and command as Awaiting Command are needed for the application even after the operations specific to them are complete?

-- No

2. Does these process affect the performance ?

-- It will if you have left thousand connections open. Connections need resources.

3. Does killing these process would affect the application connectivity ?

-- You shouldn't have to go around killing these processes. I don't think it would affect. More work for you.

4. Why do we get several process with status sleeping and command as Awaiting Command? Is this because of the front end programming errors ?

-- Web app doesnot close the connection after the work has been done.

5. What should we do to avoid the increasing number of process with status sleeping and command as Awaiting Command?

-- Your web application needs to be configured or modified so that whenever it needs to do any work on SQL Server it does the following:

1. Opens a connection.

2. Do the SQL work e.g. execute a stored procedure or select some data back.

3. Close the connection.

regards

Jag

 

Answer 2

Most web applications use a "connection pool" for SQL connections.  This is a normal operation.  Leave it alone.

Openning and closing connections is very time consuming.  You do NOT want to open and close several thousand connections per min. 

 

Answer 3

Thanks Jag & Tom. But what should I do to optimise my connections and applications ?

DBLearner

 

Answer 4

It depends on your web application.  You will need to look and see what mechanisim it is using to access the SQL server.

 

Answer 5

Agree with Tom, if it is using Connection pooling then the connections should be left alone.

But if there is no connection pooling present, then the web application should be closing there connection after the work has been completed.

regards

Jag

 

Answer 6

Hi,

Actually I am having a same problem where there are quite a number of process with sleeping status in the database. And I noticed that all these processes are called by the crystal report (using store procedures to get the data from db). FYI, I am using VS.Net 2003 and crystal report 9.0. Does anyone have idea on the solution for the this problem? Thanks.

 

Answer 7

As Jag and Tom say - if this is connection pooling then you are best to leave it alone. 

What you are seeing is one sleeping process for each active pool connection.  They are waiting, ready to respond quickly when needed (without logging in again).  The only time you have a problem is if the number of these sleeping connections does not hold steady but instead rises dramatically (implying that either it is not pooling somewhere or the pooling is failing).

The pool of connections means that the application can be responsive, and if you start killing them then there will be a reduction in that responsiveness (and servicing requests is the database's function).  Even worse if you manage to kill a connection just as it is activated to service a request you could cause all kinds of problems (hopefully just longer delays).

 

Answer 8

This is not a "problem", this is how it WORKS.  As said before, leave it alone unless you are seeing hundreds of sleeping processes overloading your SQL server.

Generally it opens new pipes when other pipes are busy.  So if you have 10 sleeping processes, it is because you NEEDED THEM sometime before.  It should timeout the pipe and close them after 60 mins or some time of inactivity.

Crystal does have some options to change the min/max connections, but I would leave them the default unless you are seriously having a problem.

 

Answer 9

I have the same problem but i don't have hundread of conn just about 40.

These 40 sleeping connections thought eat me about 90% from the CPU and reset users Session and they loose their products before ariving to the payment.

I did close all the recordsets and connections and still appear new sleeping connections.

Any suggestions?

i used for any recordset opened

rs.close

set rs = nothing

still make me these problems i don't know what to do anymore

Any help will be appreciated

thank you

 

Answer 10

This is not a "problem", this is how it WORKS.  As said before, leave it alone unless you are seeing hundreds of sleeping processes overloading your SQL server.

Generally it opens new pipes when other pipes are busy.  So if you have 10 sleeping processes, it is because you NEEDED THEM sometime before.  It should timeout the pipe and close them after 60 mins or some time of inactivity.

Crystal does have some options to change the min/max connections, but I would leave them the default unless you are seriously having a problem.

Hi  Tom,

Is there any documentation that mentions the time that SQL server keeps  a connection  as active after the .net Program  issues a connection.close  ?
For e.g. I see this problem  in my application where in  certain cases the  number of connections are steady  (in dev machines), and  in certain cases the connections grow very quickly and hit the  threshold (default of 100) when Sql server  refuses new  connections.

My question is whether  this is dependent on the interval between subsequent updates.. (The sqls do an update to a  row in  the  db using  pk and the  code  opens the connection, does  the update, commits the trans and closes the  connection). My doubt is whether sql server still holds the connection  as  active for  a fraction of time after the .net  program calls  the  connection.close().  When the  connections grow we see them in the "sleeping" status

Thanks
Anand
 

Answer 11

Just some more inputs.. you know people like me can never shutup :) and give un-necessary 2 cents and here are mine.

The only thing you should remember while working with web apps is te connection pooling thing and sometimes as in our situations.. if there are n number of connections from the we app, the users will see disconnects or hung processes from the web app side.. this is where you have to reset the connection pool..(i am a DBA so know only what the web guys do..)

There is no stress on the SQL server however.

Killing the connection will hurt any live connection/transaction.. becasue sometimes they come in and out fast.

only if you are hsing some performance issues/concerns then Check the  lastwaittype,waittime,waitresource  in sysprocesses to begin.
If the web apps run any heavy sp's in the backend and fetche's a lot of data using joins and stuff, then check your Query parallelism/Plans to start with.(sp_configure).
I hope evertything is a SP and you are no using any inline Queries in webpages, which can hurt your SQL performance MAJOR Time.. with lots of Locking/blocking .

 

Answer 12

SQL Server does not keep the connection open after a "close" command, maybe a second or two.   

When you close a connection in .NET, it does NOT close the physical connection to the SQL Server.  It RELEASES it to the connection pool for other .NET applications to reuse.  This is a function of .NET connection pooling. 

SQL Server is NOT refusing connections at 100, the .NET connection pool is defaults to 100 connections.

Please see this write up on .NET connection pooling.  http://msdn.microsoft.com/en-us/library/aa175863(SQL.80).aspx
 

Answer 13

Hi Tom

Thanks for your all updates... but I have one question on slipping connection of my application with SQL Server...

I have made one sample application in which i have connect with sql server with Dataset and fetch sample record from it then after i have forcefully close connection with SQL Server to make sure that  connection not remain open. I have make this iteration 800 times at every time i create sqlconnection, sqldataadapter object and fill dataset then close connection force fully, As you know that Dataset used close connection architecture so my connection should be closed after filling Dataset.

But still i can see one open connection in slipping status after end of all 800  iteration, so my question is

1) Why this one connection open even after i close all connection forcefully?
2) How can i set the time to close all slipping connection to SQL Server ?
3) Does SQL Server by default pool 100 connection even after i not allowed connection pooling in my application?
 

Answer 14

What if I actually have literally thousands of sleeping processes...

select

count(*)as cntSleeping
from sysprocesses wherestatus='sleeping'

cntSleeping
-----------
5856

(1 row(s) affected)

 

Answer 15

This thread doesn't belong here. A moderator should move it to either c# or Ado.Net midleware forum.
 
 
 

<< Previous      Next >>


Microsoft   |   Windows   |   Visual Studio   |   Follow us on Twitter