Sql-server – Permanent count of waiting tasks (2) in Activity monitor

activity-monitorreplicationsql server

Now I'm observing in Activity Monitor permanent count of waiting tasks:
enter image description here

and at "Resource waits" tab I see next:
enter image description here

so, my question is: How to find and resolve this wait tasks?

I suspect that it related with transactional replication, which running on the server. This wait tasks I see only on Distributor. This issues appeared after restarting SQL server service.

sp_whoisactive – doesn't show any locks.

Thanks a lot for any information.

Best Answer

Apart from Resources waits tab i believe you should expand the tab above which says processes.The Processes pane shows the information about the currently running processes on the SQL databases, who runs them, and from which application.

Also, you can simultaneously use below script to find the info on whats running and causing waits thereby:

SELECT 
      [spid] = session_Id
    , ecid
    , [blockedBy] = blocking_session_id 
    , [database] = DB_NAME(sp.dbid)
    , [user] = nt_username
    , [status] = er.status
    , [wait] = wait_type
    , [current stmt] = 
        SUBSTRING (
            qt.text, 
            er.statement_start_offset/2,
            (CASE 
                WHEN er.statement_end_offset = -1 THEN DATALENGTH(qt.text)  
                ELSE er.statement_end_offset 
            END - er.statement_start_offset)/2)
    ,[current batch] = qt.text
    , reads
    , logical_reads
    , cpu
    , [time elapsed (ms)] = DATEDIFF(mi, start_time,getdate())
    , program = program_name
    , hostname
    --, nt_domain
    , start_time
    , qt.objectid
FROM sys.dm_exec_requests er
INNER JOIN sys.sysprocesses sp ON er.session_id = sp.spid
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle)as qt
WHERE session_Id > 50              -- Ignore system spids.
AND session_Id NOT IN (@@SPID)     -- Ignore this current statement.
ORDER BY 1, 2
GO
Related Question