SQL Server Performance

'sleeping' SQL Server Processes

Discussion in 'T-SQL Performance Tuning for Developers' started by nshaan1, Jan 6, 2003.

  1. nshaan1 New Member


    I have a JSP-Servlet application using a type-4 Driver for SQL2000. The app. also extensively uses Stored Procedures.

    My problem is with 'sleeping' processes in SQL Server. When the app is running in a multi user environment and I do a sp_who2 on the database, I see a lot of SQL processes in 'Sleeping' status. Many such sleeping processes are from clients who have already disconnected from the application. Somehow, SQL is either not completing the process or freeing up this process. What could be the reason for so many 'sleeping' process.

    Also, many times, I get blocking processes. When I do a dbcc inputbuffer on these blocking process I get 'sp_cursorclose,1' or 'sp_execute' or 'null' in the results. What could be causing these blocking process ?

    Any feedback would be welcome and useful.

  2. bradmcgehee New Member

    I am not familiar with your JSP driver, so I don't know if my comments apply to it or not. One potential cause of this is the connection pooling that your driver may have implemented. When connection pooling is used, even if the user's connection is dropped, the connection will remain open with the idea that another connection may be able to reuse it. Generally, with connection pooling, after a specified amount of time, any unused connections should automatically disconnect.

    Blocking processes can be caused by many things, generally because a transaction is running too long. You will need to identify which connection is causing the blocking, and then look at this connection carefully to see what it is doing that is taking so long, trying to find ways to speed it up.

    Brad M. McGehee
  3. satya Moderator

    And also after running SP_WHO to know the command executing runn DBCC INPUTBUFFER(spid). YOu need to use disconnect and reconnect method from the application which will assure the connection is closed once the transaction is complete at server end.

    Satya SKJ
  4. Chappy New Member

    Not sure if this is automatically cleared up but it might be worth seeing if all cursors are and deallocated (and closed obviously)

Share This Page