Sql-server – Can WAITFOR increase CXPACKET waits

parallelismsql serverwait-types

If I have some T-SQL code like this:

While @done<1
Begin
WAITFOR DELAY '00:00:30';
Set @done=<some select statement>
End

Is this likely to increase waits for CXPACKET? Presumably the other processor(s) will be waiting whilst WAITFOR is running?

Best Answer

When you issue a WAITFOR command, the task will start having a wait_type of WAITFOR. This is a benign wait type and can be ignored.

Likewise, when this task enters the suspended state it will get off of the scheduler (processor) so it won't be taking up worker time. We can see this with a simple example:

use AdventureWorks2012;
go

waitfor delay '00:10:00';
go

In another window you can run the following:

select
    session_id,
    command,
    status,
    wait_type
from sys.dm_exec_requests
where command = 'waitfor';

My results are as follows:

session_id  command   status      wait_type
54          WAITFOR   suspended   WAITFOR

As for your question of how this will reflect in sys.dm_os_wait_stats, upon the WAITFOR wait type's completion, it will then increment the WAITFOR type in sys.dm_os_wait_stats for the duration that the task was waiting for:

select *
from sys.dm_os_wait_stats
where wait_type = 'waitfor';

But as said previously, the WAITFOR wait type is benign and can generally be ignored.

Related Question