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
