直接执行语句和从存储过程执行语句时的不同执行计划

Question

在开发新的查询时，我编写了它并在SQL查询分析器中对其进行了分析。查询表现非常好，没有任何表扫描，但是当我将其封装在存储过程中时，性能非常糟糕。当我查看执行计划时，我可以看到SQL Server选择了一个不同的计划，它使用表扫描而不是TableB上的索引查找（我已经强制混淆了表和列名，但没有查询逻辑已经改变）。

这里的查询

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) 

当我运行查询“原始”我得到了下面的跟踪统计

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

当我使用下面的命令

运行查询作为存储过程

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

我收到以下跟踪统计

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

我也得到了同样的结果，如果我存储查询在一个nvarchar和使用sp_executesql如下执行（它执行如存储过程）

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

该存储过程并不包含除什么上面的select语句。什么会导致sql server仅仅因为语句被作为存储过程执行而选择劣质执行计划？

目前，我们正在的SQL Server 2000

Answer 1

上运行这通常有事情做与参数嗅探。处理可能非常令人沮丧。有时候，它可以通过重新编译存储过程来解决，有时你甚至可以用重复的变量的存储过程中是这样的：

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

然后在查询中使用@ p1_copy。似乎荒谬，但它的作品。

检查我最近的问题上的同一主题：

Why does the SqlServer optimizer get so confused with parameters?

Answer 2

是 - 我已经看到了这对Oracle数据库11g作为以及 - 相同的查询SQL时迅速跑快于数据库服务器的2个节点但是当从包中调用时，它就会挂起来！

必须清除共享池以获得相同的行为：原因某些作业/脚本正在运行，而旧版本的副本被锁定在库缓存/内存中，而且该执行计划的执行计划较差。