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?
When you issue a
WAITFOR command, the task will start having a
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.
No. CXPACKET is caused by parallel processes running on the SQL Server instance.
More info here