SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


10 physical disks available - best configuration for data/log/backup?


10 physical disks available - best configuration for data/log/backup?

Author
Message
John Davys
John Davys
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 109
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.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)

Group: General Forum Members
Points: 220954 Visits: 42002
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
John Davys
John Davys
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 109
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?
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)

Group: General Forum Members
Points: 220954 Visits: 42002
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
John Davys
John Davys
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 109
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.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)

Group: General Forum Members
Points: 220954 Visits: 42002
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
John Davys
John Davys
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 109
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.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)

Group: General Forum Members
Points: 220954 Visits: 42002
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
John Davys
John Davys
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 109
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search