title: Why would replacing a parameter with a local variable speed up a query
body: <p>I have a query that has two DATE parameters, as such:</p>
  <pre><code>@startDate DATE,
  @endDate DATE
  </code></pre>
  <p>While developing the sproc it was great (&lt; 1 second). Moved it into a stored procedure as a child and when I ran it again, it takes minutes to run (2 to be exact).</p>
  <p>I ran into this before (which I thought was some anomaly that I didn't pursue back then) so I tried the last &quot;hack&quot; that worked:</p>
  <pre><code>DECLARE @sDate DATE = CAST(@startDate AS DATE);
  DECLARE @eDate DATE = CAST(@endDate AS DATE);
  </code></pre>
  <p>And sure enough, back to &lt; 1s return times.</p>
  <p>I have tried everything to figure this out and nothing seems to work. I can't find differences anywhere that changes anything. The values are exactly the same, not matter how many different ways I try to slice it.</p>
  <p>I have also tried:</p>
  <pre><code>SET @startDate = CAST(@startDate AS DATE);
  SET @startDate = CONVERT(date, @startDate, 101)
  </code></pre>
  <p>And I have tried re-declaring them (using any method) in the parent sproc.</p>
  <p>It only works if I re-declare the variables in the child sproc.</p>
  <p>So, why would re-declaring a variable of the same type, result in such an extreme difference in performance?</p>
  <h3>UPDATE - It <em>Is</em> Parameter Sniffing</h3>
  <p>I didn't originally think so, but all evidence points to the fact that it is, even though I haven't been able to fix it with normal methods that typically either work or help identify it. <em><strong>Except</strong></em> replacing it with a local variable which with the help from all the posters below would indicate it <strong>has</strong> to be parameter sniffing.</p>
  <hr />
  <p>First Update</p>
  <p>I don't think this is parameter sniffing - which was my first thought. This is what I have done to test this:</p>
  <ul>
  <li>Changed parameters (add/remove)</li>
  <li>Added additional criteria to the query</li>
  <li>Added OPTION recompile</li>
  <li>SET ARITHABORT ON</li>
  <li>Drop/Created old and new indexes</li>
  </ul>
  <p>The changes above had no impact on the query.</p>