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
>

Analyzing a Query in SQL Server

I have to duplicate a bunch of reports that were produced by a VB6 app. Now I'm using C#, ASP.net and Crystal Reports via VS2003. Each executes a stored procedure in SQL Server (before and now).

For each report I have a Stored Procedure and a View. The View's SQL code is identical to the Stored Procedure except that the two input parameters (startDate and endDate) are removed because Views don't allow parameters.

Some of the reports work perfectly right off the bat. But others are timing out. My initial test of the timing out is to just display the View. If it fails then I know that the the report engine will fail too.

So now I'm trying out the SQL Query Analyzer tool to execute the code in one of the Views. It's now at 29 minutes and still going - at least it hasn't timed out!

My question is this: Is there a way I could examine what's going on with the query to see why it's taking so long?

Robert WernerHave a look at the query execution plan in the query analyzer, do you see any bottlenecks there ? During execution you might encounter a deadlock. looking in the appropiate node in SQL Server Enterprise Manager (Current Activity) you might be able to find the blocking process.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||Jens,

Thank you for your suggestion. I've never used that Execution Plan before so just tried it out on one of the queries that's timing out. I took the liberty of taking a screen capture and posting it here:

http://pocketpollster.com/downloads/public/WBMCST11_Execution_Plan.jpg

Does this communicate anything to you?

Robert Werner
Vancouver, BC|||THe information which is sensitive while moving over the individual objects is missing for us, so its hard to tell. This is the estimated execution plan not the actual executed one, but as a rule of thumb you should have a look on the highest cost in the plan as well as actions like table scans. They are often costly if made for huge tables but are on the opposite cheaper then using indexes in smaller tables. There are some other thing you could check like setting the STATISTICS_IO to on or the SHOWPLAN to make the execution plan shippable to other peers with more information.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de