I am trying to solve a problem with a function in a DB I have
inherited. The database is running on single SQL Server 2000 SP4 on
Windows 2003 Enterprise as a VM.

The function takes a single parameter indicating the record type. It
then selects all of the records from a table (joined with several
others) which are of the specified type. On a newly restarted
database I run the query through Query Analyser and get the following
results:

Type 1 records, 5000 rows returned in 3-4 seconds.
Type 6 records, 160 rows returned in 3-4 seconds.

Not the fastest but acceptable.

The database is left to run for a while (it is serving data to an
ASP.NET web site). After several hours the performance profile
changes to:

Type 1 records, 5000 rows returned in 3-4 seconds.
Type 6 records, 160 rows returned in 31-34 seconds.

Restarting the services has the desired effect of bringing the
executions back in line with the original results. Obviously, this is
not a desirable solution.

The time taken for the performance to degrade is variable. I have
seen it after 3 hours and also after 6 hours. This problem does not
present itself in our development or test environments, only on the
live server.

Our main backups are done over night with transaction logs backed up
hourly.

I have googled and tried the top few suggestions but can not see
anything wrong with the system.

What is really baffling me is that the performance for type 6 degrades
but not type 1. Does anyone have any advice on what I can try?

Thanks in advance,
Mark
--
|\ _,,,---,,_ A picture used to be worth a
ZZZzzz /,`.-'`' -. ;-;;, thousand words - then along
|,4- ) )-,_. ,\ ( `'-' came television!
'---''(_/--' `-'\_)

Mark Stevens (mark at thepcsite fullstop co fullstop uk)

This message is provided "as is".

It could be a case of parameter sniffing, where the query plan gets
optimized based on a parameter that does not produce the best plan.

You can assign the parameter to a local variable and use the local variable
in the query (as local variables cannot be sniffed).

See for more details here:
http://blogs.msdn.com/khen1234/archi...02/424228.aspx

HTH,

Plamen Ratchev
http://www.SQLStudio.com

Hmm. I might ask if you have some kind of pathological condition,
where the app might generate a lot of connections that leak and stay
active, updating the type6 rows behind the scenes, or even just
holding a large number of shared locks.

Running sp_who2 will show you the connections, you may have to run
profiler to see if someone is hammering those rows.

Josh



On Tue, 15 Jul 2008 19:39:48 +0100, Mark Stevens <nevyn@nospam.nospam>
wrote:

>I am trying to solve a problem with a function in a DB I have
>inherited. The database is running on single SQL Server 2000 SP4 on
>Windows 2003 Enterprise as a VM.
>
>The function takes a single parameter indicating the record type. It
>then selects all of the records from a table (joined with several
>others) which are of the specified type. On a newly restarted
>database I run the query through Query Analyser and get the following
>results:
>
>Type 1 records, 5000 rows returned in 3-4 seconds.
>Type 6 records, 160 rows returned in 3-4 seconds.
>
>Not the fastest but acceptable.
>
>The database is left to run for a while (it is serving data to an
>ASP.NET web site). After several hours the performance profile
>changes to:
>
>Type 1 records, 5000 rows returned in 3-4 seconds.
>Type 6 records, 160 rows returned in 31-34 seconds.
>
>Restarting the services has the desired effect of bringing the
>executions back in line with the original results. Obviously, this is
>not a desirable solution.
>
>The time taken for the performance to degrade is variable. I have
>seen it after 3 hours and also after 6 hours. This problem does not
>present itself in our development or test environments, only on the
>live server.
>
>Our main backups are done over night with transaction logs backed up
>hourly.
>
>I have googled and tried the top few suggestions but can not see
>anything wrong with the system.
>
>What is really baffling me is that the performance for type 6 degrades
>but not type 1. Does anyone have any advice on what I can try?
>
>Thanks in advance,
>Mark

Agreed, it sounds like a classic case of parameter sniffing.

--
Andrew J. Kelly SQL MVP
Solid Quality Mentors


"Plamen Ratchev" <Plamen@SQLStudio.com> wrote in message
news:75D8EA89-2328-4253-A046-BF26532D3B21@microsoft.com...
> It could be a case of parameter sniffing, where the query plan gets
> optimized based on a parameter that does not produce the best plan.
>
> You can assign the parameter to a local variable and use the local
> variable in the query (as local variables cannot be sniffed).
>
> See for more details here:
> http://blogs.msdn.com/khen1234/archi...02/424228.aspx
>
> HTH,
>
> Plamen Ratchev
> http://www.SQLStudio.com
>

Thanks for the advice, I'll give it a go.

Cheers,
Mark

On Tue, 15 Jul 2008 16:53:19 -0400, "Plamen Ratchev"
<Plamen@SQLStudio.com> wrote:

>It could be a case of parameter sniffing, where the query plan gets
>optimized based on a parameter that does not produce the best plan.
>
>You can assign the parameter to a local variable and use the local variable
>in the query (as local variables cannot be sniffed).
--
|\ _,,,---,,_ A picture used to be worth a
ZZZzzz /,`.-'`' -. ;-;;, thousand words - then along
|,4- ) )-,_. ,\ ( `'-' came television!
'---''(_/--' `-'\_)

Mark Stevens (mark at thepcsite fullstop co fullstop uk)

This message is provided "as is".