2

Using sp_WhoIsActive, I see that I have a lot of processes in a "sleeping" status, several for hours. I am not sure of the cause and am still troubleshooting (timeout? bad code with uncommitted transaction?).

Using the query below, I noticed that all of these open transactions are SELECT queries in a "sleeping" status. Why would a SELECT open a transaction? Could these be in the middle of a stored procedure somewhere, and the [TEXT] field is only showing the last statement in the procedure that was executed? There is no wait_info on any of the open transactions.

 SELECT [TEXT] as SQLcode 
 FROM SYS.SYSPROCESSES SP
 CROSS APPLY SYS.DM_EXEC_SQL_TEXT(SP.[SQL_HANDLE])AS DEST WHERE OPEN_TRAN=1
11
  • 1
    Stop using sysprocesses its old and deprecated. Use sys.dm_exec_requests instead. In some cases sleeping task is OK it means connection is still there and thread has done its task if you see lot of sleeping task you need to get in touch with application developer to see if sessions are closed after they have done the task
    – Shanky
    Commented Jun 30, 2015 at 16:20
  • @Shanky Is it possible that the older tables have data that is not accurate? I do not see the sleeping transactions in sys.dm_exec_requests
    – SomeGuy
    Commented Jun 30, 2015 at 17:22
  • Do the processes showing this "problem" have spid < 50? Is there a symptom other than the fact that these system processes are showing up in the old deprecated view? What exactly are the queries selecting? Are they from a vendor app, your app, your users, ...? Commented Jun 30, 2015 at 17:27
  • @AaronBertrand No, the processes are spid > 50, so they are user processes. They are coming from a vendor application. The handful that I spot-checked are very simple queries selecting just a few columns from small tables. Many of the queries are even selecting from a table with 1 row. Some of these have been sleeping for 12 hours now.
    – SomeGuy
    Commented Jun 30, 2015 at 17:34
  • 1
    Why should you not be seeing sleeping processes? I see them all the time. Open a query window in SSMS, and in another window, check sys.dm_exec_sessions and observe what that first query window is doing. It's sitting there, open, sleeping. The thinking is that the resources used to hold an idle connection are less expensive than the ones used to forcefully terminate a connection and make them re-establish one. Again, if you're not seeing an actual symptom here, other than some rows in that procedure output, don't invent a problem. :-) Commented Jun 30, 2015 at 18:21

2 Answers 2

2

Sleeping isn't a problem necessarily. Depending on the app, thread pooling may keep the connection open even though nothing is running.

If there are no transactions open, then is it really a problem?

Kendra Little wrote about this recently on her blog:

Sleepers are usually OK

A sleeping session without an open transaction…

Isn’t holding locks on a table

Uses very little resources

May be re-used by the application (by something like connection pooling)

As per Kenda's suggestion, have a look at sp_whoisactive instead of using sp_who2.

1
  • Good article and video by Kendra. In my case, there ARE open transactions.
    – SomeGuy
    Commented Feb 27, 2017 at 20:14
1

what about you start using this query below, and then tailor it for your own purposes:

select *
from sys.dm_tran_database_transactions t

join sys.dm_tran_session_transactions st on t.transaction_id = st.transaction_id 

join sys.dm_exec_sessions s on s.session_id = st.session_id 

join sys.dm_exec_connections c on c.session_id = s.session_id 

cross apply sys.dm_exec_sql_text(most_recent_sql_handle) 
where 1=1

and text <> 'begin tran' 

and text not like '%sp_MS%' 

and datediff(minute, last_request_end_time, getdate()) > 60

and database_transaction_begin_time is not null

I use this one below, it helps in most cases:

    SELECT 
     es.session_id AS session_id

    ,CASE WHEN eS.LOGIN_NAME = eS.ORIGINAL_LOGIN_NAME 
     THEN eS.LOGIN_NAME 
     ELSE eS.LOGIN_NAME + ' (' + eS.ORIGINAL_LOGIN_NAME + ')' 
     END AS LOGIN_NAME

    ,es.host_name AS hostname
    ,es.status
    ,es.last_request_start_time
    ,es.last_request_end_time
    ,es.CPU_TIME AS CPU_TIME_MS
    ,es.MEMORY_USAGE AS MEMORY_USAGE_PAGES
    ,es.ROW_COUNT
    ,NULL AS blocked_by
    ,NULL AS waittype
    ,NULL AS waittime
    ,NULL AS lastwaittype
    ,NULL AS waitresource
    ,dbid=trans.database_name
    ,NULL as  cmd
    ,t.text AS [QUERY]
    ,NULL AS PARENT_QUERY

   ,TRANSACTION_ISOLATION_LEVEL = NULL

    ,NULL as cpu

    ,NULL AS physical_io

    --,es.open_transaction_count as [open_tran]
    ,es.program_name

    --,es.login_time
    --,ota.task_state
    --,ota.pending_io_count
    --,ota.pending_io_byte_count


    FROM sys.dm_exec_sessions AS es
    INNER JOIN sys.dm_exec_connections AS c
        ON es.session_id = c.session_id

    --LEFT OUTER JOIN sys.dm_os_tasks ota ON es.session_id = ota.session_id

    CROSS APPLY (

        SELECT MAX(DB_NAME(dt.database_id)) AS database_name
        FROM sys.dm_tran_session_transactions AS st
        INNER JOIN sys.dm_tran_database_transactions AS dt
            ON st.transaction_id = dt.transaction_id
        WHERE is_user_transaction = 1
        GROUP BY st.session_id
        HAVING es.session_id = st.session_id 

    ) AS trans

    CROSS APPLY sys.dm_exec_sql_text(c.most_recent_sql_handle) AS t
    WHERE es.session_id NOT IN (

            SELECT session_id
            FROM sys.dm_exec_requests

        )
        AND es.session_id IN (

            SELECT request_session_id
            FROM sys.dm_tran_locks
            WHERE request_status = 'GRANT'
        )
        AND STATUS = 'sleeping'
        AND is_user_process = 1
        AND es.session_id <> @@spid

Please avoid using SYS.SYSPROCESSES

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.