slowing/halting stored procedure from ado.net

[MarcelG]

Hi,
I've strange problem with an application developed with VB.NET 2008/ADO.NET
framework target 2.0 and SQL Server 2005.
Sometimes a certain stored procedure wich normally executes in seconds,
suddenly takes forever (15minutes plus).
When I execute the stored procedure from within SQL Server Management Studio
it always executes fast (42000+ records in 1 second), even when from ADO.NET
it takes forever.
When the slowdown appears, restarting the application does not help.
When I do modify/execute in man.studio, the slowdown goes away, and
application does it normal quick response.
Activity monitor show no blocking other queries.

The stored procedure is called with a command object and an dataadapter
filling a dataset. When I break the application in visual studio, it breaks
on the da.fill(ds) line.
I cannot find any pattern in when the slowdown starts, sometime once a
week, once a day or 3 weeks without a problem.
Records added to the table are about 45 records per day (mean).

Can anyone shed some light on this problem??

 

[MarcelG]

William,

Thanks for your reply, but at the time when calling from the application
(through ado.net), calling the same stored procedure from SQL Management
Studio goes without any slowdown, subzero performance.
There was/is no big job running on the sql server, nothing blocking etc.
Would a query plan for calling from ado.net be different than from calling
it from SQL Management Studio?

BTW:loved your book!

Marcel

 

[Cor Ligthert[MVP]]

Marcel,

There is no "SqlClient.SqlTransaction" involved?

Just a gues

Cor

 

[MarcelG]

No, there is no transaction in the context.
The stored procedure produces a list of client names who placed an order in
an order entry system.

 

[Nick]

Hold sql profiler agaist it, that may shed light on it.

 

[MarcelG]

Running sql profiler in a production environment is difficult.
And, running the stored procedure from within SQL Management Studio always
runs fast.

 

[Nick]

I agree it's not without risk (mainly only of performance) but in my
experience it can be done (I do it myself as a last resort) and often does
shed light on what's going on. Filter as much as you can before you run the
trace.

 

[MarcelG]

I will look into it when it happens.
I will also enable debugging into the dotnet framework, maybe that will also
shed some light.

 

[Cor Ligthert[MVP]]

Marcel,

Everybody is guessing, is showing "some" code maybe an idea, by instance the
way you do the way you connect, the fill and the error handling?

Cor