Sql Server 2005 & Sql Server 2008 Raid 1 OPTIMAL Stripe Size and NTFS Allocation Unit Size

Posted on 2010-01-08
Last Modified: 2012-05-08
HP DL370 G6, Quad Core Xeon E5530 2.4 Ghz, 12 Gigs Ram,  410i 256 Embedded Controller.
3 Raid 1 Mirrors:  2x 146Gb 15k (OS), 2x 600Gb 15k (Sql Database(s)/Unstructured Data), 2x600Gb 15k (Sql Logfiles and unrelated Files).

Server 2008 R2 Standard 64 Bit,  Initial Install of Sql Server 2005 and Upgrade to Sql Server 2008 when Accounting Application/Database is vendor certified for MS Sql Server 2008.

I have seen many different and conflicting expert recommendations on the net for Sql Server 2005/08 Optimal Raid 1 Stripe size and NTFS Allocation Unit Size for the Database and Logfiles.  Would appreciate any expert technical advice here on the above.  I am not soliciting information on the Raid 1 Selection.  I am aware of the varying benefits of Raid 1, Raid 5 and Raid 10, etc in regards to read/write Database performance.  

This server will be servicing a mission critical 4 Gigabyte and growing database for a 40 user environment with a maximum growth of ten employees in the next 4 years so I am not concerned with performance hits via the Raid Arrays for this server.  I am most comfortable with 3 Raid 1 mirrors.

Any comments on the above as well as MBR and GPT Partition Style selection and Basic/Dynamic disks in Server 2008 from those who have built and deployed production database servers is most appreciated.  More interested in practical experience rather than higher level theory.


Question by:rjearley1966
    LVL 13

    Expert Comment

    For partition alignment --> The default in Windows Server 2008 is good. 128KB, I believe? In my Windows Server 2003 environments I use 64k alignments.

    For the cluster size/NTFS stripe size I have had good luck with 64k there as well. Your mileage my vary but this has worked out well on warehouse type workloads as well as OLTP workloads in my environments.

    Good article:
    LVL 1

    Author Comment

    Thanks Mike. Surprised there are no opinions out there on MBR and GPT partition styles.  Tells me folks are playing it safe by going with what is known and familiar: MBR.
    LVL 1

    Author Comment

    LVL 13

    Accepted Solution

    I guess for smaller data volumes, I see no performance benefit to do the MBR style which is the "tried and tested" (and known and familiar). I have seen plenty of high volume low throughput (OLTP) workloads and high throughput in warehouse applications workloads work out fine with the MBR partition style.

    I think we will see more performance tests start to happen with GPT and we'll see suggestions and blogs but right now there is no official Microsoft best practice to move to a GPT and I personally haven't seen a reason to move that way yet, either.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
    Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
    Viewers will learn how the fundamental information of how to create a table.
    Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now