Best File Allocation Unit Size

Question

Post reply

Best File Allocation Unit Size

Vivek Shukla

SSCrazy

Points: 2146
More actions
July 22, 2009 at 6:29 am

#385028

Hello Everyone
What wiil be the best File Allocation Unit Size for storing sql server files?
Vivek
Vivek Shukla - MCTS SQL Server 2008

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

george sibbald SSC Guru Points: 104210 More actions · Answer 1

If you block size allocation for the drives, 64K for data files, 8K for log files, block aligned.

---------------------------------------------------------------------

Vivek Shukla SSCrazy Points: 2146 More actions · Answer 2

Please excuse me if i may sound ignorant here but when you say "if you block size allocation drives" - do we actually have an option there? If so then is it better to do so or not?

And i take it that sql server reads/write data to datafile in 64k and to log file in 8k.

Many thanks.

Vivek

Vivek Shukla - MCTS SQL Server 2008

george sibbald SSC Guru Points: 104210 More actions · Answer 3

vivek (7/22/2009)
Please excuse me if i may sound ignorant here but when you say "if you block size allocation drives" - do we actually have an option there? If so then is it better to do so or not?

Apologies, I totally missed out a word, I meant to say 'If you mean block size allocation for the drives '

And i take it that sql server reads/write data to datafile in 64k and to log file in 8k.

yes.

---------------------------------------------------------------------

Vivek Shukla SSCrazy Points: 2146 More actions · Answer 4

Obviously, i won't be able to cahnge existing file allocation unit size. As when i check the existing file allocation unit size it shows following.

Version : 3.1

Number Sectors : 0x0000000022ec3977

Total Clusters : 0x00000000045d872e

Free Clusters : 0x00000000043ff35c

Total Reserved : 0x0000000000000000

Bytes Per Sector : 512

Bytes Per Cluster : 4096

Bytes Per FileRecord Segment : 1024

Clusters Per FileRecord Segment : 0

Mft Valid Data Length : 0x000000000173c000

Mft Start Lcn : 0x00000000000c0000

Mft2 Start Lcn : 0x00000000022ec397

Mft Zone Start : 0x00000000000c16e0

Mft Zone End : 0x000000000097b100

Do you think it will make huge performance difference if i increase this to 64K?

Thanks a lot for your help.

Vivek

Vivek Shukla - MCTS SQL Server 2008

george sibbald SSC Guru Points: 104210 More actions · Answer 5

I would not say huge but it is the MS recommendation and would help.

---------------------------------------------------------------------

Vivek Shukla SSCrazy Points: 2146 More actions · Answer 6

Thanks george.

Vivek Shukla - MCTS SQL Server 2008

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 7

Probably more important than cluster size is sector alignment of the partition itself. Review all the stuff here for details: http://blogs.msdn.com/jimmymay/archive/2008/10/14/disk-partition-alignment-for-sql-server-slide-deck.aspx

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Vivek Shukla SSCrazy Points: 2146 More actions · Answer 8

Should this make significant perf improvement?

Vivek

Vivek Shukla - MCTS SQL Server 2008

Vivek Shukla SSCrazy Points: 2146 More actions · Answer 9

Hi George

george sibbald (7/22/2009)
If you block size allocation for the drives, 64K for data files, 8K for log files, block aligned.

Would it be better to have 8k for tempdb files or i should have them on 64K?

Vivek

Vivek Shukla - MCTS SQL Server 2008

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 10

1) failure to do disk partition alignment can rob an IO subsystem of double-digit percentage throughput capability. In the worst case (pure 64K random IO) you can gain 100% throughput by aligning because unaligned you are doing 2 IOs for every request.

2) My recollection is 64K cluster size is best practice for tlog partitions and data partitions.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Vivek Shukla SSCrazy Points: 2146 More actions · Answer 11

Thanks a lot for your help.

Vivek

Vivek Shukla - MCTS SQL Server 2008

Best File Allocation Unit Size

Cookies on SQLServerCentral