Another Slow Execution Plan with sp_prepare

We are having a similar problem. We have a 3rd party application that uses
sp_prepare and then executes it. The problem is the query plan from the
sp_prepare statement is different than if you run the statement using
sp_execute or sp_executesql. I run the following statements:
declare @.P1 int
exec sp_prepare @.P1 output, N'@.P1 bigint,@.P2 bigint,@.P3 bigint,@.P4
bigint,@.P5 bigint,@.P6 bigint,@.P7 bigint,@.P8 bigint', 'SELECT SHAPE
,S_.eminx,S_.eminy,S_.emaxx,S_.emaxy ,SHAPE.fid F_fid,SHAPE.numofpts
F_numofpts,SHAPE.entity F_entity,SHAPE.points F_points FROM (SELECT DISTINC
T
sp_fid,eminx,eminy,emaxx,emaxy FROM SDE.SDE.s162 SP_ WHERE SP_.gx >= @.P1 AN
D
SP_.gx <= @.P2 AND SP_.gy >= @.P3 AND SP_.gy <= @.P4 AND SP_.eminx <= @.P5 AND
SP_.eminy <= @.P6 AND SP_.emaxx >= @.P7 AND SP_.emaxy >= @.P8 ) S_
,SDE.SDE.ENTORDERLINESEGMENT, SDE.SDE.f162 SHAPE WHERE S_.sp_fid = SHAPE.fi
d
AND SDE.SDE.ENTORDERLINESEGMENT.SHAPE = S_.sp_fid AND (( ORDERID in (16320,
16825) ))', 1 select @.P1
exec sp_execute 1, 166, 169, 219, 224, 90269119, 119480870, 88777840,
117193071
This takes almost a minute to return.
Then I run this statement:
declare @.P1 int
exec sp_prepare @.P1 output, N'@.P1 bigint,@.P2 bigint,@.P3 bigint,@.P4
bigint,@.P5 bigint,@.P6 bigint,@.P7 bigint,@.P8 bigint', 'SELECT SHAPE
,S_.eminx,S_.eminy,S_.emaxx,S_.emaxy ,SHAPE.fid F_fid,SHAPE.numofpts
F_numofpts,SHAPE.entity F_entity,SHAPE.points F_points FROM (SELECT DISTINC
T
sp_fid,eminx,eminy,emaxx,emaxy FROM SDE.SDE.s162 SP_ WHERE SP_.gx >= @.P1 AN
D
SP_.gx <= @.P2 AND SP_.gy >= @.P3 AND SP_.gy <= @.P4 AND SP_.eminx <= @.P5 AND
SP_.eminy <= @.P6 AND SP_.emaxx >= @.P7 AND SP_.emaxy >= @.P8 ) S_
,SDE.SDE.ENTORDERLINESEGMENT, SDE.SDE.f162 SHAPE WHERE S_.sp_fid = SHAPE.fi
d
AND SDE.SDE.ENTORDERLINESEGMENT.SHAPE = S_.sp_fid AND (( ORDERID in (16320,
16825) ))', 1 select @.P1
DBCC Freeproccache
exec sp_execute 1, 166, 169, 219, 224, 90269119, 119480870, 88777840,
117193071
This returns in under 1 second. The only difference is the freeproccache.
The first sp_execute is using the store query plan from cache. The second i
s
forced to compile a new execution plan which is much faster.
Does anyone know why the sp_prepare query execution plan would be slower?
Shouldn't it create the same plan?
Also, how did you change sp_prepare to use varchar instead of nvarchar? I
get an error stating the sp_prepare expects @.statement of type
ntext/nchar/nvarchar.
Thanks for any help.
Doug Matney
"lorinda" wrote:

> This makes sense. Looks like I will have to find a work around. Thanks
> again for your help!
>dmatney (dmatney@.discussions.microsoft.com) writes:
> We are having a similar problem. We have a 3rd party application that
> uses sp_prepare and then executes it. The problem is the query plan
> from the sp_prepare statement is different than if you run the statement
> using sp_execute or sp_executesql. I run the following statements:
>...
> This returns in under 1 second. The only difference is the
> freeproccache. The first sp_execute is using the store query plan from
> cache. The second is forced to compile a new execution plan which is
> much faster.
> Does anyone know why the sp_prepare query execution plan would be slower?
> Shouldn't it create the same plan?
Since I have never understood the point with sp_prepare, or what it really
achieves, this had me stumped at first, but I think I know the answer.
SQL Server is fond of parameter sniffing. This means that when you
run something that has parameters, be that a stored procedure or
sp_executesql, it looks at the parameter values and take these as
guidance for the plan.
But when you run sp_prepare there are no actual parameter values. Still
SQL Server builds a plan this point, using standard assumptions. Your
query includs a lot of >=. I believe the standard assumption here is
a 30% hit-rate. This is far above the limit where a non-clustered index
is deemed to be more expensive than a table scan.
When you flush the cache, there is no plan for sp_execute to use, so
a new plan has to be created, and since the parameter values are now
available, the optimizer can "sniff" them, and build a plan that better
fits the actual values. Presumably, the input values are close to the
edges.
Since this is from a third-party app, I guess your options to address
this are limited. You could investigate if you could make any of the
involved indexes clustered, but assuming that there is a clustered index
already, this could have ramifications elsewhere in the application.
If you are on SQL 2005, you could add a plan guide, which is quite an
advanced exercise.

> Also, how did you change sp_prepare to use varchar instead of nvarchar? I
> get an error stating the sp_prepare expects @.statement of type
> ntext/nchar/nvarchar.
I guess you don't. It's the same with sp_executesql. The parameter is
ntext (nvarchar(MAX) in SQL 2005), and since this is a built-in stored
procedure, there is no implicit conversion from varchar.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks. I think you are correct about the parameter sniffing. I have tried
creating a plan guide using a Recompile hint and it works for this particula
r
sp_prepare but since the application doesn't use parameters for all values (
the OrderID in clause uses real values) I can't get it to work every time.
I'm going to do a little more research and see if we can't convince the
application developer to use a different method. Thanks again for your help
.
"Erland Sommarskog" wrote:

> dmatney (dmatney@.discussions.microsoft.com) writes:
> Since I have never understood the point with sp_prepare, or what it really
> achieves, this had me stumped at first, but I think I know the answer.
> SQL Server is fond of parameter sniffing. This means that when you
> run something that has parameters, be that a stored procedure or
> sp_executesql, it looks at the parameter values and take these as
> guidance for the plan.
> But when you run sp_prepare there are no actual parameter values. Still
> SQL Server builds a plan this point, using standard assumptions. Your
> query includs a lot of >=. I believe the standard assumption here is
> a 30% hit-rate. This is far above the limit where a non-clustered index
> is deemed to be more expensive than a table scan.
> When you flush the cache, there is no plan for sp_execute to use, so
> a new plan has to be created, and since the parameter values are now
> available, the optimizer can "sniff" them, and build a plan that better
> fits the actual values. Presumably, the input values are close to the
> edges.
> Since this is from a third-party app, I guess your options to address
> this are limited. You could investigate if you could make any of the
> involved indexes clustered, but assuming that there is a clustered index
> already, this could have ramifications elsewhere in the application.
> If you are on SQL 2005, you could add a plan guide, which is quite an
> advanced exercise.
>
> I guess you don't. It's the same with sp_executesql. The parameter is
> ntext (nvarchar(MAX) in SQL 2005), and since this is a built-in stored
> procedure, there is no implicit conversion from varchar.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>