|
|
Posted by Erland Sommarskog on 05/03/06 00:28
Dimitri Furman (dfurman@cloud99.net) writes:
> 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.
Yeah, but it's easier to change the setting from QA.
Then again, it's a good habit to always turn on ARITHABORT from ADO,
as the setting must be on if you work with indexed views and indexed
computed columns.
> 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.)
--
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]
|