SQL Server 2005: functions become slow after a while

[=?ISO-8859-1?Q?Ren=E9?=]

Hi,

I'm having serious issues with our user defined functions. They get
very slow after a while. The functions I use have several IF-branches
which check input parameters. Always exactly one branch will be
executed.
Normally, the execution time of my functions is something like 300ms.
After a while though, it climbs up to 20 seconds (!!).
The interesting thing about it is now: if I just alter the function by
adding a blank somewhere it will be fast again. What the hell is going
on here?

To me it seems that 2005 is optimizing the function incorrectly. It is
executed on very different result sets (some are extremly small, some
are extensivly large). I can imagine that for one case hashed indexing
is better and for the other one nested loops. So maybe it sticks to
one ooptimization after a while? The problem is that I cannot really
prove it because I cannot force the behavior. It occurs suddenly it
won't go away until I perform the "add blank" action.

The server is a 8 processor DB cluster with 12GB of RAM, all SPs
installed (SP2 for 2005).

Any hints what to do?

René

[Tom van Stiphout]

On Thu, 17 Apr 2008 02:29:07 -0700 (PDT), René
<re**********@googlemail.comwrote:

sp_recompile is typically used to get a new execution plan. On very
dynamic systems it may have to be run daily. On others still not a bad
idea to run it once every week.

-Tom.

>Hi,

I'm having serious issues with our user defined functions. They get
very slow after a while. The functions I use have several IF-branches
which check input parameters. Always exactly one branch will be
executed.
Normally, the execution time of my functions is something like 300ms.
After a while though, it climbs up to 20 seconds (!!).
The interesting thing about it is now: if I just alter the function by
adding a blank somewhere it will be fast again. What the hell is going
on here?

To me it seems that 2005 is optimizing the function incorrectly. It is
executed on very different result sets (some are extremly small, some
are extensivly large). I can imagine that for one case hashed indexing
is better and for the other one nested loops. So maybe it sticks to
one ooptimization after a while? The problem is that I cannot really
prove it because I cannot force the behavior. It occurs suddenly it
won't go away until I perform the "add blank" action.

The server is a 8 processor DB cluster with 12GB of RAM, all SPs
installed (SP2 for 2005).

Any hints what to do?

René

[=?ISO-8859-1?Q?Ren=E9?=]

sp_recompile is typically used to get a new execution plan. On very

dynamic systems it may have to be run daily. On others still not a bad
idea to run it once every week.

The problem is that in my case the SP/function is fast at 8AM and slow
at 9AM.Then I add the blank (=recompile) and then it may work for a
day or maybe only for 30 minutes...I really cannot do a recompile
every 30 minutes, can I?

René

[Plamen Ratchev]

This looks like parameter sniffing. Read the following articles on possible
solutions:
http://blogs.msdn.com/khen1234/archi...02/424228.aspx
http://www.sqlmag.com/Article/Articl...ver_94369.html

Using OPTION(RECOMPILE) on the particular query will cause only that query
to be recompiled instead of all queries in the function.

HTH,

Plamen Ratchev
http://www.SQLStudio.com

[--CELKO--]

Look at the WITH RECOMPILE option for stored procedures. In T-SQL,
the first time a procedure is compiled, it looks at the parameters and
assumes that they are gospel; it optimizes for those values. DB2 and
other SQL products you might have worked with can store many different
execution plans and the pick the best one for each call to the
procedure.

Another piece of advice is to avoid user defined functions
altogether. This is proprietary syntax, a return to procedural
coding, and you can get the same things with pure SQL most of the
time.

[Tom van Stiphout]

On Thu, 17 Apr 2008 07:38:16 -0700 (PDT), René
<re**********@googlemail.comwrote:

You left out that gem. In one hour you go from msecs to dozens of
seconds? That's just wrong.
Look at an execution plan when it's fast, and another one when it's
slow.
Is your db very dynamic? For example if you have a big data load at
08:30, indeed the execution plan wouldn't be much good at 9:00.

-Tom.

>sp_recompile is typically used to get a new execution plan. On very
dynamic systems it may have to be run daily. On others still not a bad
idea to run it once every week.

The problem is that in my case the SP/function is fast at 8AM and slow
at 9AM.Then I add the blank (=recompile) and then it may work for a
day or maybe only for 30 minutes...I really cannot do a recompile
every 30 minutes, can I?

René