disk alignment partitioning for SQL performance

Posted on 2009-12-28
Last Modified: 2012-05-08
KevinK recommends for SQL on Windows Server 2003  to have 64K sector offset for the Data drives.

Ours has 32K as the Starting Offset. Since 64K is multiples of 32K, will that be ok, or is it recommended to change it to 64KB.

Question by:anushahanna
    LVL 6

    Accepted Solution

    I know that it is recommended to have 64K, but after realigning disk and setting to 64K - did not gain any performance (tested with SQL backup to see MB/s) and users reported no difference. Our ERP DB is ~20GB.
    By the way, Windows 2008 Server sets 64K by default.
    LVL 6

    Author Comment

    Ours is 122GB.

    Vaidas911, did you do it on W 2003 Server?

    I thought ours was 32KB, but the it is only 31.5K of metadata.
    LVL 6

    Expert Comment

    Ours also W 2003 Server, and yes, 31.5K is standart.
    LVL 5

    Assisted Solution

    What would make a monster of a difference is increasing your RAID controller's stripe size and getting more cache to your controller, adding battery to have a battery backed cache.. That surely makes things better.

    If it's not possible, then it's a good idea to span the data on more spindles, specially on your case with such a huge database.
    LVL 6

    Author Comment

    tiago_aviz, it is just not about the stripe size, but  if it will lead to 2 IO ops instead of 1.

    We have 100 spindles underneath our LAN, but still the Disk IO has remained the same..
    LVL 6

    Author Comment

    Vaidas911, perhaps you should have gone higher than 64k?

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Solid State Drive Performance Tips: Solid state storage technology is now a standard.  After testing and using several different brands and revisions of SSD's over the years I have put together a collection of tips,tools and suggestions that I ha…
    Ever notice how you can't use a new drive in Windows without having Windows assigning a Disk Signature?  Ever have a signature collision problem (especially with Virtual Machines?)  This article is intended to help you understand what's going on and…
    This video teaches viewers how to encrypt an external drive that requires a password to read and edit the drive. All tasks are done in Disk Utility. Plug in the external drive you wish to encrypt: Make sure all previous data on the drive has been …
    This Micro Tutorial will teach you how to reformat your flash drive. Sometimes your flash drive may have issues carrying files so this will completely restore it to manufacturing settings. Make sure to backup all files before reformatting. This w…

    754 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

    20 Experts available now in Live!

    Get 1:1 Help Now