Same query has different execution plan in SP
When the exact same query is placed within a stored procedure, it's generating an entirely different executing plan which takes ~ 18 seconds.
Versus when I run solo (and just DECLARE the variables above the query) it runs substantially faster ~ 1-2 seconds.
What can I do so that the query in the SP runs using the execution plan when run solo? FYI, I've even reproduced the issue by creating an entirely new stored procedure and literally copy-pasting the query into it. Same results of ~ 18 seconds.
The query is shown below for reference
select
COUNT(b.BookKey) as RowCounter
from
Books b (nolock)
inner join BookPublishRegions bp (nolock)
on b.BookKey = bp.BookKey
where
b.IsUnavailable = 0 and
(@AuthorKey is null or b.AuthorKey = @AuthorKey) and
(b.Price between @MinPrice and @MaxPrice) and
contains(bp.PublishRegionName, @SearchTerm)
I have indexes on b.IsUnavailable, b.AuthorKey, b.Price and a full-text index on bp.PublishRegionName.
Any thoughts?
Local variables and parameters are entirely different things even though the query appears identical. With local variables, SQL Server estimate row counts based on statistic average density values because the actual value is unknown at compile time. However, in the case of a parameter, the actual parameter values are "sniffed" and the estimate gleaned from the statistics histogram for the actual value supplied. The resultant execution plans may be different if the estimated number of rows differs significantly.
The symptoms you mention could be an indication that statistics are stale or the parameter sniffing issue mentioned. Try updating stats on the table with FULLSCAN and rerunning the parameterized query to see if the plan is better:
UPDATE STATISTICS ON dbo.Books WITH FULLSCAN;
UPDATE STATISTICS ON dbo.BookPublishRegions WITH FULLSCAN;
下一篇: 相同的查询在SP中具有不同的执行计划