RE: slowing/halting stored procedure from ado.net

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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" wrote:

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

"Nick" wrote:

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

"MarcelG" wrote:

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??

.

Relevant Pages

  • slowing/halting stored procedure from ado.net
    ... When I execute the stored procedure from within SQL Server Management Studio ... it always executes fast, ... When the slowdown appears, restarting the application does not help. ...
    (microsoft.public.dotnet.framework.adonet)
  • SqlDataAdapter.Fill returns results of previous command
    ... This code executes successfully at certain points in the application, ... I don't know what the cause of this exception is yet, ... stored procedure was indeed executed on the database. ... The trace shows that the last action on the db was the stored ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Help Isolating Data Access
    ... > I've set up a stored procedure that first queries the table for a record ... > The process that executes this stored procedure runs on multiple threads ... Is there a design pattern for ...
    (microsoft.public.dotnet.general)
  • Re: Tracing user & permissions (triggers and stored p)
    ... To solve my problem I chosed to configure the SQL ... > from xp_cmdshell executes under the security context of the SQL Server ... >> sql Trigger executes a Stored Procedure passing it some variables. ...
    (microsoft.public.sqlserver.security)
  • Re: Stored Procedure slow when called from Crystal Reports
    ... Access executes ... that statement to my stored procedure that is executed by the report, ... report runs about 3 times faster - almost as fast as the SP on its own. ... Yes, it is slow the first time, but that's before ...
    (microsoft.public.vb.crystal)