MS SQL Server - SQL Server 2000 instances suffer from sudden slow down

Author

SQL Server 2000 instances suffer from sudden slow down

melliott1963

2005-10-11, 7:24 am

Hi,

I'm the DBA for 9 SQL Server 2000 machines. These contain a mixture of
in-house developed applications and 3rd party applications.

At the moment, most are still running SP3 with a subsequent hotfix applied,
although 3 now have SP4 installed. Also, most are running on Windows 2000,
with a few on Windows 2003. All instances of Windows are fully patched.

Over the past few years, all these machines have been very stable with
little or no problems experienced. However, over the past half year or so,
we've started to experience sudden, dramatic slow down on a few of the
machines.

When the slow down occurs, there's no blocking issues, no 'unusual' activity
going on, CPU usage is very low, and no error messages are generated. Once
the system is in this state, the only way I've found to 'fix' it, is by
stopping and restarting the SQL Server instance, and then everything runs
well again.

This problem happened when all machines were on SP3, but has still occured
on one of the machines that has been upgraded to SP4. It is also occuring on
both Windows 2000 and Windows 2003 machines.

As far as the actual usage of the SQL Servers is concerned, the 2 main
machines where we most suffer from this couldn't be more different.

Our main production server has dual 800MHz processors and 2Gb Ram (which
admittedly in under powered and is shortly to be replaced), will have, on
average anything up to 50 users at any one time, accessing around 10
databases.

The other machine which suffers from this problem, has dual 3.40GHz
processors and 4Gb Ram and has a maximum of 5 users accessing 2 databases.

Initially, we though it could be to do with our Virus checker (McAfee
VirusScan Enterprise), but we have disabled the on access virus checking
during the day on our main production server for the time being.

I've done a quick search on Google and found someone else who seems to be
suffering from the same problem, but no one has come up with an explanation.

Has anyone else experienced this problem and, if so, have you found a proper
fix?

Thanks for any help.

Jerry Spivey

2005-10-12, 11:23 am

Many times this can be attributed to inefficient data access consuming
memory and physical I/O. Might check:

1. Ensure the appropriate indexes exist to help minimize physcial I/O
2. Ensure the end-user ad-hoc queries (not really recommended) include
WHERE clauses (that match the indexes)
3. Check for table/clustered index scans for queries
4. Ensure the statistics are current and up-to-date
5. Ensure that no CROSS JOINs (or multiple tables withouth WHERE clause -
old syntax) are not occuring
6. Perhaps think about using the Query Governor Cost Limit

HTH

Jerry

"melliott1963" < melliott1963@discuss
ions.microsoft.com> wrote in message
news:A5C815EE-BBB3-4727-9EE9- 6F9240F90840@microso
ft.com...
> Hi,
>
> I'm the DBA for 9 SQL Server 2000 machines. These contain a mixture of
> in-house developed applications and 3rd party applications.
>
> At the moment, most are still running SP3 with a subsequent hotfix
> applied,
> although 3 now have SP4 installed. Also, most are running on Windows 2000,
> with a few on Windows 2003. All instances of Windows are fully patched.
>
> Over the past few years, all these machines have been very stable with
> little or no problems experienced. However, over the past half year or so,
> we've started to experience sudden, dramatic slow down on a few of the
> machines.
>
> When the slow down occurs, there's no blocking issues, no 'unusual'
> activity
> going on, CPU usage is very low, and no error messages are generated. Once
> the system is in this state, the only way I've found to 'fix' it, is by
> stopping and restarting the SQL Server instance, and then everything runs
> well again.
>
> This problem happened when all machines were on SP3, but has still occured
> on one of the machines that has been upgraded to SP4. It is also occuring
> on
> both Windows 2000 and Windows 2003 machines.
>
> As far as the actual usage of the SQL Servers is concerned, the 2 main
> machines where we most suffer from this couldn't be more different.
>
> Our main production server has dual 800MHz processors and 2Gb Ram (which
> admittedly in under powered and is shortly to be replaced), will have, on
> average anything up to 50 users at any one time, accessing around 10
> databases.
>
> The other machine which suffers from this problem, has dual 3.40GHz
> processors and 4Gb Ram and has a maximum of 5 users accessing 2 databases.
>
> Initially, we though it could be to do with our Virus checker (McAfee
> VirusScan Enterprise), but we have disabled the on access virus checking
> during the day on our main production server for the time being.
>
> I've done a quick search on Google and found someone else who seems to be
> suffering from the same problem, but no one has come up with an
> explanation.
>
> Has anyone else experienced this problem and, if so, have you found a
> proper
> fix?
>
> Thanks for any help.

melliott1963

2005-10-12, 11:23 am

Thanks for the reply.

I'll definitely check these out, although to be quite honest I doubt that
this is the problem. The only reason why I say this is that, on the machine
with very few users, when it's virtually ground to a halt, because there's
only 3 or 4 people using the server, I've easily been able to ask them to get
out of their databases, so the only active connection is mine. Even then just
navigating around the SQL instance using Enterprise manager is painfully slow.

Surely if the problem was to do with a database design or statistic problem,
once people stopped using the system, it should go back to running at normal
speed?

"Jerry Spivey" wrote:

> Many times this can be attributed to inefficient data access consuming
> memory and physical I/O. Might check:
>
> 1. Ensure the appropriate indexes exist to help minimize physcial I/O
> 2. Ensure the end-user ad-hoc queries (not really recommended) include
> WHERE clauses (that match the indexes)
> 3. Check for table/clustered index scans for queries
> 4. Ensure the statistics are current and up-to-date
> 5. Ensure that no CROSS JOINs (or multiple tables withouth WHERE clause -
> old syntax) are not occuring
> 6. Perhaps think about using the Query Governor Cost Limit
>
> HTH
>
> Jerry
>
>
> "melliott1963" < melliott1963@discuss
ions.microsoft.com> wrote in message
> news:A5C815EE-BBB3-4727-9EE9- 6F9240F90840@microso
ft.com...
>
>
>

Sponsored Links