title: Random timeout running a stored proc - drop recreate fixes
body: <p>So I work with a largish database (30 gig) sql 2005 with a .net 3.5  web front end on a 10 year old system. It has new and old bits</p>
  
  <p>We are getting a problem that is happening more and more frequently.</p>
  
  <p>A stored proc (we've had 4 different ones so far) decides that it will timeout. The call is happening from the webserver and hits the 30 sec timeout and logs to our error log. The website uses a single login (I know this is wrong but it cannot be changed due to legacy code). </p>
  
  <p>Just after this I run the exact same call and it takes (logged in as me) 1 sec.</p>
  
  <p>The issue remains on this one stored proc until we drop and recreate it, getting loads of timeouts. Each sp call has different parameters. 
  As in get me all the unsigned off shifts pertaining to the current user, so current user is passed in as a parameter</p>
  
  <p>The solution works but I don't really understand why.</p>
  
  <p>Our release cycle is two weeks and this error happens at anytime during it. It has happens the day after a release a week after the release and the last one was 12 days after the release.</p>
  
  <p>Durign each release we SQL multi script all the stored procs/triggers/functions/views with each dropping and recreateing itself.</p>
  
  <p>All I can think is that the stored proc execution plan has corrupted/gone wrong and dropping recreateing it clears this.</p>
  
  <p>I am thinking of calling the sps WITH RECOMPILE option, is this a no-no?? or an acceptable way around</p>