Hi all,

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

Once the users startusing the system, Icould 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 Commandare needed for the application even after the operations specificto 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 Commandare needed for the application even after the operations specificto 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

• Proposed as answer by Leonardo Pedroso Costa Friday, November 30, 2012 8:04 PM

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.

• Proposed as answer by Kalman Toth Thursday, November 19, 2009 4:51 PM

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. AndI noticed thatall these processes arecalled 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

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

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

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 .

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

• Edited by Tom PhillipsModerator Monday, May 11, 2009 9:06 PM

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?

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

select

count(*) as cntSleeping
from sysprocesses where status = 'sleeping'

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

(1 row(s) affected)

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

Please explain why inline Queries cause locking/blocking and why SP's don't.

We are experiencing sleeping connections that are locking tables, I'm the dba, not the developer so my guess was that they are not clearing the connection properly, though this comment of yours was interesting. Perhaps this is so old no one will answer....

Thanks