title: Different execution plan when executing statement directly and from stored procedure
body: <p>While developing a new query at work I wrote it and profiled it in SQL Query Analyzer. The query was performing really good without any table scans but when I encapsulated it within a stored procedure the performance was horrible. When I looked at the execution plan I could see that SQL Server picked a different plan that used a table scan instead of an index seek on TableB (I've been forced to obfuscate the table and column names a bit but none of the query logic has changed).</p>
  
  <p>Here's the query</p>
  
  <pre><code>SELECT     
      DATEADD(dd, 0, DATEDIFF(dd, 0, TableA.Created)) AS Day, 
      DATEPART(hh, TableA.Created) AS [Hour], 
      SUM(TableB.Quantity) AS Quantity, 
      SUM(TableB.Amount) AS Amount
  FROM
      TableA
      INNER JOIN TableB ON TableA.BID = TableB.ID
  WHERE     
      (TableA.ShopId = @ShopId)
  GROUP BY 
      DATEADD(dd, 0, DATEDIFF(dd, 0, TableA.Created)), 
      DATEPART(hh, TableA.Created)
  ORDER BY 
      DATEPART(hh, TableA.Created)
  </code></pre>
  
  <p>When I run the query "raw" I get the following trace stats</p>
  
  <pre>
  Event Class         Duration  CPU  Reads Writes
  SQL:StmtCompleted   75        41      7      0
  </pre>
  
  <p>And when I run the query as a stored proc using the following command</p>
  
  <pre><code>DECLARE @ShopId int
  SELECT @ShopId = 1
  EXEC spStats_GetSalesStatsByHour @ShopId
  </code></pre>
  
  <p>I get the following trace stats</p>
  
  <pre>
  Event Class         Duration  CPU  Reads Writes
  SQL:StmtCompleted   222       10     48      0
  </pre>
  
  <p>I also get the same result if I store the query in an nvarchar and execute it using sp_executesql like this (it performs like the sproc)</p>
  
  <pre><code>DECLARE @SQL nvarchar(2000)
  SET @SQL = 'SELECT DATEADD(dd, ...'
  exec sp_executesql @SQL
  </code></pre>
  
  <p>The stored procedure does not contain anything except for the select statement above. What would cause sql server to pick an inferior execution plan just because the statement is executed as a stored procedure?</p>
  
  <p>We're currently running on <strong>SQL Server 2000</strong></p>