SQL Performance

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Mark Stevens - 15 Jul 2008 19:39 GMT

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

Signature

|\ _,,,---,,_ 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".

Reply to this Message

Plamen Ratchev - 15 Jul 2008 21:53 GMT

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/archive/2005/06/02/424228.aspx

HTH,

Plamen Ratchev
http://www.SQLStudio.com

Reply to this Message

Andrew J. Kelly - 16 Jul 2008 03:02 GMT

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

Signature

Andrew J. Kelly SQL MVP
Solid Quality Mentors

> 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.
[quoted text clipped - 9 lines]
> Plamen Ratchev
> http://www.SQLStudio.com

Reply to this Message

Mark Stevens - 16 Jul 2008 19:28 GMT

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

Cheers,
Mark

>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).

Signature

Reply to this Message

JXStern - 16 Jul 2008 02:44 GMT

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

>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
[quoted text clipped - 38 lines]
>Thanks in advance,
>Mark

Reply to this Message