Can WAITFOR increase CXPACKET waits?

Posted on

Question :

If I have some T-SQL code like this:

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

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

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;

waitfor delay '00:10:00';

In another window you can run the following:

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.

No. CXPACKET is caused by parallel processes running on the SQL Server instance.

More info here

Leave a Reply

Your email address will not be published. Required fields are marked *