June 7, 2010 at 4:48 am
I have a question about where to place the files of our databases.
We have a server which has 3 seperate harddrives, this is the max so we can't add another HD anymore.
On 1 HD we have the OS installed.
On 1 drive we have all the datafiles.
And on the last drive we have all the logfiles AND also the datafile and logfile of all SystemDatabases.
I'm not sure if placing all logfiles and all the Systemdatabases on the same HD is a good idea.
Does anybody have any advice on where to place what db / files?
June 9, 2010 at 3:09 am
I would recommend moving tempdb data file from the last drive; as heavy tempdb activity could slow down the log write of other user databases.
June 9, 2010 at 3:42 am
RAID arrays or single 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
June 9, 2010 at 4:11 am
Assuming this is a dedicated SQL Server, I would start with the following:
Drive 1: OS, system databases, tempdb
Drive 2: Databases
Drive 3: Logfiles
I would see how that performs, keeping an eye out for bottlenecks on the drives.
Thomas Rushton
blog: https://thelonedba.wordpress.com
June 9, 2010 at 7:11 am
I've been talking with our SystemAdministrator and it appears that he set the hardware up as followed :
1 RAID Controller with 5 physical harddrives
The 5 drives are divided (using the RAID Controller BIOS) as a RAID 1 with 2 physical drives and a RAID 5 with 3 physical drives.
The RAID 1 has 1 partition, all logfiles and all system databases are placed on this partition.
The RAID 5 has 2 partitions. 1 partition has the OS on it and the second partition has all datafiles.
In my opinion it would be best to change this as followed :
RAID 1 with only the logfiles
RAID 1 with only the datafiles
1 HD with OS and systemdatabases.
Is this a good setup for performance? Or would it be better if it were drives on seperates raid controllers?
June 9, 2010 at 7:16 am
NitroBoarder (6/9/2010)
The RAID 5 has 2 partitions. 1 partition has the OS on it and the second partition has all datafiles.
You may as well make that one partition. You're gaining nothing by having 2 partitions on the RAID array.
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
June 9, 2010 at 8:04 am
No, i know. a seperate partition only has some administrative advantages but nothing else.
But if i split the raid with 3 physical drives to 1 raid with 2 physical drives and 1 seperate drive does that have a performance gain?
I know that having a drive on a seperate controller for data and 1 drive on another controller is good for performance.
But i'm not sure when all drives are on the same RAID controller and we tell the controller BIOS to build 1 drive using all 5 physical drives or we tell it to build 2 drives each using 2 physical drives will have any benefit for performance?
June 9, 2010 at 8:17 am
NitroBoarder (6/9/2010)
No, i know. a seperate partition only has some administrative advantages but nothing else.But if i split the raid with 3 physical drives to 1 raid with 2 physical drives and 1 seperate drive does that have a performance gain?
Yes there may be a performance gain. But you also introduce greater risk.
I think I would leave, considering your hardware, all of it on one RAID array. In the meantime, I would work toward getting more drives and an additional RAID controller.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
June 9, 2010 at 9:14 am
Thanks for all the advice.
I will talk to our systemadministrator to see if i can get some new hardware...luckely he always likes ordering new servers 🙂
June 9, 2010 at 9:47 am
NitroBoarder (6/9/2010)
Thanks for all the advice.I will talk to our systemadministrator to see if i can get some new hardware...luckely he always likes ordering new servers 🙂
Well, if you're going for all-new hardware, then you might want to think about doing it properly. I mean really properly.
Separate RAID for OS, pagefile, TempDB, Databases, Logspace, Backups... I'm sure the guys here can come up with something particularly drool-worthy!
Thomas Rushton
blog: https://thelonedba.wordpress.com
June 9, 2010 at 9:49 am
You may want to look into getting a SAN as well. At least it should be an option.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
June 9, 2010 at 9:53 am
CirquedeSQLeil (6/9/2010)
You may want to look into getting a SAN as well. At least it should be an option.
Definitely.
See? I told you somebody else would be along with something even more drool-worthy... 😛
Thomas Rushton
blog: https://thelonedba.wordpress.com
June 9, 2010 at 11:33 am
Depends on the size and usage of the databases. SANs aren't magic solutions to storage problems, and they're not exactly cheap.
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
June 9, 2010 at 11:49 am
GilaMonster (6/9/2010)
Depends on the size and usage of the databases. SANs aren't magic solutions to storage problems, and they're not exactly cheap.
Agreed and Agreed. But they should be considered. They do bring in a different knowledge-base and skill set requirement.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply