title: Stored procedure hangs seemingly without explanation
body: <p>we have a stored procedure that ran fine until 10 minutes ago and then it just hangs after you call it. </p>
  
  <p>Observations:</p>
  
  <ul>
  <li>Copying the code into a query window yields the query result in 1 second</li>
  <li>SP takes > 2.5 minutes until I cancel it</li>
  <li>Activity Monitor shows it's not being blocked by anything, it's just doing a SELECT.</li>
  <li>Running sp_recompile on the SP doesn't help</li>
  <li>Dropping and recreating the SP doesn't help</li>
  <li>Setting LOCK_TIMEOUT to 1 second does not help</li>
  </ul>
  
  <p>What else can be going on?</p>
  
  <hr>
  
  <p><strong>UPDATE</strong>: I'm guessing it had to do with parameter sniffing. I used Adam Machanic's routine to find out which subquery was hanging. I found things wrong with the query plan thanks to the hint by Martin Smith. I learned about <code>EXEC ... WITH RECOMPILE</code>, <code>OPTION(RECOMPILE)</code> for subqueries within the SP, and <code>OPTION (OPTIMIZE FOR (@parameter = 1))</code> in order to attack parameter sniffing. I still don't know what was wrong in this particular case but I came out of this battle seasoned and much better armed. I know what to do next time. So here's the points!</p>