Re: Install SS 2005 - log files best practice

Tech-Archive recommends: Fix windows errors by optimizing your registry

They are definitely not talking about logical drives. Like Andrew said, you won't gain anything by placing your files on different logical drives if these are on the same physical disks.
The reason for placing the files on different physical arrays (and best of all on different disk/RAID controllers), is to split the load to different hardware.
An example is if SQL server are going to write some data to the logfile while at the same time another user is requesting some data. If you have your files on different PHYSICAL arrays, the database array can read the data from the database while at the same time the log file array can write the data to the log file.
If you have both your database- and logfile on the same physical arrays (no mater if it's different logical drives or not..) the array can't both read and write at the same time. One of the operation might have to wait for the other to have finished it's work before it can continue with other tasks.

I know the above is quite simplified and in many cases there are RAID controllers/cache etc. that tries to "limit" these disadvantages but I think you get the big picture?

Depending on the load on the server, there might be cases where you don't see the big performance gain in splitting data on different arrays though. If you are using a SAN as storage, that might also give a different picture, but that another story...:-).

--
Regards
Steen Schlüter Persson
Database Administrator / System Administrator

Sandy wrote:
Hi Kelly -

Thanks for your response. The following is a direct quote from Microsoft SQL Server 2005 Implementation and Maintenance (MCTS Exam 70-431) Self-Paced Training Kit:

"To configure data and log files for best performance, follow these best practices:

* To avoid disk contention, do not put data files on the same drive that contains the operating system files.
* Put transaction log files on a separate drive from data files. This split gives you the best performance by reducing disk contention between data and transaction log files.
* Put the tempdb on a separate drive if possible, preferably on a RAID 10 or RAID 5 system. In environments in which there is intensive use of tempdb databases, you can get better performance by putting tempdb on a separate drive, which lets SQL Server perform tempdb operations in parallel with database operations."

Are they talking about logical drives?

Would you have any links to articles that support putting everything on a single partition?
.

Relevant Pages

  • Re: Configuring Harddrive Array, plus location of DB and Log file
    ... to be separate Arrays. ... I've been told that the Database and the Log file should be on ... I'm going to need 2 Logical drives when I configure my array. ... I'm thinking I would want the Data partition to be RAID1+0 ...
    (microsoft.public.sqlserver.setup)
  • Re: monitoring raid arrays
    ... They have hardware raid 1 arrays controlled respectively by a Compaq ... controller and PERC 4/SC. ... Here is the relevant dmesg output on the Proliant: ... delete logical drives supported by controller ...
    (freebsd-questions)
  • Re: monitoring raid arrays
    ... They have hardware raid 1 arrays controlled respectively by a Compaq Smart Array 532 ... Here is the relevant dmesg output on the PowerEdge: ... delete logical drives supported by controller ...
    (freebsd-questions)
  • Re: Configuring Harddrive Array, plus location of DB and Log file
    ... best practice is to seperate the Data and Log files. ... I'm afraid locating data and log files on logically seperated drives will not help you to gain performance advantage of seperation of these files. ... you need to seperate your arrays physically rather than logically to gain performance benefit. ... I'm going to need 2 Logical drives when I configure my array. ...
    (microsoft.public.sqlserver.setup)
  • Re: Database filegroups
    ... unless I have a strong reason to do other wise... ... > have 2 logical drives assign to the storage, ... > in a year the new database will grow to about 200GB. ... > considering allocate one mdf file of 200GB, ...
    (microsoft.public.sqlserver.server)