|
|
Posted by Dimitri Furman on 05/03/06 05:59
On May 02 2006, 05:28 pm, Erland Sommarskog <esquel@sommarskog.se> wrote
in news:Xns97B7EED824620Yazorman@127.0.0.1:
> Dimitri Furman (dfurman@cloud99.net) writes:
>> 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?
>
> Correct, that appears to be a missing feature. And neither can you say
> "sp_recompile table_udf". But if there is a table that is referred to
> by the UDF and not the surrounding SP, you can say "sp_recompile" on
> that table next time this happens. If performance then comes back to
> normal, then you know that the UDF is the culprit.
>
> A possible trick to force a recompile each time is to add this line
> first in the procedure:
>
> SET ANSI_NULLS OFF
>
> Since this setting is saved with the function, it's a dummy operation,
> but it nevertheless causes a recompilation of a stored procedure. I
> have not verified this with a table function. (You could still run
> into trouble with indexed views if you do this.)
Well, of course this function uses not one but two indexed views...
What if I set ANSI_NULL_DFLT_ON to OFF within the function? If I am reading
KB243586 correctly, this should cause a recompile, and I believe it won't
interfere with indexed views.
If that doesn't work, I think I can create a dummy table, reference it in
the function, and call sp_recompile on that table prior to calling the
procedure.
--
remove a 9 to reply by email
Navigation:
[Reply to this message]
|