Another major reason to splitting data files and logs is to be able to
have concurrent physical data i/o to/from logs and data files. If they
are on same drive, the drive heads have to move back and forth, and data
file writes have to wait until the physical log writes are done. If
they are on same array, it's even worse (Log write at location 1 and log
write at location 2, then data write at location 4 and data write at
location 5, then log update at location 1 and log update at location 2).

Under an ideal setup, you would also have the data and log files on
separate drive controllers, so that the writes can be done almost
simultaneously. But you tempdb should also be on separate
drive/controller, as well as system files/program files, and backup
files, and I haven't seen a server yet with 5 controllers to handle
that.


=======================
Mark Harr


> -----Original Message-----
> From: Hugh du Toit
> [mailto:mssqldba-ezmlmshield-x33820009.[Email address protected]
> Sent: Friday, May 28, 2004 1:15 AM
> To: LazyDBA.com Discussion
> Subject: RE: performance thresholds[Scanned]
>
> Hi,
>
> Stupid question here - I've read on different sites and in
> this forum that one should store your logs and data files on
> different drive arrays. I agree, but if I have a SCSI array,
> and both files are on the array, isn't that good enough? The
> point of putting them on different drives is to save at least
> one file should the other crash. But if they are in an array,
> it should be a problem, should it? Odds are not all the
> drives in the array will crash at the same time...
>
> Regards
> Hugh
>
>
> -----Original Message-----
> From: Holland Stephen
> [mailto:mssqldba-ezmlmshield-x94949958.[Email address protected]
> Sent: Thursday, May 27, 2004 20:07 PM
> To: LazyDBA.com Discussion
> Subject: RE: performance thresholds[Scanned]
>
> If you have a lot of inserts, deletes, etc., you should not use RAID-5
> because of the parity that must be created for the new data entries.
> RAID-5 is good for static data, only.
>
> RAID 10 is optimum for both.
>
> And yes, the logs and data should most definitely be on PHYSICALLY
> separate media. No virtual drives, partitions, etc.
>
> -----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