SQL Server File Placement Best Practice

Question

Post reply

SQL Server File Placement Best Practice

oracle_dba_newbie

Old Hand

Points: 359
More actions
October 7, 2010 at 2:47 pm

#227751

Hi everyone,
I new to SQL Server (currently working with Oracle). I've been tasked with setting up a SQL Server 2008 environment.
My initial question relates to the preparation of the disk storage. The disk storage is directly attached to the server.
I'm planning something along the following lines
C:\ OS and SQL Binaries
D:\ User databases (mdf)
E:\ User databases (ldf)
What about the tempdb - should it be split across 2 drives in a similar manner to the user databases
ie set up
F:\ Tempdb (mdf)
G:\ Tempdb (ldf)
H:\ A scratch drive to hold backups etc
Do I leave the system databases in their default position on the C:\ drive or should they be moved elsewhere?
Thanks in anticipation

Viewing 15 posts - 1 through 15 (of 17 total)

You must be logged in to reply to this topic. Login to reply

AndrewSQLDBA SSC-Insane Points: 21827 More actions · Answer 1

I never place anything of SQL on the same drive as the OS, in fact, nothing ever goes on the same spindle as the OS.

As far as databases, I keep all my databases under a directory on one drive and the logs on a different drive.

I name my directories very descriptive, I create a separate directory for each database, I like to be organized. For the really large database, I place them on a drive by themselves, same for the logs. These are usually on a SAN, where they will not run out of space. All of the corporate databases are on a SAN in fact. But the databases that I use at home, a SAN is not needed. For databases that will be log intensive, I place them by themselves, but not too many. You need to determine how many writes or deletes each one will have, and then store them accordingly. Do not keep all the high intensity databases on the same drive, I will mix in some small, less intensive databases.

On one drive:

SQL Server 2008 Databases

|-> Database Name --> Database Name.mdf

On another drive:

SQL Server 2008 Database Logs

|-> Database Name --> Database Name.ldf

In short, another one of these answers - "It Depends"

Andrew SQLDBA

DNA_DBA SSCrazy Eights Points: 9116 More actions · Answer 2

We put the binaries on the os disk, the system databases on another disk, the tempdb (+log) on another disk and then the user database and logs on separate disks. When using the SAN we have a base drive (eg: D:) and then use mount points underneath to separate the databases and logs, and each mount point can utilize different size, number and types of disk - this allows multiple instances each with their own base drive

grahamc SSCertifiable Points: 6762 More actions · Answer 3

I have never been a fan of splitting the OS and SQL, especially on dedicated SQL Servers, seems silly. But until my dream of a SQL OS comes true, here is my plan for my next upgrade (big DB, SAN drives, lots of concurrent users. And since the 2008 installer is rather good and uses a single INSTANCE based folder...

C - OS

D - Instance

E,F,G,H - User DB MDF files (lots of splitting between high use tables, partitioned tables, etc)

I,J,K,M - User DB Index files

L,O - User DB LOG files

P - PageFile - I think this is quite a biggie and one that is normally forgotten about. MS recommends a PageFile of 1.5x the available physical memory.

S,T,U,V,W,X,Y,Z - TempDB split per CPU...

:w00t:

oracle_dba_newbie Old Hand Points: 359 More actions · Answer 4

Thanks for all your replies, they certainly given me a few ideas to go away with.

Has anyone any thoughts as to the placement of the tempdb. All the documentation points to placing the tempdb on a separate drive with 1 file per cpu core. However should the tempdb log file (ldf) be placed on another separate drive (ie 2 drives one for the tempdb mdf/ndf files and one for the tempdb ldf file or should it just be placed on the same drive as the tempdb mdf/ldf files.

Derrick Smith SSChampion Points: 10833 More actions · Answer 5

Just a note..that whole thing about 1 tempdb file per cpu core is outdated. It was probably true back in SQL 2000, but nowadays it's bad advice. You should have multiple, but I wouldn't make more than 4 or so on a 16 logical cpu system.

oracle_dba_newbie Old Hand Points: 359 More actions · Answer 6

Any suggestions regarding the placement of the tempdb mdf and ldf files, should i be looking at two separate drives for the tempdb?

Derrick Smith SSChampion Points: 10833 More actions · Answer 7

Separate spindles is ALWAYS better..although not always possible due to SAN storage being shared/spanned, cost, etc.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

grahamc (10/8/2010)
L,O - User DB LOG files

Log files plural? Multiple DBs I assume.

P - PageFile - I think this is quite a biggie and one that is normally forgotten about. MS recommends a PageFile of 1.5x the available physical memory.

Ideally on a SQL box you don't want the page file in use at all. You need it, in case of a bluescreen and full memory dump, but you don't generally want to use it.

S,T,U,V,W,X,Y,Z - TempDB split per CPU...

Splitting for IO bottleneck or splitting for allocation contention?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

Derrick Smith (10/8/2010)
Just a note..that whole thing about 1 tempdb file per cpu core is outdated. It was probably true back in SQL 2000, but nowadays it's bad advice. You should have multiple, but I wouldn't make more than 4 or so on a 16 logical cpu system.

In addition, there are two reasons to split tempDB into multiple files. IO bottleneck and allocation contention. The recommended splits are different depending why you're splitting it.

As for numbers, I often recommend starting at 1/4 the number of cores and then testing and seeing if additional splits are necessary.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

george sibbald SSC Guru Points: 104210 More actions · Answer 10

no ones mentioned the blocking factors to use yet - 8K for logs, 64K for data files, 4K for system databases.

You don't say which OS you are using, if less than windows 2008 make sure the drives are track aligned, windows 2008 automatically takes care of that.

---------------------------------------------------------------------

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 11

One other thing I noticed. You said direct attached storage. What RAID level do you have on those drives?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

grahamc SSCertifiable Points: 6762 More actions · Answer 12

GilaMonster (10/10/2010)
grahamc (10/8/2010)
L,O - User DB LOG files
Log files plural? Multiple DBs I assume.
P - PageFile - I think this is quite a biggie and one that is normally forgotten about. MS recommends a PageFile of 1.5x the available physical memory.
Ideally on a SQL box you don't want the page file in use at all. You need it, in case of a bluescreen and full memory dump, but you don't generally want to use it.
S,T,U,V,W,X,Y,Z - TempDB split per CPU...
Splitting for IO bottleneck or splitting for allocation contention?

Yes, multiple DBs... 5 DBs, I want the 1 log split off from the rest.

No you dont, but I want the pagefile separated for if it does page.

Splitting the tempDB for a couple of reason:

- I have no idea how the SAN is setup

- I have very little room for making changes in this phase of work

- The code is no where near good enough and 80% of procedures use massive temp tables (this includes reports)

The 8 is a bit of a rough guess right now (still waiting for the final server spec), might scale that down to half... I also have VERY little info on the PROD server, whats happening how its working etc. Its one of those rather locked down environments, where PROD access for pretty much anyone is a no-no :hehe: Its what makes it so much fun here 😀

Nice Marmot SSCrazy Points: 2278 More actions · Answer 13

This has been a very valuable thread to read. Maybe the OP might be interested in this disk partioning article: http://msdn.microsoft.com/en-us/library/dd758814.aspx.

One point I did not see discussed was:

oracle_dba_newbie (10/7/2010)
Do I leave the system databases in their default position on the C:\ drive or should they be moved elsewhere?

Is it now best practice (i.e. least hassle) to leave the master and mssqlsystemresource databases in the default install location?

george sibbald SSC Guru Points: 104210 More actions · Answer 14

Nice Marmot (10/12/2010)
This has been a very valuable thread to read. Maybe the OP might be interested in this disk partioning article: http://msdn.microsoft.com/en-us/library/dd758814.aspx.
One point I did not see discussed was:
oracle_dba_newbie (10/7/2010)
Do I leave the system databases in their default position on the C:\ drive or should they be moved elsewhere?
Is it now best practice (i.e. least hassle) to leave the master and mssqlsystemresource databases in the default install location?

they should be moved away from the OS ideally. And this should also be done at install time, not after the event.

---------------------------------------------------------------------