title: Getting Around Parameter Sniffing in SQL Server 2005
body: <p>I have seen people suggest copying the parameters to a local variable to avoid parameter sniffing in a stored proc.  Say you have</p>
  
  <pre><code>CREATE PROCEDURE List_orders_3 @fromdate datetime AS
   DECLARE @fromdate_copy datetime
   SELECT @fromdate_copy = @fromdate
   SELECT * FROM Orders WHERE OrderDate &gt; @fromdate_copy
  </code></pre>
  
  <p>(I got this from <a href="http://www.sommarskog.se/query-plan-mysteries.html" rel="nofollow">http://www.sommarskog.se/query-plan-mysteries.html</a> but I need more details to understand it fully). </p>
  
  <p>But what does this actually do to the query plan cache and query plan optimizer? If it is true that the optimizer makes no assumptions about @fromdate_copy, then why is it that it won't cache a plan that is most likely going to be a full table scan (since it makes no assumptions, how could it generate anything else)? </p>
  
  <p>Is this technique basically like a "no inputs will run well, but no input will run terribly either" ? </p>