Performance Problem - Request for advice

I am a DBA working for a large business in the North East of England.

One of our main Database servers (SQL Server 2000 SP4) is configured as follows:

Storage - 1. Logical Drive configured as RAID5 with about 800MB of storage, 40% free space. Used for main database files. 8 Physical drives.
- 2. Logical Drive configured as RAID1 with about 140MB of storage, 20% free space. Used for sql log files.

Both 1 and 2 are on the same controller (SCSI).

- 3. System Drive (C:)

Storage configuration was done by an external consultancy and disk performance (when benchmarked) was excellent.

Memory - 8GB of memory 6.5GB allocated to SQL Server
Operating System: Windows server 2003 Enterprise Edition
SQL Server: 2000 SP4

We would appreciate your advice regarding the following problem that we have on busy times:

Typical number of users/connections to SQL Server is between 200 to 250 (sysprocesses)
Typical number of active processes can vary between 5 to 30 (all sorts of applications)

The problem we have is that on some busy days a combination of processes grinding the server to a halt.
When we check the activity (normally using sp_who2) we can see that there are no blocked processes, cpu and DiskIO progressing as normal
(some values maybe large but not beyond expectation).


However, when "the problem" occurs, everything (queries, updates, etc) is extremely slow for 5-20 minutes until the bottleneck is freed.

We would appreciate any advice (tempdb?, disk bottleneck?, etc) and recommendation for any useful tools.

Thank you,You say when the bottleneck is freed...do you know what it is? Do you assume that it's freed because performance come back.

IMHO, I would not mess with memory allocation. I would let SQL Server do whatever it wants

BTW, the number of users/connections seems pretty low.

My guess is that there is some background batch job kibking off during that time which is a pig....|||Hows your tempdb during the time of extremely slow?|||Other simple stuff,

Are your statistics upto date on tables?
Is your disk storage fragmented?
Any bad disk warnings from the raid control?

The memory allocation for db growth (i.e 10% or 10MB) in the Transaction log can bugger it up too depending on it's growth and backups.

..and the killer... any virus checking going on?
I've had issues where {insert top end brand name here} have caused massive performance reductions due to unnecessary scheduled scans (company policy) or signature update downloads.

Is someone starting up a games server on the box?

Cheers
Phil

--
Special one just for you:
Police in Yorkshire are concerned about an alarming rise in the new trend of drug takers using dentists's syringes to inject drugs directly into the mouth.
They have described this tactic as "E by Gum":shocked:

Performance outside private network

Can SQL run more than one request at the same time from the same IP address?
I've noticed that if I open two instances of my site in IE they tend to ru
n sluggish, and it seems like the second instance does not start to respond
to a request until the firs
t request is completed on my first instance. I'm trying to run both request
s simultaneously. I'm running SQL Server 2000 Developer's Edition, by the w
ay. Any takers?>
> Can SQL run more than one request at the same time from the
> same IP address? I've noticed that if I open two instances of
> my site in IE they tend to run sluggish, and it seems like the
> second instance does not start to respond to a request until the
> first request is completed on my first instance. I'm trying to run
> both requests simultaneously. I'm running SQL Server 2000 Developer's
> Edition, by the way. Any takers?
>
--
You address this as a normal application performance issue. This is the
place to start:
HOW TO: Troubleshoot Application Performance Issues
http://support.microsoft.com/?id=298475
Eric Crdenas
Senior support professional
This posting is provided "AS IS" with no warranties, and confers no rights.

Performance outside private network

Can SQL run more than one request at the same time from the same IP address? I've noticed that if I open two instances of my site in IE they tend to run sluggish, and it seems like the second instance does not start to respond to a request until the firs
t request is completed on my first instance. I'm trying to run both requests simultaneously. I'm running SQL Server 2000 Developer's Edition, by the way. Any takers?
>
> Can SQL run more than one request at the same time from the
> same IP address? I've noticed that if I open two instances of
> my site in IE they tend to run sluggish, and it seems like the
> second instance does not start to respond to a request until the
> first request is completed on my first instance. I'm trying to run
> both requests simultaneously. I'm running SQL Server 2000 Developer's
> Edition, by the way. Any takers?
>
You address this as a normal application performance issue. This is the
place to start:
HOW TO: Troubleshoot Application Performance Issues
http://support.microsoft.com/?id=298475
Eric Crdenas
Senior support professional
This posting is provided "AS IS" with no warranties, and confers no rights.

Performance outside private network

Can SQL run more than one request at the same time from the same IP address? I've noticed that if I open two instances of my site in IE they tend to run sluggish, and it seems like the second instance does not start to respond to a request until the first request is completed on my first instance. I'm trying to run both requests simultaneously. I'm running SQL Server 2000 Developer's Edition, by the way. Any takers?>
> Can SQL run more than one request at the same time from the
> same IP address? I've noticed that if I open two instances of
> my site in IE they tend to run sluggish, and it seems like the
> second instance does not start to respond to a request until the
> first request is completed on my first instance. I'm trying to run
> both requests simultaneously. I'm running SQL Server 2000 Developer's
> Edition, by the way. Any takers?
>
--
You address this as a normal application performance issue. This is the
place to start:
HOW TO: Troubleshoot Application Performance Issues
http://support.microsoft.com/?id=298475
--
Eric Cárdenas
Senior support professional
This posting is provided "AS IS" with no warranties, and confers no rights.