Post reply

Methods for IO decisions

  • October 22, 2009 at 10:13 am

    #138967

    Hi all -

    I don't have a SAN to work with (yet!), so I'm trying to figure out how to best configure my DASD for a new SQL server. It is a Dell PE2900 with 10 drives on a single Perc 6i controller. Several small OLTP support databases (largest is 1GB total), heavy tempdb use.

    My question could actually be considered a continuation of the following thread regarding creating multiple RAIDs:

    http://www.sqlservercentral.com/Forums/Topic297230-146-1.aspx#bm298865

    I am considering the same config suggested there:

    OS = RAID1 - 2 drives

    SQLDATA = RAID10 - 4 drives

    SQL logs = RAID1 - 2 drives

    tempdb = RAID1 - 2 drives

    Colin Leversuch wrote in that previous thread:

    "There are a couple of points on seperating tempdb which I find are often overlooked .. this is only really relevent where tempdb is very heavily used.

    First if you don't allocate enough spindles to tempdb you actually impose a bottleneck on your server. Lets say your mdf files are on 6 available spindles - moving tempdb to a seperate array with less than 6 spindles could actually slow things down - the available i/o is relative to the number of spindles."

    So theoretically, a single RAID10 with 8 spindles would allow 4 times as much IO as any single RAID1, right? But how do you weigh the value of putting the tempdb and the DB log files on separate spindles against the speed of the larger, faster array?

    There is a SSC article on using ProcMon to capture all the IO for SQL files, and I was thinking of using this method to get IO activity for each DB and level according to those percentages - is there an easier way to do this?

    Also - what is the expert gut expectation on which would be faster in general - my proposed config or a RAID1 OS with an 8-spindle RAID10 for everything else?

    Please help me think like a "real" DBA! Thanks.

    Steve

  • October 22, 2009 at 1:45 pm

    #1069637

    Steve,

    It has been my experience that in a system as you are describing that you would want to do a configuration where the .mdf file(s) are on a RAID 5 array. This is conducive to high speed reads, which would be the database devices. The transaction log would be well off on a mirrored drive, which provided redundancy but is also conducive to high speed writes. The tempdb, for some people the say RAID 0, some RAID 1. I personally like RAID 10, which provides the security of redundancy as well as the speed for read/write that would boost performance.

    A link which may help you out is:

    http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1262122_mem1,00.html

    I would also keep the system pagefile on a seperate drive as well, as this can also be an issue. You need to consider where you are putting your system databases as well as your database dump files as well in order to not have them impact performance, though I usually allow the masdter, model and msdb to reside where I install the software.

    Finally, you need to consider the size of the database and what it's function is. Is this an OLTP system with a lot of users? Will there be reporting against it? You may want to consider partitioning the database itself into different segments in which case each of the .ndf files would ideally be placed on seperate RAID 5 arrays using different spindles.

    Proper placement/configuration of the drives will reduce headaches further down the road.

    Good luck

    Steve

    Steve Jimmo
    Sr DBA
    “If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan

Viewing 2 posts - 1 through 1 (of 1 total)

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