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