Do you ever get complaints that SQL Server is “slow”? Yeah, that’s a helpful report, right? when I get those kinds of reports, I like to take a quick look at the activity on the server to see what is currently waiting and why. The trouble with this is there’s a LOT of white noise to filter through. Additionally, when you find a problem query, it generally leads to lots of follow-up queries for stuff like getting the query plan or the query text or getting info on a task that is blocking the query. As a result, I wrote my own query to filter out a lot of the white noise and return the extra information straight away.
My query filters out non-active sessions, system sessions, my own session that I’m using, and wait types that I don’t care about.
The Query
Declare @ExcludedWaits Table (WaitType sysname not null primary key) -- Waits that can be ignored Insert Into @ExcludedWaits Values ('CLR_SEMAPHORE'), ('SQLTRACE_BUFFER_FLUSH'), ('WAITFOR'), ('REQUEST_FOR_DEADLOCK_SEARCH'), ('XE_TIMER_EVENT'), ('BROKER_TO_FLUSH'), ('BROKER_TASK_STOP'), ('CLR_MANUAL_EVENT'), ('CLR_AUTO_EVENT'), ('FT_IFTS_SCHEDULER_IDLE_WAIT'), ('XE_DISPATCHER_WAIT'), ('XE_DISPATCHER_JOIN'), ('BROKER_RECEIVE_WAITFOR'); Select SessionID = WT.session_id, WaitDuration_ms = WT.wait_duration_ms, WaitType = WT.wait_type, WaitResource = WT.resource_description, Program = 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), BlocingSessionID = 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
(SFTW) SQL Server Links 13/12/13 • John Sansom
[…] 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). […]
T-SQL Tuesday #49: Wait For It Roundup | SQLSoldier
[…] 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 […]
Liam Gavin
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’),
(‘SQLTRACE_BUFFER_FLUSH’),
(‘WAITFOR’),
(‘REQUEST_FOR_DEADLOCK_SEARCH’),
(‘XE_TIMER_EVENT’),
(‘BROKER_TO_FLUSH’),
(‘BROKER_TASK_STOP’),
(‘CLR_MANUAL_EVENT’),
(‘CLR_AUTO_EVENT’),
(‘FT_IFTS_SCHEDULER_IDLE_WAIT’),
(‘XE_DISPATCHER_WAIT’),
(‘XE_DISPATCHER_JOIN’),
(‘BROKER_RECEIVE_WAITFOR’);
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 = ‘+ j.name
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
end,
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
SQLSoldier
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).
SQL Server is slow – Activity Monitor shows high number of Waiting tasks – Useful Query for Waiting task | Vikas D More
[…] Found this waiting tasks query from SqlSoldier to be very useful: […]
T-SQL Tuesday #87 – New Way to See Wait Stats for a Single Query – SQLSoldier
[…] 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. […]