SQL Server 2005 disk layout

SQL Server 2005 disk layout - opinions?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

aj - 27 Mar 2008 18:54 GMT

I'm planning the disk layout for my soon-to-be-live SQL Server 2005 SP2
OLTP database. I've read all of the "best practices" doc I can find,
and have an idea of what I will do. I am looking for any comments and
generally having the experts shoot holes in it. Any help is
appreciated.

I know there are a million more questions that could be asked about the
system -- I'm just trying to define /general/ guidelines for this (or
any other) SQL Server system that I administer.

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?

Lets assume I have user databases called A and B, and perhaps 30-40 gigs
of total data. Logs will be ~25% of database size. Here are the drive
contents:

D:\ (TEMP)
tempdb.mdf
tempdb1.ndf
tempdb2.ndf
tempdb3.ndf
templog.ldf

E:\ (DATA)
MSDBData.mdf
model.mdf
master.mdf
a.mdf
a1.ndf
a2.ndf
a3.ndf
b.mdf
b1.ndf
b2.ndf
b3.ndf

F:\ (LOG)
MSDBLog.ldf
modellog.ldf
mastlog.ldf
a.ldf
b.ldf

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....

As I said: any comments appreciated.

A soon-to-be SQL Server DBA..

aj

Reply to this Message

jlaustill@gmail.com - 28 Mar 2008 15:10 GMT

> I'm planning the disk layout for my soon-to-be-live SQL Server 2005 SP2
> OLTP database. I've read all of the "best practices" doc I can find,
[quoted text clipped - 60 lines]
>
> aj

You have a good start here. The size of your logs is going to be very
important here, and they will depend on many factors. The first is
what recovery model's will you be using? Where will you do your daily
backups to? Will you have enough drive space with that format to do
weekly maintenance such as index rebuilding? Breaking your drives
apart will make things faster, but it comes at a price, mainly wasted
disk space. Think about the above questions and let everyone know
your thoughts :)

Good day,
Joshua Austill
Senior DBA

Reply to this Message

Pasi Oja-Nisula - 28 Mar 2008 16:03 GMT

> I'm planning the disk layout for my soon-to-be-live SQL Server 2005 SP2
> OLTP database. I've read all of the "best practices" doc I can find,
> and have an idea of what I will do. I am looking for any comments and
> generally having the experts shoot holes in it. Any help is
> appreciated.

I'm no expert in this field, but recently I had a server with roughly
the same specs and I did some tests with filesystem options and
also with tempdb. This is interesting stuff for someone who mainly
does T-SQL, so I wrote about my experiments.

http://seepia.dyndns.org/~pojanisu/diskalignment/diskalignment.html
http://seepia.dyndns.org/~pojanisu/tempdb/tempdb.html

If you have a possibility to test for example how the separating
tempdb in multiple files works in your case, it would be interesting
to know the results.

Pasi

Reply to this Message

Erland Sommarskog - 30 Mar 2008 10:28 GMT

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/workingwithtempdb.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.

Signature

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

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

Reply to this Message