title: MS SQL Server 2005 - Stored Procedure "Spontaneously Breaks"
body: <p>A client has reported repeated instances of Very strange behaviour when executing a stored procedure.</p>
  
  <p>They have code which runs off a cached transposition of a volatile dataset.  A stored proc was written to reprocess the dataset on demand if:<br>
  1. The dataset had changed since the last reprocessing<br>
  2. The datset has been unchanged for 5 minutes  </p>
  
  <p>(The second condition stops massive repeated recalculation during times of change.)</p>
  
  <p><br></p>
  
  <p>This worked fine for a couple of weeks, the SP was taking 1-2 seconds to complete the re-processing, and it only did it when required.  Then...  </p>
  
  <ul>
  <li>The SP suddenly "stopped working" (it just kept running and never returned)  </li>
  <li>We changed the SP in a subtle way and it worked again  </li>
  <li>A few days later it stopped working again  </li>
  <li>Someone then said "we've seen this before, just recompile the SP"  </li>
  <li>With no change to the code we recompiled the SP, and it worked  </li>
  <li>A few days later it stopped working again  </li>
  </ul>
  
  <p><br></p>
  
  <p>This has now repeated many, many times.  The SP suddenly "stops working", never returning and the client times out.  (We tried running it through management studio and cancelled the query after 15 minutes.)</p>
  
  <p>Yet every time we recompile the SP, it suddenly works again.</p>
  
  <p>I haven't yet tried WITH RECOMPILE on the appropriate EXEC statments, but I don't particularly want to do that any way.  It gets called hundred of times an hour and normally does Nothing (It only reprocesses the data a few times a day).  If possible I want to avoid the overhead of recompiling what is a relatively complicated SP "just to avoid something which "shouldn't" happen...</p>
  
  <p><br></p>
  
  <ul>
  <li>Has anyone experienced this before?  </li>
  <li>Does anyone have any suggestions on how to overcome it?</li>
  </ul>
  
  <p><br></p>
  
  <p>Cheers,<br>
  Dems.</p>
  
  <p><br></p>
  
  <p><strong>EDIT:</strong></p>
  
  <p>The pseduo-code would be as follows:</p>
  
  <ul>
  <li>read "a" from table_x</li>
  <li>read "b" from table_x</li>
  <li>If (a &lt; b) return</li>
  <li>BEGIN TRANSACTION</li>
  <li>DELETE table_y</li>
  <li>INSERT INTO table_y &lt;3 selects unioned together></li>
  <li>UPDATE table_x</li>
  <li>COMMIT TRANSACTION</li>
  </ul>
  
  <p>The selects are "not pretty", but when executed in-line they execute in no time.  Including when the SP refuses to complete.  And the profiler shows it is the INSERT at which the SP "stalls"</p>
  
  <p>There are no parameters to the SP, and sp_lock shows nothing blocking the process.</p>