title: What is the impact of WAITFOR on other processes and transactions?
body: <p>I try to find out the performance or internal implementation for WAITFOR in T-SQL, have gone through <a href="http://msdn.microsoft.com/en-US/library/ms187331%28v=SQL.90%29.aspx" rel="noreferrer">MSDN</a> and Stackoverflow and other sites without luck, here is my question</p>
  
  <p>For below code, I want to delete the top 10,000 rows from table <code>DUMMY</code>. 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.</p>
  
  <p>Question:</p>
  
  <ol>
  <li><p>During the <code>WAITFOR</code> blocking time, will this transaction consume CPU or just idle and waiting for kicked up by some event 1 second later?</p></li>
  <li><p>During that 1 sec, if there are other transactions trying to <code>INSERT/UPDATE</code> on the <code>DUMMY</code> table, who gets priority? </p></li>
  </ol>
  
  <p>Really appreciate your help or any insights for this</p>
  
  <pre><code>declare @cnt int 
  set @cnt = 0
  while @cnt &lt; 100
  begin
    delete top 100 from DUMMYTABLE where FOO = 'BAR'
    set @cnt = @cnt + 1
    waitfor delay '00:00:01'
  end
  </code></pre>