|
|
Posted by Dimitri Furman on 05/02/06 23:14
On May 02 2006, 11:36 am, Erland Sommarskog <esquel@sommarskog.se> wrote
in news:Xns97B7B3270C20Yazorman@127.0.0.1:
> 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.)
Well, I had to do the DBCC thing so I can't repro this right now, but will
check this the next time it happens. If this turns out to be the cause, I
guess I'll just need to set ARITHABORT to ON from ADO on the same
connection prior to running the proc, right? At least this will make the
behavior consistent between the app and QA, even though it probably won't
fix the issue.
>> 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.
No triggers or inner procedures, but there are a few UDFs, one of which (a
fairly complex multi-statement table-valued one) is suspicious. So is it
possible that there is a separate plan for that function, which does not
get recompiled even though the calling proc is WITH RECOMPILE? If that's
the case, how can I force recompilation for a UDF? There is no WITH
RECOMPILE for functions, is there?
>> 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.
--
(remove a 9 to reply by email)
Navigation:
[Reply to this message]
|