|
|
Posted by Erland Sommarskog on 10/02/96 11:46
Dimitri Furman (dfurman@cloud99.net) writes:
> There is a relatively complex stored procedure that usually completes in
> less than 20 seconds. Occasionally it times out after 180 seconds. The
> SP is called via ADO 2.8, using adCmdStoredProc command type. If I use
> Profiler to capture the EXEC that ADO sends to run the procedure, and
> run that from QA, the procedure completes in less than 20 seconds as it
> should.
If you in this situation issue SET ARITHABORT OFF from QA, what happens?
This may seem like a crazy thing, but ADO connects by default with
ARITHABORT OFF, whereas QA swears by a default of ON. This is one of the
options that are saved with the query plan, so different settings, different
query plans. (But this does not mean that any or either setting affects
the performance. It's just that QA gets a fresh deal.)
> The procedure is created WITH RECOMPILE.
>...
> The only thing that fixes it, at least for a day or two, is DBCC
> FREEPROCCACHE. So it appears that a bad plan is somehow stuck in memory
Nah, I would rather look for something that is being invoked by the
procedure: a trigger, a used-defined function or an inner stored procedure.
> Other than scheduling the DBCC call to run every night, is there anything
> else I could try to get this resolved? Thanks.
You need to track down exactly which piece of codes that all of a sudden
takes a long time. Profiler is good for this, although it may be trouble
some if the problem is in a UDF.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|