SQL Server 2005: functions become slow after a while

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=E9

Re: SQL Server 2005: functions become slow after a while

On Thu, 17 Apr 2008 02:29:07 -0700 (PDT), René
wrote:

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é

Re: SQL Server 2005: functions become slow after a while

> 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 (=3Drecompile) 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=E9

Re: SQL Server 2005: functions become slow after a while

This looks like parameter sniffing. Read the following articles on possible
solutions:
http://blogs.msdn.com/khen1234/archive/2005/06/02/424228.asp x
http://www.sqlmag.com/Article/ArticleID/94369/sql_server_943 69.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

Re: SQL Server 2005: functions become slow after a while

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.

Re: SQL Server 2005: functions become slow after a while

On Thu, 17 Apr 2008 07:38:16 -0700 (PDT), René
wrote:

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é