July 22, 2009 at 6:29 am
Hello Everyone
What wiil be the best File Allocation Unit Size for storing sql server files?
Vivek
Vivek Shukla - MCTS SQL Server 2008
July 22, 2009 at 6:37 am
If you block size allocation for the drives, 64K for data files, 8K for log files, block aligned.
---------------------------------------------------------------------
July 22, 2009 at 6:52 am
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
July 22, 2009 at 7:11 am
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.
---------------------------------------------------------------------
July 22, 2009 at 8:12 am
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
July 22, 2009 at 8:52 am
I would not say huge but it is the MS recommendation and would help.
---------------------------------------------------------------------
July 27, 2009 at 2:14 am
Thanks george.
Vivek Shukla - MCTS SQL Server 2008
July 28, 2009 at 8:30 am
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
August 19, 2009 at 3:27 am
Should this make significant perf improvement?
Vivek
Vivek Shukla - MCTS SQL Server 2008
August 19, 2009 at 3:30 am
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
August 19, 2009 at 7:34 am
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
August 19, 2009 at 10:47 am
Thanks a lot for your help.
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