4

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?

8

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.

2

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

More info here

  • Yes, exactly. So if one process was running this piece of code would the other threads be waiting at the WAITFOR command? – dwjv Oct 16 '14 at 11:10
  • 1
    The WAITFOR just delays the execution of the next line of code. There is nothing waiting on it. It holds no locks on anything. – James Anderson Oct 16 '14 at 11:15
  • Right, I understand. So whilst I'm waiting the 30 seconds, nothing will increase in sys.dm_os_wait_stats (for that command). – dwjv Oct 16 '14 at 11:32
  • 1
    WAITFOR has no concern with CXPACKET, rather it will generate wait type of "WAITFOR". An animated video how waits occur connectsql.com/2014/04/video-sql-server-performance-tuning.html And What are CXPACKET waits connectsql.com/2014/04/… – aasim.abdullah Oct 16 '14 at 11:33
  • That isn't quite correct. It will increase the WAITFOR wait type in sys.dm_os_wait_stats. – Thomas Stringer Oct 16 '14 at 11:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.