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?elRoyFlynn (lit@.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, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||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.|||elRoyFlynn (lit@.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, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.aspsql