470,638 Members | 1,642 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,638 developers. It's quick & easy.

exec sp_executesql vs. sp_executesql and performance

This is a odd problem where a bad plan was chosen again and again, but
then not.

Using the profiler, I identified an application-issued statement that
performed poorly. It took this form:

exec sp_executesql N'SELECT col1, col2 FROM t1 WHERE (t2= @Parm1)',
N'@Parm1 int', @Parm1 = 8609

t2 is a foreign key column, and is indexed.

I took the statement into query analyzer and executed it there. The
query plan showed that it was doing a scan of the primary key index,
which is clustered. That's a bad choice.

I then fiddled with it to see what would result in a good plan.

1) I changed it to hard code the query value (but with the parm
definition still in place. )
It performed well, using the correct index.
Here's how it looked.
exec sp_executesql N'SELECT cbord.cbo1013p_AZItemElement.AZEl_Intid AS
[Oid], cbord.cbo1013p_AZItemElement.incomplete_flag AS [IsIncomplete],
cbord.cbo1013p_AZItemElement.traceflag AS [IsTraceAmount],
cbord.cbo1013p_AZItemElement.standardqty AS [StandardAmount],
cbord.cbo1013p_AZItemElement.Uitem_intid AS [NutritionItemOid],
cbord.cbo1013p_AZItemElement.AZeldef_intid AS [AnalysisElementOid] FROM
cbord.cbo1013p_AZItemElement WHERE (Uitem_intid= 8609)', N'@Parm1 int',
@Parm1 = 8609

After doing this, re-executing the original form still gave bad
results.

2) I restored the use of the parm, but removed the 'exec' from the
start.
It performed well.

After that (surprise!) it also performed well in the original form.

What's going on here?

Jul 23 '05 #1
3 10260
elRoyFlynn (li*@twcny.rr.com) writes:
t2 is a foreign key column, and is indexed.

I took the statement into query analyzer and executed it there. The
query plan showed that it was doing a scan of the primary key index,
which is clustered. That's a bad choice.
Sometimes it is, sometimes it's not. This is a delicate choice that
the optimizer have to make. Non-clustered index + bookmark lookup, or
clustered index scan? The first strategy fantastic if there are only
a few hits, but disastrous if you hit, say, 30% of the rows. Many page
will be accessed more than once, and it will be a lot slower than a CI
scan.
2) I restored the use of the parm, but removed the 'exec' from the
start.
It performed well.

After that (surprise!) it also performed well in the original form.


Probably parameter sniffing. SQL Server caches the query plan for the
query, and the cached plan is built from the parameter value that
query first was run for. That value may have been handled best with
a CI scan.

But it might also be that the statistics were poor initially, and caused
SQL Server to make an incorrect estimate. But SQL Server has auto-
statistics, so it could be that statistics were updated, and the plan
was flushed, and a new plan built.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #2
In this case, the "bad plan" clearly is: a 6-second response time vs.
a sub-second response when the "best" plan is used.

Problem is, the application generated this query in the "exec sp..."
form hundreds of times, getting the bad result each time. It was while
the app was still running that I worked in query analyzer. I executed
the problem sql multiple times, duplicating the bad result, before
trying it the other way. The first time I did it the other way, it
worked well, which also immediately fixed the application. Coincidence
, unrelated to the different execution form, that just at that moment
mss figured out that the other plan was better? I'm skeptical. I
think that something about 'exec sp_.." vs. plain "sp_..." had an
unintended effect.

But thanks for the response, I'll think about it.

Jul 23 '05 #3
elRoyFlynn (li*@twcny.rr.com) writes:
In this case, the "bad plan" clearly is: a 6-second response time vs.
a sub-second response when the "best" plan is used.
It should be admitted that this is quite common. The optimizer seems to
be overly conservative with regards to non-clustered indexes.
Coincidence , unrelated to the different execution form, that just at
that moment mss figured out that the other plan was better? I'm
skeptical. I think that something about 'exec sp_.." vs. plain "sp_..."
had an unintended effect.


It could be that the misisng "exec" triggered a recompile of the query,
but from what I know about how the cache works, I can't really see that
it would matter.

What could matter, though, is whether you changed somehting inside
the query. With regards to single queries, the cache is both case-
and space-sensitive. (But the part "EXEC sp_executesql" is not in
the cache, only the first argument to sp_executesql is.)

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by Evgeny Gopengauz | last post: by
6 posts views Thread by Dipak Patel | last post: by
3 posts views Thread by LineVoltageHalogen | last post: by
26 posts views Thread by warth33 | last post: by
31 posts views Thread by eliben | last post: by
1 post views Thread by Korara | last post: by
???
1 post views Thread by Stoney L | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.