title: Why does the SqlServer optimizer get so confused with parameters?
body: <p>I know this has something to do with parameter sniffing, but I'm just perplexed at how something like the following example is even possible with a piece of technology that does so many complex things well.</p>
  
  <p>Many of us have run into stored procedures that intermittently run several of orders of magnitude slower than usual, and then if you copy out the sql from the procedure and use the same parameter values in a separate query window, it runs as fast as usual.</p>
  
  <p>I just fixed a procedure like that by converting this:</p>
  
  <pre><code>alter procedure p_MyProc
  (
      @param1 int
  ) as -- do a complex query with @param1
  </code></pre>
  
  <p>to this:</p>
  
  <pre><code>alter procedure p_MyProc
  (
      @param1 int
  )
  as
  
  declare @param1Copy int;
  set @param1Copy = @param1;
  
  -- Do the query using @param1Copy
  </code></pre>
  
  <p>It went from running in over a minute back down to under one second, like it usually runs.  This behavior seems totally random.  For 9 out of 10 @param1 inputs, the query is fast, regardless of how much data it ends up needing to crunch, or how big the result set it.  But for that 1 out of 10, it just gets lost.  And the fix is to replace an int with the same int in the query?</p>
  
  <p>It makes no sense.</p>
  
  <p>[Edit]</p>
  
  <p>@gbn linked to this question, which details a similar problem:</p>
  
  <p><a href="https://stackoverflow.com/questions/379007/known-issue-sql-server-2005-stored-procedure-fails-to-complete-with-a-parameter">Known issue?: SQL Server 2005 stored procedure fails to complete with a parameter</a></p>
  
  <p>I hesitate to cry "Bug!" because that's so often a cop-out, but this really does seem like a bug to me.  When I run the two versions of my stored procedure with the same input, I see identical query plans.  The only difference is that the original takes more than a minute to run, and the version with the goofy parameter copying runs instantly.</p>