2

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.

1
0

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
1
  • When I'm looking to "Processes pane", I can't see any connections with "wait time" more than 0. And unfortunately your script doesn't show any rows with "blocked by" not equals 0 or "wait" not equal NULL. P.S. waiting tasks in Activity Monitor showing even if no any users connected to DB.
    – whitebeast
    Jun 5 '15 at 14:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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