Re: slowing/halting stored procedure from ado.net

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

"William Vaughn [MVP]" wrote:

Erratic performance of any query is usually due to something changing. For
example:
a.. A query plan that does not match the operations being requested.
This is caused by the system generating a QP based on a set of a
parameters that might work for the first invocation, but the next
execution (or the problem execution) the cached plan does not match the
operations dictated by the subsequent parameters or the state of the
statistics (what's in the DB). Best idea? Simplify the procedure (break it
into smaller pieces) whose QPs are not dependent on the vagarities of the
parameters--or don't accept parameters that break the QP.
b.. The query (when it slows down) might be blocked by some other
operation that's holding a resource that's competing or colliding with the
resources needed by the SP.
c.. The system might be busy when the query is being run. For example,
it might be loaded up with another application that flushes the data
cache, competes for disk IO, RAM or CPU time. Examples? A print job, a
reporting services job, a backup, almost anything that steals
resources--including your own application and another connection.

--
__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhiker’s Guide to Visual Studio and SQL Server (7th Edition)
__________________________________________________________________________
__________________
"MarcelG" <MarcelG@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:A5E650CF-C2BE-4E15-9279-0B23A5540B6B@xxxxxxxxxxxxxxxx
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

  • Re: Stored procedure works in sql, not in .net
    ... Thanks for replying (yes i was calling my sp from code ... "Dan Bass" wrote: ... > I'm assuming you're not using the SQL in your code-behind to do this, ... > If you could post your code that you're using to access the stored procedure ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: WaitForSingleObject Event Thrown from Sql Server
    ... > I have a Widnows Service that creates a system event that I would like ... > to have a Sql Server stored procedure fire when the stored procedure is ... > having is actually throwing the event from inside a Sql Server Stored ... Calling an extended stored procedure from SQL is ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: Help in writing a trigger
    ... are calling a stored procedure. ... They are calling xp_sendmail, which is deprecated, and has been ... Links for SQL Server Books Online: ...
    (microsoft.public.sqlserver.programming)
  • Re: Call SQL Stored procedure with Bit datatype
    ... > I am having difficulty calling a stored procedure from VB.Net that has a Bit ... The SQL SP ... Also, if you have access to it, you can always run SQL Profiler and see ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Views vs Stored Procedures, whats the difference?
    ... I hope you are not suggesting you embed SQL queries into the application? ... A stored procedure logic will be exactly as fast as the algorithm you ... I understant that SQL Server supports hints. ... implementations (nestedloop, merge, hash, ..) on decent sized tables, then ...
    (comp.databases.ms-sqlserver)
Loading