MS SQL Server - Touching UDF Fixes Performance Problem

Author

Touching UDF Fixes Performance Problem

David Sullivan

2006-03-08, 8:23 pm

Hi all,
I would like to relate a strange performance problem we have had lately. I
would be very interested to know whether anyone has encountered a similar
situation.

I work in the technology area of a large enterprise. We host a public-facing
website that uses a SQL Server 2000 back-end. Full @@version dump follows:

Microsoft SQL Server 2000 - 8.00.818 (Intel X86) May 31 2003 16:08:15
Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on Windows
NT 5.2 (Build 3790: Service Pack 1)

Hardware Characteristics:
IBM X336
8GB RAM
Disk: 50GB LUN on Hitachi 9585V SAN
Windows 2003 Enterprise Edition SP1
Hyperthreading ON

The database in question makes heavy use of table-valued functions.
Recently, the performance of the most frequently used function degraded
sharply. An execution of the function would easily take over 30 to 40 seconds
even when the server was NOT under load. Normally the function would execute
within 2 to 10 seconds depending on the parameters.

We took a backup of the production database and mounted it on a staging
server (identical configuration, memory, disk etc) to conduct some tests. We
found that we could reproduce the problem at will on the staging server. We
initially thought that there may be some change introduced that was causing a
non-optimal query plan to be cached. However, checking of indexes and
statistics showed that no change had been introduced since the performance
problem began.

Here comes the weird part - we were focusing in on one part of the function
with a view to changing it. However, at one stage the DBA looking at the
issue did an ALTER to the function without actually changing the SQL in the
function and then executed the function again. Execution time dropped to 2
seconds (the previous execution had taken 39 seconds). Thereafter it took 2
seconds to execute for hundreds of re-tries. Just to be sure we were not
seeing things, we restored the database again, restarted the SQL service and
did the same sequence of actions:
1) Execute the function a number of times (takes 39 seconds consistently)
2) ALTER the function with no code change
3) Execute the function (takes 2 seconds - first try)

Note that cached plans cannot be a factor here as the behaviour is evident
immediately after a restore and a restart - when there will not be a cached
plan.

Now a question for those with a deeper knowledge of SQL Server than I - Are
functions and stored-procs etc converted to an intermediate format (possibly
binary) when the text of the function or SP is saved - ready for conversion
to an in-memory compiled-plan and execution plan?
This behaviour seems to suggest this - as I assume that this intermediate
binary version of the script would be re-built if the function is ALTERed.
If there is such an intermediate format, it appears to become corrupted
somehow.

If anyone has encountered a similar problem I would be very interested to
hear about it. I've searched the Knowledge Base for any articles relating to
a problem like this and came up empty. If anyone is aware of a KB article
that touches on this I would be grateful for an article number.

Regards,
David Sullivan.

Conor Cunningham [MS]

2006-03-14, 3:23 am

I think that the issue has to do with set options when the original function
was created. when you touch it, it resets this and the default is perhaps
different in 2005.

(this is from memory and may be wrong - however, this may save you some time
and if so, I hope it is useful and can avoid you wasting time).

"David Sullivan" < DavidSullivan@discus
sions.microsoft.com> wrote in message
news:A25C9637-88F7-4C63-943C- 816F98259C72@microso
ft.com...
> Hi all,
> I would like to relate a strange performance problem we have had lately. I
> would be very interested to know whether anyone has encountered a similar
> situation.
>
> I work in the technology area of a large enterprise. We host a
> public-facing
> website that uses a SQL Server 2000 back-end. Full @@version dump follows:
>
> Microsoft SQL Server 2000 - 8.00.818 (Intel X86) May 31 2003 16:08:15
> Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on
> Windows
> NT 5.2 (Build 3790: Service Pack 1)
>
> hardware Characteristics:
> IBM X336
> 8GB RAM
> Disk: 50GB LUN on Hitachi 9585V SAN
> Windows 2003 Enterprise Edition SP1
> Hyperthreading ON
>
> The database in question makes heavy use of table-valued functions.
> Recently, the performance of the most frequently used function degraded
> sharply. An execution of the function would easily take over 30 to 40
> seconds
> even when the server was NOT under load. Normally the function would
> execute
> within 2 to 10 seconds depending on the parameters.
>
> We took a backup of the production database and mounted it on a staging
> server (identical configuration, memory, disk etc) to conduct some tests.
> We
> found that we could reproduce the problem at will on the staging server.
> We
> initially thought that there may be some change introduced that was
> causing a
> non-optimal query plan to be cached. However, checking of indexes and
> statistics showed that no change had been introduced since the performance
> problem began.
>
> Here comes the weird part - we were focusing in on one part of the
> function
> with a view to changing it. However, at one stage the DBA looking at the
> issue did an ALTER to the function without actually changing the SQL in
> the
> function and then executed the function again. Execution time dropped to 2
> seconds (the previous execution had taken 39 seconds). Thereafter it took
> 2
> seconds to execute for hundreds of re-tries. Just to be sure we were not
> seeing things, we restored the database again, restarted the SQL service
> and
> did the same sequence of actions:
> 1) Execute the function a number of times (takes 39 seconds consistently)
> 2) ALTER the function with no code change
> 3) Execute the function (takes 2 seconds - first try)
>
> Note that cached plans cannot be a factor here as the behaviour is evident
> immediately after a restore and a restart - when there will not be a
> cached
> plan.
>
> Now a question for those with a deeper knowledge of SQL Server than I -
> Are
> functions and stored-procs etc converted to an intermediate format
> (possibly
> binary) when the text of the function or SP is saved - ready for
> conversion
> to an in-memory compiled-plan and execution plan?
> This behaviour seems to suggest this - as I assume that this intermediate
> binary version of the script would be re-built if the function is ALTERed.
> If there is such an intermediate format, it appears to become corrupted
> somehow.
>
> If anyone has encountered a similar problem I would be very interested to
> hear about it. I've searched the Knowledge Base for any articles relating
> to
> a problem like this and came up empty. If anyone is aware of a KB article
> that touches on this I would be grateful for an article number.
>
> Regards,
> David Sullivan.
>

Doug

2006-03-14, 8:23 pm

David,

You've got the gist of it. The way I think about it, when you save a
sproc or view, it does a "pseudo compile" against the code, and saves
off the "plan" of how to go about executing it.

Sometimes data changes, or indexes change, or the number of records
change, and all of a sudden the old plan isn't very efficient.
So, we force a recompile, and the engine comes up wiht a better plan.

The engine does its best. But if you really think about what it is
trying to do, sometimes it needs a little help, or hints. Sometimes you
can encourage it to use specific indexes to speed things up, or slow
them down.

Great catch on seeing what fixed it. Most do not have the discipline to
keep track of what changed.
if your app is that big, odds are performance tuning of the code could
improve speed even more, if it is an issue for you group.
Regards,
doug

Sponsored Links