Since you have mainly a CPU bottleneck and not a IO bottleneck I think you should not care that much for optiomal disk performance now.
RAID 5 is good for reading and obviously enough for writing (with the load you currently have) because you dont get high values on the disk queue length performance counters. RAID 5 is not the worse choice for data file if you dont have the demand to get the optimum out of the storage because it is cheaper (only 1 disk needed for redundancy).
How long ist the time period of your sine wave CPU load ? (Is it seconds or minutes, ..)
How long are the timeouts that are reached ?
The default timeout In ADO is 10 seconds.
If a statement or stored procedure has much to calculate and needs more than 10 seconds (when default is used) before it is ready it will be cancelled.
Did you trace the CPU usage, Duration, Reads, etc. of your statements in the Sql Server profiler ?
This could give you some hints too.
-----Original Message-----
From: mdefnet
[mailto:mssqldba-ezmlmshield-x81077018.[Email address protected]
Sent: Donnerstag, 27. Mai 2004 20:26
To: LazyDBA.com Discussion
Subject: RE: performance thresholds
This SQL server is running off a web farm of three web servers and a
standalone web server (four altogether). We get about 3 hits per second on
average, with the hit count probably 7-10 during the day. It is a .NET
application where all the pages are built using DB data. It is probably
60-70% reads of data with the remainder writes. We only have one user that
the application uses for connection purposes.
The web site could be accessed by up to a thousand different contractors who
log onto it (each with a unique ID). But their ID is not used for database
access, only for site authentication and then for SELECTs/WHEREs of the
data.
-----Original Message-----
From: Forchue Abraham
[mailto:mssqldba-ezmlmshield-x64293686.[Email address protected]
Sent: Thursday, May 27, 2004 2:06 PM
To: LazyDBA.com Discussion
Subject: RE: performance thresholds
How many users you have connected to sql server?
-----Original Message-----
From: mdefnet
[mailto:mssqldba-ezmlmshield-x20583642.[Email address protected]
Sent: Thursday, May 27, 2004 12:59 PM
To: LazyDBA.com Discussion
Subject: performance thresholds
We are running a SQL Server that is generally running at an average of 50%
CPU utilization and that has me somewhat concerned. It will sometimes
crater to 10-15% (and lower) and sometimes swells to 80% (sometimes even
more, but generally not). It generally follows a sine wave pattern between
30 and 70%. It is running on a Compaq DL380G3 running W2K3 Enterprise
Edition, SQL Server 2000 SP3a, 6GB of memory, SQL Server itself is installed
on drive C:, the two user databases and logs share drive D: which is RAID-5
on 4x146GB drives, and 2 physical 3.08GHz CPUs (that appear as four to the
OS and SQL Server ... I was told the two virtual CPUs are the same as
physical ones). It never does memory pages, and the avg. disk queue length
is almost always under 1. The databases are both about 10GB in size with
5GB of logs each so there is a lot of open disk space.
Does the CPU utilization sound too high? I don't know what comprises a
reasonable threshold for a SQL Server-based server. The primary reason I am
asking is that the developers are stating they are getting frequent
timeouts. Should the log files be put on a separate drive array? I've read
that the log files should be physically separated from the data files and
the recommendation is RAID-5 for the DB and RAID-10 for the logs. Is this
overkill?
Any thoughts or recommendations are greatly appreciated. Or am I concerned
for no reason?
Mike
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
Website : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
For additional commands, e-mail: mssqldba-[Email address protected]
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
Website : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
For additional commands, e-mail: mssqldba-[Email address protected]
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
Website : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
For additional commands, e-mail: mssqldba-[Email address protected]
MS Sql Server LazyDBA home page