File Allocation Unit size

ffe_bob


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

Arnie Rowland

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

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

ffe_bob

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

 

 

Arnie Rowland

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.

ffe_bob

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."

Arnie Rowland

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.

ffe_bob

But it also says that "Disk I/O operations are performed at the page level."

Arnie Rowland

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.

ffe_bob

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

Brad Turner

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...

Arnie Rowland

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.

pmacdonald

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.

Arnie Rowland

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.