locked
Disk Stripe Width RRS feed

  • Question

  • Since SQL Server uses 64K pages, isn't it MORE advantageous to use a larger stripe width, say, 1MB?  This would allow for multi-page reads.

    Doc

     

    Monday, August 30, 2010 8:02 PM

Answers

  • SQL will also perform read ahead operations and read prefetch operations during processing to optimize CPU and IO utilization (http://technet.microsoft.com/en-us/library/cc966500.aspx). Writes to the data files is generally multipage as well, since changes are first written to the transaction log, and held in the buffer pool until checkpoint occurs, at which time the changed pages get flushed to disk using scatter/random IO.the ideal stripe size based on the partition alignment whitepaper referenced above is generally 64KB, allowing a single extent to incur a single IO and notnhave to split RAID boundaries, but depending on the workload and hardware being configured a 128KB or 256KB stripe may be ideal, allowing contiguous extents to be retrieved from the same spindle in the array.
    Jonathan Kehayias
    http://sqlblog.com/blogs/jonathan_kehayias/
    http://www.twitter.com/SQLSarg
    http://www.sqlclr.net/
    Please click the Mark as Answer button if a post solves your problem!
    • Marked as answer by JoeBx Monday, October 4, 2010 9:32 PM
    Tuesday, September 7, 2010 9:59 PM

All replies

  • Hi JoeBx,

    If I understand correctly, you means stripe unit size other than stripe width.

    Stripe width refers to the number of parallel stripes that can be written to or read from simultaneously.
    Stripe unit size is the size of each element of the stripe, as stored on each member of the disk group. Stripe size is a product of the stripe unit size and the number of disks in a RAID group. Stripe unit size is the attribute of a RAID disk group that can be configured by administrators. A stripe unit is the collection of bits on each disk exactly equal to the stripe unit size.

    Windows does not have a reliable way to determine stripe unit sizes. These values are obtained from vendor disk management software or from your SAN administrator.

    For more information, please see:
    Disk Partition Alignment Best Practices for SQL Server: http://msdn.microsoft.com/en-us/library/dd758814(SQL.100).aspx

    Thanks,
    Jin Chen


    Jin Chen - MSFT
    Tuesday, August 31, 2010 9:12 AM
  • Hi Jim,

       Yes, stripe width and stripe unit size are one and the same.  One prefers stripe width because it is more descriptive and easily understood.  More to my question: Is there any SQL setting that would encourage SQL Server to perform multi-page reads and writes?

     

    Joe

    Tuesday, September 7, 2010 1:52 PM
  • SQL will also perform read ahead operations and read prefetch operations during processing to optimize CPU and IO utilization (http://technet.microsoft.com/en-us/library/cc966500.aspx). Writes to the data files is generally multipage as well, since changes are first written to the transaction log, and held in the buffer pool until checkpoint occurs, at which time the changed pages get flushed to disk using scatter/random IO.the ideal stripe size based on the partition alignment whitepaper referenced above is generally 64KB, allowing a single extent to incur a single IO and notnhave to split RAID boundaries, but depending on the workload and hardware being configured a 128KB or 256KB stripe may be ideal, allowing contiguous extents to be retrieved from the same spindle in the array.
    Jonathan Kehayias
    http://sqlblog.com/blogs/jonathan_kehayias/
    http://www.twitter.com/SQLSarg
    http://www.sqlclr.net/
    Please click the Mark as Answer button if a post solves your problem!
    • Marked as answer by JoeBx Monday, October 4, 2010 9:32 PM
    Tuesday, September 7, 2010 9:59 PM