none
File Allocation Unit size

    Question

  • This is a question that has always intrigued me: what is the ideal File Allocation Unit Size for a disk holding only data or index pages on a server running SQL Server?  It seems to me that 8,192 would be the ideal size as it would enable the system to gobble up an entire page in one go.  Any ideas?
    Tuesday, March 20, 2007 8:57 PM

Answers

  • SQL Server grabs an 'extent' -which is 8 pages. It never takes just one page!

    So 64kb is the 'recommended' allocation unit size.

    Wednesday, March 21, 2007 12:10 AM
    Moderator
  • Your original question seemed to ask about disk allocation which is an OS function -NOT how SQL Server internally manages data.

    There are two separate issues here.

    Externally, SQL Server interacts with the OS in 64K chucks -extents.

    Internally, SQL Server manages data on the page level.

    An extent can be 'mixed', that is one page for one table, another page for a different table, etc. Fragmentation occurs.

    About indexes, again, for internal use, SQL Server manages data AND indexes (an index is just another form of data) at a page level.

     

    Thursday, March 22, 2007 6:23 PM
    Moderator
  • I think the key here is the little word 'in'. You asked about file allocation size which exists outside SQL Server.

     ffe_bob wrote:

    The fundamental unit of data storage in SQL Server is the page.

    Thursday, March 22, 2007 8:13 PM
    Moderator
  • INTERNAL to SQL Server -which has little to do with setting the OS file allocation size!

    On the OS level, the file has already been created and space allocated. SQL Server is only dealing with I/O within the allocated space. If SQL Server requires additional space from the OS, it requests an extent -64KB. How is uses that extend has NO effect on file allocation size. A file allocation size of 8KB would require 8 different actions on the part of the OS to give SQL Server the additional space. A file allocation size of 64KB woudl require 1 action on the part of the OS.

    Consider that on the OS level, the file allocation size is kind of like your organization deciding how frenquently to pay salaries. The organization (OS) divies up the total salary amount on that schedule. You, then are like SQL Server -after you get your allocation, how you handle divying up your money is up to you, but it does not impact the organization (OS). You can decide to pay your bills daily, weekly bi-weekly, monthly -it doesn't effect the organization.

    Thursday, March 22, 2007 11:00 PM
    Moderator

All replies

  • SQL Server grabs an 'extent' -which is 8 pages. It never takes just one page!

    So 64kb is the 'recommended' allocation unit size.

    Wednesday, March 21, 2007 12:10 AM
    Moderator
  • Actually, according to an article I just found on MSDN (Pages and Extents), SQL Server uses the page as the fundamental unit of IO, which I suspected all along.  Does anyone else have any thoughts?

     

    One thing: what if the table does not have enough data to fill an entire extent?

     

    Does the 'grab the entire extent' apply to indexes as well?

     

     

    Thursday, March 22, 2007 3:37 PM
  • Your original question seemed to ask about disk allocation which is an OS function -NOT how SQL Server internally manages data.

    There are two separate issues here.

    Externally, SQL Server interacts with the OS in 64K chucks -extents.

    Internally, SQL Server manages data on the page level.

    An extent can be 'mixed', that is one page for one table, another page for a different table, etc. Fragmentation occurs.

    About indexes, again, for internal use, SQL Server manages data AND indexes (an index is just another form of data) at a page level.

     

    Thursday, March 22, 2007 6:23 PM
    Moderator
  • Okay.  but here is the quote from Pages and Extents from MSDN

     

    "The fundamental unit of data storage in SQL Server is the page. The disk space allocated to a data file (.mdf or .ndf) in a database is logically divided into pages numbered contiguously from 0 to n. Disk I/O operations are performed at the page level. That is, SQL Server reads or writes whole data pages."

    Thursday, March 22, 2007 7:50 PM
  • I think the key here is the little word 'in'. You asked about file allocation size which exists outside SQL Server.

     ffe_bob wrote:

    The fundamental unit of data storage in SQL Server is the page.

    Thursday, March 22, 2007 8:13 PM
    Moderator
  • But it also says that "Disk I/O operations are performed at the page level."
    Thursday, March 22, 2007 8:16 PM
  • INTERNAL to SQL Server -which has little to do with setting the OS file allocation size!

    On the OS level, the file has already been created and space allocated. SQL Server is only dealing with I/O within the allocated space. If SQL Server requires additional space from the OS, it requests an extent -64KB. How is uses that extend has NO effect on file allocation size. A file allocation size of 8KB would require 8 different actions on the part of the OS to give SQL Server the additional space. A file allocation size of 64KB woudl require 1 action on the part of the OS.

    Consider that on the OS level, the file allocation size is kind of like your organization deciding how frenquently to pay salaries. The organization (OS) divies up the total salary amount on that schedule. You, then are like SQL Server -after you get your allocation, how you handle divying up your money is up to you, but it does not impact the organization (OS). You can decide to pay your bills daily, weekly bi-weekly, monthly -it doesn't effect the organization.

    Thursday, March 22, 2007 11:00 PM
    Moderator
  • Okay, I see your point.  However: does this mean that all physical disk I/O is at 64K?  In other words: how big is an actual physical write when SQL Server writes a page to physical disk?  If the checkpoint writes out a dirty page, does that write mean a 64k write; even if not all of the pages asscociated with the dirty page in the same extent are dirty? 

    Seems to me that this would help reduce index and table fragmentation.

    Would the allocation size apply to a spindle with only the TempDB on it? 

    What would be the ideal allocation size for a disk with only the transaction log on it? 

    Do you know of any publications that go into more detail on this topic?

    Friday, March 23, 2007 12:01 AM
  • And just to complicate the question further, does the Stripe size of an array hosted logical drive affect this either way?  The stripe size on my RAID 1+0 volume is 128KB...
    Friday, April 13, 2007 8:27 PM
  • If the array handled ONLY SQL Server, I think that I would use a 64KB stripe. If it is also handling OS file operations, then you have to decide which is more efficient.
    Saturday, April 14, 2007 4:18 AM
    Moderator
  • Could you help explain the following MSDN  article that was brought to my attention by some developers.  They seem to think it will help in DW setting.

     

    http://support.microsoft.com/default.aspx/kb/329526

     

    Continuing on with your analogy of the Organization and employee - would applying the setting give the employee a bigger paycheck while the organization monies stay the same, or is it the other way around. 

    Thursday, July 19, 2007 4:57 PM
  • The -E startup option increases the number of extents requested by SQL Server from the OS from one to four (64 kb vs. 256 kb) at a time. This can be especially useful when the database uses mulitple files/filegroups, and there are frequent large data loads -it can reduce the file fragmentation.

     

    As to the Organization/Employee analogy, it would be like changing the pay frequency from weekly to monthly. Overall, the pay is the same, the employees just gets larger 'chunks' to work with. The employee can now pay his/her 'rent' monthly instead of weekly, etc.

    Thursday, July 19, 2007 7:14 PM
    Moderator
  • I ran the SysInternals FileMon utility to observe SQL Server I/O activity at the file level.  I looked at roughly 5000 I/Os while running a test job.  90% of the  transaction log writes were  1KB in size and the remaining 10% were 512 bytes in size.  95% of the I/O to the data file consisted of 8KB chunks, 4% consisted of 64KB chunks, and the remaining 1% were either 16,  32, or 48 KB in size.

    It wouldn't surprise me if the ratios are application-dependent.
    Thursday, April 03, 2008 8:46 PM
  • Log reads can go up to 120K, writes up to 60K.

     

    Data is a little more complicated and is dependent on the operation, not application. Last I checked, index seeks are always 8K, Lazy writer can be any multiple of 8K up to 256K, backup can be a multiple of 64K up to 4MB, etc...

     

    Lots of testing done by various parties point to 64K being a good block/stripe size. Just make sure you remember to perform sector alignment.

     

     

    Some must reads for SQL Server IO

    http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/pdpliobp.mspx

     

    http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx

    http://www.microsoft.com/technet/prodtechnol/sql/2005/iobasics.mspx

     

     

    joe.

    Friday, April 04, 2008 1:02 AM