Different execution plans from c# code and MSSQLServerManagementStudio

Hi all,
I've been looking into a strange problem in the last weeks.
We use SQL Server 2005.
We have some code written in C# which goes to the database, executes
some stored procedures and retrieves the results.
Now, the procedures are a little bit strange, as in a procedure we
dynamically create the SELECT statement with different WHERE clauses
based on the parameters we receive and then use exec sp_executesql to
actually run the statement. (I assume that this prevents sql server to
compile the final statements and reuse the compiled execution plan,
right?)
All is well for most of the time. Sometimes though some of the
procedures get very slow.
For example a procedure which would usually run in 100ms now takes up
to 5 seconds. Or one which normally ran in 1 sec, runs more than 30
second.
I started examining with the SQL Server Profiler, and got strange
results:
- I launch the C# code, it calls the SP and the trace shows a Duration
of 5000 ms.
- I copy the statement executed in SQL Management Studio, run from
there and the Duration is 90ms
(Note that when I compared the SQLManagementStudio and C# runs of the
procedures, I used the same parameters).
I configured the profiler to show the execution plans, and the two
plans are different. It is clear that when I run the query from the SQL
management studio, it uses the right indexes, but when running from the
C# code it won't use the good indexes.
Interestingly I can run them alternatively several times (once from SQL
Management Studio, once from C#, again from SQLMS, again from C#,
a.s.o), and the results are unchanged: good performance from SQLMS, bad
performance from C#.
As our procedures generate dynamic sql statements, I assume that no
execution plan is reused here. The two exceutino plans generated are
just plain different.
If I restart the SQL Server, everything goes back to normal: the
procedures run fast from C#. After a day or two the performance problem
pops up in the same place, or in some other (but very similar)
procedure.
If I modify the procedure to include a WITH(Index(...)) hint,
everything is fast :-)
I also qualify the owner of the stored procedure when I call it.
(dbo.sp_ProcName, xxx.sp_AnotherProcName a.s.o).
In order to minimize the effect of the ,net framework classes, I
reproduced the calls using SqlConnection/SqlCommand,
OleDBConnection/OleDBCommand too. One of my colleagues also reproduced
it with code written in VB6 (non .net) (running from a different
machine).
Any idea why the C# call will generate a bad execution plan? And how I
could make sure it generates the good one (except using the
WITH(INDEX()) hints)?
Thank you,
Trucza CsabaHi
> As our procedures generate dynamic sql statements, I assume that no
> execution plan is reused here. The two exceutino plans generated are
> just plain different.
No, SQL Server could resuse an execution plan otherwise we would have seen
RECOMPILE statement in Profiler
Well , there are many scenarios that make hurt perfomance as we know as
"parameter sniffing"
Search on interenet for this tiltle to get more details as well please read
this article may shed some lights on.
http://www.sql-server-performance.com/q&a132.asp
http://www.sql-server-performance.c...cess_tuning.asp
<csaba.trucza@.gmail.com> wrote in message
news:1143715907.378509.198330@.j33g2000cwa.googlegroups.com...
> Hi all,
> I've been looking into a strange problem in the last weeks.
> We use SQL Server 2005.
> We have some code written in C# which goes to the database, executes
> some stored procedures and retrieves the results.
> Now, the procedures are a little bit strange, as in a procedure we
> dynamically create the SELECT statement with different WHERE clauses
> based on the parameters we receive and then use exec sp_executesql to
> actually run the statement. (I assume that this prevents sql server to
> compile the final statements and reuse the compiled execution plan,
> right?)
> All is well for most of the time. Sometimes though some of the
> procedures get very slow.
> For example a procedure which would usually run in 100ms now takes up
> to 5 seconds. Or one which normally ran in 1 sec, runs more than 30
> second.
> I started examining with the SQL Server Profiler, and got strange
> results:
> - I launch the C# code, it calls the SP and the trace shows a Duration
> of 5000 ms.
> - I copy the statement executed in SQL Management Studio, run from
> there and the Duration is 90ms
> (Note that when I compared the SQLManagementStudio and C# runs of the
> procedures, I used the same parameters).
> I configured the profiler to show the execution plans, and the two
> plans are different. It is clear that when I run the query from the SQL
> management studio, it uses the right indexes, but when running from the
> C# code it won't use the good indexes.
> Interestingly I can run them alternatively several times (once from SQL
> Management Studio, once from C#, again from SQLMS, again from C#,
> a.s.o), and the results are unchanged: good performance from SQLMS, bad
> performance from C#.
> As our procedures generate dynamic sql statements, I assume that no
> execution plan is reused here. The two exceutino plans generated are
> just plain different.
> If I restart the SQL Server, everything goes back to normal: the
> procedures run fast from C#. After a day or two the performance problem
> pops up in the same place, or in some other (but very similar)
> procedure.
> If I modify the procedure to include a WITH(Index(...)) hint,
> everything is fast :-)
> I also qualify the owner of the stored procedure when I call it.
> (dbo.sp_ProcName, xxx.sp_AnotherProcName a.s.o).
> In order to minimize the effect of the ,net framework classes, I
> reproduced the calls using SqlConnection/SqlCommand,
> OleDBConnection/OleDBCommand too. One of my colleagues also reproduced
> it with code written in VB6 (non .net) (running from a different
> machine).
> Any idea why the C# call will generate a bad execution plan? And how I
> could make sure it generates the good one (except using the
> WITH(INDEX()) hints)?
> Thank you,
> Trucza Csaba
>|||Hi Uri,
Uri wrote:
> No, SQL Server could resuse an execution plan otherwise we would have seen
> RECOMPILE statement in Profiler
That SQL Server reuses the execution plan of dynamic statements came to
me as a major surprise. Pleasant one.
I'm still confused though about why the execution plan differs when I
execute the same stored procedure with the same parameters from C# code
and from SQL Management Studio.
Cheers,
Csaba|||> That SQL Server reuses the execution plan of dynamic statements came to
> me as a major surprise. Pleasant one.
But not always the best thing to to. In most cases, you will get an "exact t
ext" matching of the
plan. If you search for different names, for example, you will have differen
t plans for what in the
application is the same query. I've seen cases when the customer had 10,000
plans for the same query
in the plan cache. You can identify this in syscacheobjects by checking for
"AdHoc" object types.

> I'm still confused though about why the execution plan differs when I
> execute the same stored procedure with the same parameters from C# code
> and from SQL Management Studio.
One possible reason can be different SET options.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<csaba.trucza@.gmail.com> wrote in message
news:1143803884.817874.61660@.g10g2000cwb.googlegroups.com...
> Hi Uri,
> Uri wrote:
> That SQL Server reuses the execution plan of dynamic statements came to
> me as a major surprise. Pleasant one.
> I'm still confused though about why the execution plan differs when I
> execute the same stored procedure with the same parameters from C# code
> and from SQL Management Studio.
> Cheers,
> Csaba
>|||HI Tibor,
You were right. The ARITHABORT was the culprit. By default SMS has this
setting ON. The connections coming from ADO.NET did not set it
explicitly, so they got it from the database setting (I think), which
was off.
(And having this at hand, I googled +ARITHABORT +"execution plan" and
it seems I'm not alone with this problem).
This explains why the execution plan was not reused.
What still puzzles me is why the two generated execution plans are so
much different.