Different execution plan when executing statement directly and from stored procedure

Question

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).

Here's the query

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)

When I run the query "raw" I get the following trace stats

Event Class         Duration  CPU  Reads Writes
SQL:StmtCompleted   75       41      7      0

And when I run the query as a stored proc using the following command

DECLARE @ShopId int
SELECT @ShopId = 1
EXEC spStats_GetSalesStatsByHour @ShopId

I get the following trace stats

Event Class         Duration  CPU  Reads Writes
SQL:StmtCompleted   222       10     48      0

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)

DECLARE @SQL nvarchar(2000)
SET @SQL = 'SELECT DATEADD(dd, ...'
exec sp_executesql @SQL

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?

We're currently running on SQL Server 2000

Answer 1

This generally has something to do with parameter sniffing. It can be very frustrating to deal with. Sometimes it can be solved by recompiling the stored procedure, and sometimes you can even use a duplicate variable inside the stored procedure like this:

alter procedure p_myproc (@p1 int) as
declare @p1_copy int;
set @p1_copy = @p1;

And then use @p1_copy in the query. Seems ridiculous but it works.

Check my recent question on the same topic:

http://stackoverflow.com/questions/414336/why-does-the-sqlserver-optimizer-get-so-confused-with-parameters