Re: SQL Server 2005 disk layout - opinions?

Since this is not my area of expertise, I asked in our internal MVP
forum, and my MVP mate Andrew Kelly had this comment:

>>>>>
I have an EMC SAN. I will separate storage into 3 RAID 10 LUNs
(separate spindles):
D: is TEMP
E: is DATA (DATABASES)
F: is LOG
The server has 2 dual-core CPUS, so I will plan on 4 equally-sized DATA
files per user database (I will only use a PRIMARY filegroup). Should I
have 4 equally-sized LOG files per user database as well?
<<<<<

It is OK to have 4 files for tempdb due to the potential contention issues
outlined here:
http://www.microsoft.com/technet/prodtechnol/sql/2005/workin gwithtempdb.m
spx

but I would not create multiple files for the user databases if the dbs are
of the size you mention. But you should definately create a secondary
filegroup with 1 file and leave only the system objects in the primary
filegroup for recovery purposes. And DO NOT create more than 1 log file per
db. Adding additional logs will not help performance due to the sequential
access. I would also move the tempdb log file to the same Raid 10 as the
other log files.

>>>>>
Is there any good reason to create database sub-folders in the drives?
Like:
E:\msdb\MSDBData.mdf
E:\model\model.mdf
E:\master\master.mdf
E:\a\a.mdf
E:\a\a1.ndf
...and so on....
<<<<<<<

If the number of dbs are reasonable then I prefer to have the data and log
files in separate sub dirs so there is never an issue if two dbs have the
same physical name for the files.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx