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.

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

|||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.

|||

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

DBLearner

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

|||

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

|||

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.

|||

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).

|||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.

|||

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

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.

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

|||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.

|||

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

DBLearner

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

|||

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

|||

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.

|||

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).

|||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.

|||

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

Process Status "sleeping"

I have processes running on the server that show a status of "sleeping" from sp_who and sysprocesses, however they are still running. I run sp_who over and over again and only occasionally the process status shows running, and then switches back to "sleeping" or "defwakeup". I know what all the statuses mean, I just don't know why a running process would ever show as "sleeping". The processes take a very very long time to run as well.

Thanks in advance,
SteveThe process is probably waiting on a disk read, while it is sleeping. What is the waittype from sysprocesses?

Process runnable but wait indefinitely

There's a job that is scheduled to run indefinitely. The process's status is
"runnable" but it does nothing. It has the lastwaittype = CMEMTHREAD, kpid
= 3296, and it's not blocking any other processes or being bloked.
The waitresource is blank.
Please help!
DN
On Jul 26, 3:50 am, "DN" <tie...@.hotmail.com> wrote:
> There's a job that is scheduled to run indefinitely. The process's status is
> "runnable" but it does nothing. It has the lastwaittype = CMEMTHREAD, kpid
> = 3296, and it's not blocking any other processes or being bloked.
> The waitresource is blank.
> Please help!
> DN
Hi, what is the status on profiler? Do you see the counter increase
under the reads column?
|||Do you see the physical IO and CPU columns changing in sysprocesses
for that SPID, if so, then you might have to wait for it to complete.
Keep running sysprocesses to check if you see any changes in
lastwaittype.

Process runnable but wait indefinitely

There's a job that is scheduled to run indefinitely. The process's status is
"runnable" but it does nothing. It has the lastwaittype = CMEMTHREAD, kpid
= 3296, and it's not blocking any other processes or being bloked.
The waitresource is blank.
Please help!
DNOn Jul 26, 3:50 am, "DN" <tie...@.hotmail.com> wrote:
> There's a job that is scheduled to run indefinitely. The process's status is
> "runnable" but it does nothing. It has the lastwaittype = CMEMTHREAD, kpid
> = 3296, and it's not blocking any other processes or being bloked.
> The waitresource is blank.
> Please help!
> DN
Hi, what is the status on profiler? Do you see the counter increase
under the reads column?|||Do you see the physical IO and CPU columns changing in sysprocesses
for that SPID, if so, then you might have to wait for it to complete.
Keep running sysprocesses to check if you see any changes in
lastwaittype.

Process runnable but wait indefinitely

There's a job that is scheduled to run indefinitely. The process's status is
"runnable" but it does nothing. It has the lastwaittype = CMEMTHREAD, kpid
= 3296, and it's not blocking any other processes or being bloked.
The waitresource is blank.
Please help!
DNOn Jul 26, 3:50 am, "DN" <tie...@.hotmail.com> wrote:
> There's a job that is scheduled to run indefinitely. The process's status
is
> "runnable" but it does nothing. It has the lastwaittype = CMEMTHREAD, kpi
d
> = 3296, and it's not blocking any other processes or being bloked.
> The waitresource is blank.
> Please help!
> DN
Hi, what is the status on profiler? Do you see the counter increase
under the reads column?|||Do you see the physical IO and CPU columns changing in sysprocesses
for that SPID, if so, then you might have to wait for it to complete.
Keep running sysprocesses to check if you see any changes in
lastwaittype.

process question

What does the sleeping status of a process mean? If I kill that process,
will I lose anything?Hi,
You are not supposed to kill a process in sleeping state, because most of
the time if the process wait for a user Input or waiting for a lock will be
in sleeping mode.
It can go for Running status any time.
Thanks
Hari
MCDBA
"supernova" <abc@.yahoo.com> wrote in message
news:O#K9cYiuDHA.2368@.TK2MSFTNGP09.phx.gbl...
> What does the sleeping status of a process mean? If I kill that process,
> will I lose anything?
>|||Got it. Thanks. Hari
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:%23VvMDsiuDHA.2440@.TK2MSFTNGP12.phx.gbl...
> Hi,
> You are not supposed to kill a process in sleeping state, because most of
> the time if the process wait for a user Input or waiting for a lock will
be
> in sleeping mode.
> It can go for Running status any time.
> Thanks
> Hari
> MCDBA
>
> "supernova" <abc@.yahoo.com> wrote in message
> news:O#K9cYiuDHA.2368@.TK2MSFTNGP09.phx.gbl...
> > What does the sleeping status of a process mean? If I kill that process,
> > will I lose anything?
> >
> >
>

Process Hangs

the OK status is:

I build 12 fact table point to 12 partitions, and a father-son dimention. the dimention get to the fact table by the key column(son). I processFull,processUpdate the dimention, it run well. I processFull the cube or the partition, it run well too.

The Hangs status is:

I use the Process Dimention component in the SSIS. I chose the dimention, and use an ole db Source component point to the dimention table,and in it I use a query to chose the new row:"select from AgentDim where newdata=1". In the IDE, run the package, it's OK. Then I save the package to file system, and run it by Sql Server Job Agent.

Then it hangs, the job to run the package never stop. I stop the AS service and restart it. And I get into the Management Studio, and try to Process, then found it hangs.

I tried ProcessFull, ProcessAdd, ProcessUpdate, I tried deal with cube,dimention and any object, all hangs. It seem to AS is already dead.

God save me.

thanks.

Further Info: the query to chose the new row will return 0 rows.

Found Something, I think I found a bug.

The Hangs status happened when you do this:

The query to chose the new row returns 0 rows, that will make SSIS try to put a NULL set into AS,but AS don't know how to deal with such a NULL set, so it just keeps stop there.

This is really make me angry. We will get new rows everytime if we processAdd? Ofcause not.

Procedure to search a table

Hi All,

I have the following table:

Code Block

Request
requestid
customername
age
sex
address
status

[status =
0 deleteme
1 addme
2 updatme

3 processed]

I need to write a stored proc that will do the following :

Input : requestid

Logic : check the customers who already have a status of 'deleteme' and have now filed a status of 'addme'. For this, inside the stored proc, i will retreive the customername and address using the requestid and then check in the table for all the customers who have the samename, address and status=0. How can i detect this.

Output : 0 for no entry found and requestid if a request of 'deleteme' for the same person was found.

For eg:

Code Block

requestid,customername,age,sex,address,status
22352,Jack,23,M,'Texas',1

I need to check if a person with the name of Jack and address Texas is there in the db with the status of 0.

Plz note: My Request table can contain more than 100000 records at a time. Please sugest accordingly

Plz help. Thanks

Here it is,

Code Block

Create Table #sample (

[requestid] int ,

[customername] Varchar(100) ,

[age] Varchar(100) ,

[sex] Varchar(100) ,

[address] Varchar(100) ,

[status] int

);

Insert Into #sample Values('20352','Jack','23','M','Texas','0');

Insert Into #sample Values('22352','Jack','23','M','Texas','1');

Insert Into #sample Values('22353','Jack','23','M','Austin','0');

Insert Into #sample Values('22362','Buck','23','M','Austin','0');

Insert Into #sample Values('22392','Buck','23','M','Austin','1');

select

*

from

#sample [add]

Where

Exists

(

select

[requestid]

,[customername]

,[address]

from

#sample [del]

where

[del].[status] = 0

and [del].[customername] = [add].[customername]

and [del].[address] = [add].[address]

and [del].[requestid] < [add].[requestid]

and [add].[status] = 1

)

|||

Hi,

Thanks for query. I ran the query and it returns all people who have status as 1.

I want all people who have status as 0 and not 1. How can i make this in a stored proc so that i accept only request id and then search.

thanks.

|||

Expected one.. Use the following query..

Code Block

select

*

from

#sample [add]

Where

Exists

(

select

[requestid]

,[customername]

,[address]

from

#sample [del]

where

[del].[status] = 1

and [del].[customername] = [add].[customername]

and [del].[address] = [add].[address]

and [del].[requestid] > [add].[requestid]

and [add].[status] = 0

)

|||

thanks so strange..i tried that previously on the query you gave first..it gave no records..no when i try yours, it gave the needed results..

nyways thanks.