title: SQL Server "Dry Run" mode? Load data buffers, without holding locks or changing data
body: <p>i'm going to running some queries against an SQL Server database, followed by a delete. Ideally all this happens inside a transaction (i.e. atomic).</p>
  
  <p>But practically, because the data has long since been purged from the buffers, SQL Server will have to perform a lot of physical IO in order to complete the transacted T-SQL. This can be a problem, because if the entire batch takes longer than 30 seconds to run, then users will experience timeout problems.</p>
  
  <p>i noticed that if i run my <code>select</code>s in pieces, each time running more and more of the final SQL, letting SQL Server fill the buffers with more and more of the required data. e.g.:</p>
  
  <p><strong>First run</strong>:</p>
  
  <pre><code> BEGIN TRANSACTION
   SELECT ... WHERE ...
   ROLLBACK
  </code></pre>
  
  <p><strong>Second run</strong>:</p>
  
  <pre><code> BEGIN TRANSACTION
   SELECT ... WHERE ...
   SELECT ... WHERE ...
   ROLLBACK
  </code></pre>
  
  <p>...</p>
  
  <p><strong>n-th run</strong>:</p>
  
  <pre><code>BEGIN TRANSACTION
  SELECT ... WHERE ...
  SELECT ... WHERE ...
  ...
  SELECT ... WHERE ...
  ROLLBACK
  </code></pre>
  
  <p>And by the time i reach the <strong>final run</strong>:</p>
  
  <pre><code>BEGIN TRANSACTION
  SELECT ... WHERE ...
  SELECT ... WHERE ...
  ...
  SELECT ... WHERE ...
  DELETE FROM ... WHERE ...
  COMMIT
  </code></pre>
  
  <p>The entire batch runs fast, since the buffers are pre-filled.</p>
  
  <p>Is there a mode of SQL Server (i.e. <code>SET NOEXEC ON</code>) that would cause SQL Server to not perform any actual data modifications, not take any locks, but fill the buffers with needed data? e.g.</p>
  
  <pre><code>SET NOEXEC ON
  EXECUTE ThatThingYouDo
  
  SET NOEXEC OFF
  EXECUTE ThatThingYouDo
  </code></pre>
  
  <p>or</p>
  
  <pre><code>SET DRYRUN ON
  EXECUTE ThatThingYouDo
  
  SET DRYRUN OFF
  EXECUTE ThatThingYouDo
  </code></pre>