title: SQL poor stored procedure execution plan performance - parameter sniffing
body: <p>I have a stored procedure that accepts a date input that is later set to the current date if no value is passed in:</p>
  
  <pre><code>CREATE PROCEDURE MyProc
      @MyDate DATETIME = NULL
  AS
      IF @MyDate IS NULL SET @MyDate = CURRENT_TIMESTAMP
      -- Do Something using @MyDate
  </code></pre>
  
  <p>I'm having problems whereby if <code>@MyDate</code> is passed in as <code>NULL</code> when the stored procedure is first compiled, the performance is always terrible for all input values (<code>NULL</code> or otherwise), wheras if a date / the current date is passed in when the stored procedure is compiled performance is fine for all input values (<code>NULL</code> or otherwise).</p>
  
  <p>What is also confusing is that the poor execution plan that is generated in is terrible even when the value of @MyDate used is <strong>actually</strong> <code>NULL</code> (and not set to <code>CURRENT_TIMESTAMP</code> by the IF statement)</p>
  
  <p>I've discovered that disabling parameter sniffing (by spoofing the parameter) fixes my issue:</p>
  
  <pre><code>CREATE PROCEDURE MyProc
      @MyDate DATETIME = NULL
  AS
      DECLARE @MyDate_Copy DATETIME
      SET @MyDate_Copy = @MyDate
      IF @MyDate_Copy IS NULL SET @MyDate_Copy = CURRENT_TIMESTAMP
      -- Do Something using @MyDate_Copy
  </code></pre>
  
  <p>I know this is something to do with parameter sniffing, but all of the examples I've seen of "parameter sniffing gone bad" have involved the stored procedure being compiled with a non-representative parameter passed in, however here I'm seeing that the execution plan is terrible for all conceivable values that SQL server might think the parameter might take at the point where the statement is executed - <code>NULL</code>, <code>CURRENT_TIMESTAMP</code> or otherwise.</p>
  
  <p>Has anyone got any insight into why this is happening?</p>