Comments (6)

  1. […] My Go-To Query For Waiting Tasks – Find out what a SQL Server MCM uses to get right down to the WAIT types that really matter, its Robert L. Davis(Blog|Twitter). […]

  2. […] L Davis (blog|@SQLSoldier) — that’s me — shared my My Go-To Query For Waiting Tasks that I use whenever I get a basic report that SQL Server is […]

  3. Nice query, I added this change to bring in SQL job name:

    Declare @ExcludedWaits Table (WaitType sysname not null primary key)

    — Waits that can be ignored
    Insert Into @ExcludedWaits
    Values (‘CLR_SEMAPHORE’),

    Select SessionID = WT.session_id,
    WaitDuration_ms = WT.wait_duration_ms,
    WaitType = WT.wait_type,
    WaitResource = WT.resource_description,
    Program = case left(S.program_name,15)
    when ‘SQLAgent – TSQL’ THEN
    (select top 1 ‘SQL Job = ‘+
    from msdb.dbo.sysjobs (nolock) j
    inner join msdb.dbo.sysjobsteps (nolock)js on js.job_id = j.job_id
    where right(cast(js.job_id as nvarchar(50)),10) = RIGHT(substring(S.program_name,30,34),10) )
    when ‘SQL Server Prof’ then ‘SQL Server Profiler’
    else S.program_name
    QueryPlan = CP.query_plan,
    SQLText = SUBSTRING(ST.text, (R.statement_start_offset/2)+1,
    ((Case R.statement_end_offset
    When -1 Then DATALENGTH(ST.text)
    Else R.statement_end_offset
    End – R.statement_start_offset)/2) + 1),
    DBName = DB_NAME(R.database_id),
    BlockingSessionID = WT.blocking_session_id,
    BlockerQueryPlan = CPBlocker.query_plan,
    BlockerSQLText = SUBSTRING(STBlocker.text, (RBlocker.statement_start_offset/2)+1,
    ((Case RBlocker.statement_end_offset
    When -1 Then DATALENGTH(STBlocker.text)
    Else RBlocker.statement_end_offset
    End – RBlocker.statement_start_offset)/2) + 1)
    From sys.dm_os_waiting_tasks WT
    Inner Join sys.dm_exec_sessions S on WT.session_id = S.session_id
    Inner Join sys.dm_exec_requests R on R.session_id = WT.session_id
    Outer Apply sys.dm_exec_query_plan (R.plan_handle) CP
    Outer Apply sys.dm_exec_sql_text(R.sql_handle) ST
    Left Join sys.dm_exec_requests RBlocker on RBlocker.session_id = WT.blocking_session_id
    Outer Apply sys.dm_exec_query_plan (RBlocker.plan_handle) CPBlocker
    Outer Apply sys.dm_exec_sql_text(RBlocker.sql_handle) STBlocker
    Where R.status = ‘suspended’ — Waiting on a resource
    And S.is_user_process = 1 — Is a used process
    And R.session_id @@spid — Filter out this session
    And WT.wait_type Not Like ‘%sleep%’ — more waits to ignore
    And WT.wait_type Not Like ‘%queue%’ — more waits to ignore
    And WT.wait_type Not Like — more waits to ignore
    Case When SERVERPROPERTY(‘IsHadrEnabled’) = 0 Then ‘HADR%’
    Else ‘zzzz’ End
    And Not Exists (Select 1 From @ExcludedWaits
    Where WaitType = WT.wait_type)
    Option(Recompile); — Don’t save query plan in plan cache

  4. Thanks Liam! Nice idea!

    I would recommend writing it as either a CTE or a LEFT JOIN rather than a subquery to avoid executing the query repeatedly on a really busy system. Also, the use of hints without the WITH is deprecated so if you are going to use nolock, you should use WITH(nolock).

  5. […] SQL Server 2005 gave us a new way to view wait statistics for queries, Dynamic Management Views (DMVs). Two of the DMVs in particular give you information about the wait stats for an actively executing query: sys.dm_exec_requests and sys.dm_os_waiting_tasks. Several years ago, I blogged the query I was using to check the waits for currently executing queries: T-SQL Tuesday #49: My Go-To Query For Waiting Tasks. […]

Leave a Reply