ADO.NET query execution much slower than SQL Management Studio

[Database Administrator]

I have a stored procedure that when called from SQL 2005 Management
Studio
takes less than one second to execute. The same SP, when called
from .NET
code takes about 13 seconds.

I have tried using a SqlDataReader and a DataAdapter
to retrieve the data, but both are equally slow.


Profiler Traces (ON THE SAME QUERY):

Running From .Net App:
Reads: 7449000
Duration: 11200

Running From SQL Management Studio Query Window:
Reads: 45505
Duration 224

When I recompile the SP it fixes the problem only to come back the
next day to the same problem
I can add a sp_recompile before it executes the SP everytime but that
is not a solution!
I think it has something to do with the .Net SQL Provider
(It is not a problem with SET NOCOUNT or something like that because
the query difference is 11sec to 0.2secs)

Has anybody seen this problem?

[Database Administrator]

eliperp@gmail.com wrote:
> I have a stored procedure that when called from SQL 2005 Management
> Studio
> takes less than one second to execute. The same SP, when called
> from .NET
> code takes about 13 seconds.
>
> I have tried using a SqlDataReader and a DataAdapter
> to retrieve the data, but both are equally slow.
>
>
> Profiler Traces (ON THE SAME QUERY):
>
> Running From .Net App:
> Reads: 7449000
> Duration: 11200
>
> Running From SQL Management Studio Query Window:
> Reads: 45505
> Duration 224
>
> When I recompile the SP it fixes the problem only to come back the
> next day to the same problem
> I can add a sp_recompile before it executes the SP everytime but that
> is not a solution!
> I think it has something to do with the .Net SQL Provider
> (It is not a problem with SET NOCOUNT or something like that because
> the query difference is 11sec to 0.2secs)
>
> Has anybody seen this problem?
>
How does the code look that calls and execute the proc from ADO.NET?

Niels

[Database Administrator]

(eliperp@gmail.com) writes:
> I have a stored procedure that when called from SQL 2005 Management
> Studio takes less than one second to execute. The same SP, when called
> from .NET code takes about 13 seconds.
>
> I have tried using a SqlDataReader and a DataAdapter
> to retrieve the data, but both are equally slow.
>...
> When I recompile the SP it fixes the problem only to come back the
> next day to the same problem
> I can add a sp_recompile before it executes the SP everytime but that
> is not a solution!
> I think it has something to do with the .Net SQL Provider

No, it pleads innosence. After all, SSMS uses it too!

This is an issue with two things:
1) Different default settings in the client API and in SSMS. Next the
query runs slow in the app, run this command in SSMS first:
SET ARITHABORT OFF - my prediction is that the query will run slow
in SSMS now as well. ARITHABORT is one of these settings that is an
attribute of the query plan.

2) Parameter sniffing. When SQL Server runs a query the first time, it
uses the input parameters as guidance to build the plan. If the
first-time run is with atypical values, you will end up with a bad
plan.

It appears in your case that the good plan is flushed for some reason,
and righty after that the query is run with different parameters. Maybe
there are some nightly task going on?



--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Database Administrator]

Thanks Erland,
We got the problem again and tried using SET ARITHABORT OFF in SSMS -
and it did indeed run slowly.

The next questions are:
1) Why is this happening, and
2) Can we just add SET ARITHABORT ON to the top of the stored
procedure to fix this?

[Database Administrator]

Peril49 (eliperp@gmail.com) writes:
> We got the problem again and tried using SET ARITHABORT OFF in SSMS -
> and it did indeed run slowly.

Then you can examine the query plan, and compare it to the good plan.
Pay particular attention to the estimates, as that may give a clue to
under which presumptions the plan was built.

> The next questions are:
> 1) Why is this happening, and

Presumably becuase the plan is flushed at some point, and the next time
the procedure is run after that, it is run with a different set of
parameters. Exactly what this may be, I don't know, but maybe the procedure
is run in the wee mornings to load all data, and this happens just after
the nightly maintenance job.

See also my previous post about "parameter sniffing" or Google this
expression for more extensive write-ups on the topic.

> 2) Can we just add SET ARITHABORT ON to the top of the stored
> procedure to fix this?

No, that would not help. My guess is that this would give the effect that
the procedure would run slow in SSMS as well, even with ARITHABORT on. It's
not the setting of ARITHABORT that matters, it the value of the parameters
when the procedure is compiled.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx