Brief Description about WAITs

---------------------------------------

-- WAITS
---------------------------------------
/*
Some of the wait types should be ignored which are system wait types
create a view to ignore certain wait types
*/
create view dbo.lib_vw_ignore_waittypes
as
select 'DBMIRRORING_CMD' as wait_type
union all
select 'BROKER_EVENTHANDLER'
union all
select 'BROKER_TRANSMITTER'
union all
select 'BROKER_RECEIVE_WAITFOR'
union all
select 'ONDEMAND_TASK_QUEUE'
union all
select 'REQUEST_FOR_DEADLOCK_SEARCH'
union all
select 'DBMIRROR_EVENTS_QUEUE'
union all
select 'SQLTRACE_BUFFER_FLUSH'
union all
select 'BAD_PAGE_PROCESS'
union all
select 'CHECKPOINT_QUEUE'
union all
select 'DBMIRROR_EVENTS_QUEUE'
union all
select 'LAZYWRITER_SLEEP'
union all
select 'LOGMGR_QUEUE'
union all
select 'KSOURCE_WAKEUP'
go
-------------------------------------------------------------------------
/*
Performance of the query depends on Current Waits
availbale in sys.dm_os_waiting_tasks.
Find waits currently occuring on the system
check wait_duration_ms to identify waits with high wait duration
select * from sys.dm_os_waiting_tasks

Troubleshooting Blocking

1. Identify blocking
2. Cause of blocking
3. Remove the cause of blocking

select * from sys.dm_os_waiting_tasks provides blocking information
Session currently executing is associated with a task

Some systems tasks will not have session_id

1. waiting tasks columns
a. waiting_task_address ( unique internal memory address of the object
representing the task)
b. session_id ( user session associated with the task.
Association between session and task is only for the duration of the task )
c. exec_context_id
2. blocking task information columns
a. blocking_task_address
b. blocking_session_id
c. blocking_exec_context_id

3. wait information

a. wait_type ( current_wait_type for the waiting_task_address)
b. wait_duration_ms ( duration of the current wait)
c. resource_address ( memory address of the resource on which task is waiting)
d. resource_description ( populated only for locks,latch,CXPACKET,THREADPOOL)
*/

SELECT
WT.*
FROM sys.dm_os_waiting_tasks AS WT
where wait_type not in (select wait_type from master.dbo.lib_vw_ignore_waittypes)
/*

1. Check Wait_type column . If this column is repeated for several session_ids
with the same resource_id for long duration, resolve this wait_type

2. Check resource_address. If same resource_address is repeated then there is
a problem with the resource

3. wait_duration_ms provides evidence of high wait time
*/
-------------------------------------------------------------------------------

/*
sys.dm_os_wait_stats -- provides cumulative
wait type statistics from the time of sql server tart
-- You can clear the wait static information .
dbcc sqlperf('sys.dm_os.wait_stats','CLEAR')
-- NOTE: Avoid wait statisitcs clear in production environment
By default all wait types are not listed from sys.dm_os.wait_stats
-- To display all the wait types
dbcc traceon(8001,-1)
select * from sys.dm_os_wait_stats
*/

/*
Compare Signal Waits and Resource Waits
Signal waits are the time spent in the runnable queue waiting for the CPU,
while resource waits are the time spent waiting for the
resource (wait_time_ms - signal_wait_time_ms).
Wait_time_ms represents the total waits.
Check for CPU Issue

If %signal waits is around 25% and above, CPU issue

*/
Select signal_wait_time_ms=sum(signal_wait_time_ms)
,'%signal waits' = cast(100.0 * sum(signal_wait_time_ms) / sum (wait_time_ms) as numeric(20,2))
,resource_wait_time_ms=sum(wait_time_ms - signal_wait_time_ms)
,'%resource waits'= cast(100.0 * sum(wait_time_ms - signal_wait_time_ms) / sum (wait_time_ms) as numeric(20,2))
From sys.dm_os_wait_stats
where wait_type not in (select wait_type from master.dbo.lib_vw_ignore_waittypes)
go

-------------------------------------------------------------------------------------

-- Wait Types with high wait times
select top 10 *,wait_time_ms*1.0000/waiting_tasks_count as avg_wait_ms,
wait_time_ms/1000.00/60.00/24.00 as wait_time_days,
max_wait_time_ms/1000.00/60.00 as max_wait_time_min
from sys.dm_os_wait_stats
where wait_type not in (select wait_type from master.dbo.lib_vw_ignore_waittypes)
order by wait_time_ms desc

-----------------------------------------------------------------------------------
-- Wait Types with high maximum wait time
select top 10 *,
wait_time_ms/1000.00/60.00/24.00 as wait_time_days,
max_wait_time_ms/1000.00/60.00 as max_wait_time_min
from sys.dm_os_wait_stats
where wait_type not in (select wait_type from master.dbo.lib_vw_ignore_waittypes)
order by max_wait_time_ms desc
----------------------------------------------------------------------------
-- Wait Types with high waiting tasks count
select top 10 *, wait_time_ms*1.0000/waiting_tasks_count as avg_wait_ms,
wait_time_ms/1000.00/60.00/24.00 as wait_time_days,
max_wait_time_ms/1000.00/60.00 as max_wait_time_min
from sys.dm_os_wait_stats
where wait_type not in (select wait_type from master.dbo.lib_vw_ignore_waittypes)
order by waiting_tasks_count desc
------------------------------------------------------------------------
-- Wait Types with high average wait time

select top 20 *, wait_time_ms*1.0000/waiting_tasks_count as avg_wait_ms,
wait_time_ms/1000.00/60.00/24.00 as wait_time_days,
max_wait_time_ms/1000.00/60.00 as max_wait_time_min
from sys.dm_os_wait_stats
where wait_type not in (select wait_type from master.dbo.lib_vw_ignore_waittypes)
and waiting_tasks_count > 0
order by avg_wait_ms desc

------------------------------------------------------------------------------