What is the impact of WAITFOR on other processes and transactions?

Question

I try to find out the performance or internal implementation for WAITFOR in T-SQL, have gone through MSDN and Stackoverflow and other sites without luck, here is my question

For below code, I want to delete the top 10,000 rows from table DUMMY. I want to make this delete job have the least performance impact on the database's other jobs as possible and give priority to others (if any). So I make it delete 100 rows at a time and do it 100 times with sleep time in two adjacent deletes.

Question:

1. During the WAITFOR blocking time, will this transaction consume CPU or just idle and waiting for kicked up by some event 1 second later?

2. During that 1 sec, if there are other transactions trying to INSERT/UPDATE on the DUMMY table, who gets priority?

Really appreciate your help or any insights for this

declare @cnt int 
set @cnt = 0
while @cnt < 100
begin
  delete top 100 from DUMMYTABLE where FOO = 'BAR'
  set @cnt = @cnt + 1
  waitfor delay '00:00:01'
end

Answer 1

• It does not consume any CPU
• Status = suspended

You can see this with 2 query windows:

SELECT @@SPID;
GO
WAITFOR DELAY '000:03:00'; -- three minutes

Then in the other

SELECT * FROM sys.sysprocesses S WHERE S.spid = 53; -- replace 53

Note: SQL Server 2012 SP1 but AFAIK behaviour is the same

Point 2, sorry missed this

Another session will modify the table while the WAITFOR is running. It isn't a lock.