Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

10 physical disks available - best configuration for data/log/backup? Expand / Collapse
Author
Message
Posted Tuesday, January 29, 2013 9:47 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, May 18, 2014 3:44 PM
Points: 5, Visits: 94
I have 10 300GB SAS 15k rpm 6Gb disks available to me on a HP P2000 SAN. I have a 150GB database and I want to configure these 10 disks for best performance. There are around 2 billion rows in one particular table and the workload consists mainly of querying out of this table and loading/deleting data into/from this table. Using SQL Server 2010 Standard Edition.

The OS for the server is on separate local disk, same type of drive, one 300GB disk mirrored. SQL Server tempdb is also on this C: drive.

So given these constraints, this is what I think would be best, but I would like to know what others think and why:

one array of 4 disks RAID10 for database files i.e. 600GB available space
one array of 4 disks RAID10 for log files, 600GB available space
one array of 2 disks RAID1 for backup, 300GB available space

The loading and deleting hit the database and log file hard which is why I have configured the log drive to have 2 disk spindles. I can live with slower backups.
Post #1413431
Posted Wednesday, January 30, 2013 6:25 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 1:53 PM
Points: 35,366, Visits: 31,905
If each disk is 300GB, how is it that you're only getting 600GB out of 4 drives and 300GB out of 2 drives according to your description? Is that for mirroring?

--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1413606
Posted Wednesday, January 30, 2013 10:50 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, May 18, 2014 3:44 PM
Points: 5, Visits: 94
Yes that's correct. This is a production system and some form of redundancy is required.

I was just wondering if instead of the layout I suggested, would it be better to just have one array of 10 disks in RAID5 i.e. 9 available disks, capacity=2.7TB and just have everything on the one volume (or split it via Windows into logical drives) - is the advantage of more spindles better than separating database & logs & backups into their own arrays?
Post #1413717
Posted Thursday, January 31, 2013 7:40 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 1:53 PM
Points: 35,366, Visits: 31,905
What about TempDB, an archive, and a spare? 10 Drives doesn't seem to be enough even if TempDB is on DAS (which would mess with clustering if you went that way).

To answer your question (apologies, I ask another question), what are you shooting for with your SAN? Max capacity with the ability to rebuild bad drives in an online fashion? Max speed (which can usually be done MUCH better by writing good code)? Max safety? or ???


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1414425
Posted Sunday, February 3, 2013 1:20 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, May 18, 2014 3:44 PM
Points: 5, Visits: 94
The SAN has other disks and also spares etc. The 10 disks I am talking about are available for dedicated use for this sql server.

I would certainly like to have more disks available, but given the constraints the question really was whether anyone had experience of whether SQL Server performed better with disk arrays dedicated for purpose i.e. separate database, logs & backup disk arrays where each disk array has few spindles (e.g. 2 in my case) versus a disk array with more spindles (e.g. 9 in my case) but with all the disk activity going to the same array. So I'm looking just at performance in this case as with either configuration I have redundancy to cope with a failed drive.

I may be able to test this myself soon when I reconfigure my Test environment.
Post #1415053
Posted Sunday, February 3, 2013 1:38 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 1:53 PM
Points: 35,366, Visits: 31,905
To answer that question, my experience has been that the more spindles you can involve, the better the performance. A lot of SAN folks will argue against that but I'm not sure why because it actually does make a lot of sense.

To wit, I normally try to separate MDF and LDF files to their own physical sets of spindles as well as setting up TempDB on it's own set of spindles. If I can, I'll set it up so that the MDF/NDF files of TempDB are on separate spindles from the LDF files but, no matter what, I try to put TempDB on it's own drive(s) so I can configure it differently than all of the others.

Of course and as a sidebar, that's not always possible with the ridiculously sized hard disks they have now. It was so much easier to right-size and get more spindles involved when disk size was much smaller. For example, I just can't see dedicating a 300GB drive to the LDF files of a system that won't grow to more than 600GB across multiple databases. It was a little tougher on electricity and cooling but it even allowed for faster disk replacement if one went bad because the system didn't have to rebuild so much.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1415055
Posted Sunday, February 3, 2013 2:07 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, May 18, 2014 3:44 PM
Points: 5, Visits: 94
Thanks for your replies Jeff. I think you are basically agreeing then with my original disk layout. I agree with your comment about disk sizes being annoyingly large now - I am old enough to remember setting up servers with arrays of 9/18/36/72GB disks and performance was pretty good even though disk technology has improved since then.
Post #1415057
Posted Sunday, February 3, 2013 3:32 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 1:53 PM
Points: 35,366, Visits: 31,905
The original layout isn't bad although I'd be tempted to NOT mirror backup drives and I'm pretty sure you won't need 600GB of LDF space for a 600GB set of MDFs. I'd be might tempted to use at least one of the drives as a dedicated drive for TempDB.

--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1415063
Posted Sunday, February 3, 2013 4:01 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, May 18, 2014 3:44 PM
Points: 5, Visits: 94
Thanks - I think you're right on both suggestions. The backup is written to tape anyway so I don't need to protect that disk from a failure and yes it would probably be better to move the tempdb area onto the SAN than use local disk. Certainly don't need the whole 600GB for logs.
Post #1415067
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse