locked
SUSPENDED status issue RRS feed

  • Question

  • Hi all, we have queries submited via IBM Cognos to MS SQL 2008 and we get a lot of SUSPENDED processes. The problem

    1. The SUSPENDED process does not close down by itself even when the request no longer exists in IBM Cognos and the only way to get rid of it is by killing it.

    Any ideas on how we can rectify this issue?

    Friday, January 16, 2015 10:01 AM

All replies

  • I agree with Ashwin here.

    Below is extract from This blog

    It means that the request currently is not active because it is waiting on a resource. The resource can be an I/O for reading a page, A WAITit can be communication on the network, or it is waiting for lock or a latch. It will become active once the task it is waiting for is completed. For example, if the query the has posted a I/O request to read data of a complete table tblStudents then this task will be suspended till the I/O is complete. Once I/O is completed (Data for table tblStudents is available in the memory), query will move into RUNNABLE queue.

    So if it is waiting, check the wait_type column to understand what it is waiting for and troubleshoot based on the wait_time. "SQL Server 2005 Waits and Queues" whitepaper published by SQL Cat team will be a good read to troubleshoot performance issue based on Waits. Download it from http://sqlcat.com/whitepapers/archive/2007/11/19/sql-server-2005-waits-and-queues.aspx

    You can also check my blog http://blogs.msdn.com/b/sqlsakthi/archive/2011/02/20/sql-query-slowness-troubleshooting-using-extended-events-wait-info-event.aspx to find out waits for a particular SPID using XEVENTS.

    Here is a query that returns information about the wait queue of tasks that are waiting on some resource:

    SELECT wt.session_id,

    ot.task_state, wt.wait_type,

    wt.wait_duration_ms,

    wt.blocking_session_id,

    wt.resource_description,

    es.[host_name], es.[program_name]

    FROM sys.dm_os_waiting_tasks wt

    INNER JOIN sys.dm_os_tasks ot

    ON ot.task_address = wt.waiting_task_address

    INNER JOIN sys.dm_exec_sessions es ON es.session_id = wt.session_id

    WHERE es.is_user_process = 1




    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Technet Wiki Article

    MVP

    Friday, January 16, 2015 10:45 AM
  • It has something to do with how IBM Cognos handles connections with SQL. When a request is closed in IBM Cognos does it close the connections ? Moreover, SUSPENDED means its waiting for a resource, once that resource is available then it will go to runnable mode. So probably the spids are waiting something.

    I guess, you need to contact Cognos support for this.


    Regards, Ashwin Menon My Blog - http:\\sqllearnings.com

    Friday, January 16, 2015 10:50 AM
  • I can see that via activity monitor it's not something new, is there a configuration anywhere where we can specify the SUSPENDED connection should die after certain amount of idle time?
    Friday, January 16, 2015 11:28 AM
  • I can see that via activity monitor it's not something new, is there a configuration anywhere where we can specify the SUSPENDED connection should die after certain amount of idle time?
    That would be a very bad idea as almost all processes will go (may be ephemerally) into suspended status at one time or another as they wait for a resource like I/O. As others have stated you should have the vendor review their application. However, in the interim, if you have to implement a termination process, you can have a script that be run by SQLAgent that will check specific process from COGNOS only and if they are SLEEPING for a long time (say couple of hours), kill them. But don't try to kill suspended processes, as they are not necessarily evil

    Satish Kartan www.sqlfood.com

    Friday, January 16, 2015 11:35 AM
  • Do you face any issues because of these suspended threads? If not why are you trying to terminate them?

    As I said earlier this has to be handled in COGNOS side and not from SQL Side. Handling this issue in SQL Side could cause other issues where you are killing spids which are in actual SUSPENDED state, like waiting for IO etc.


    Regards, Ashwin Menon My Blog - http:\\sqllearnings.com

    Friday, January 16, 2015 11:55 AM
  • I can see that via activity monitor it's not something new, is there a configuration anywhere where we can specify the SUSPENDED connection should die after certain amount of idle time?
    I would never do that, instead find out why it is lingering out there

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Technet Wiki Article

    MVP


    Friday, January 16, 2015 12:35 PM