SQL Server Performance

64k cluster size for data and log file partition

Discussion in 'Performance Tuning for Hardware Configurations' started by kenc, Oct 11, 2007.

  1. kenc New Member

    Hi,
    I've read somewhere that it's recommended to format the drive where you want to place the database data and log files with a 64k cluster size to have better i/o performance because of it's 1 to 1 corelation. is that true?
    Ken

  2. thomas New Member

    Supposedly, because SQL Server uses extents as a unit of I/O, and 1 extent is 8 * 8K pages = 64k.

    Also aligning the boundaries is important, I don't tend to get too involved in this kind of stuff because I have hardware men to do all this stuff. We had a new HP EVA SAN a while back though and did a bunch of testing on boundary alignment and found that it didn't make much difference. I believe 64k block size is a genuine recommendation though.
  3. alzdba Member

    We also format it with 64k clusster size because it is certainly (even a little) one form of filefragmentation you can do something about. [Y]
  4. satya Moderator

    Is it a SAN?
    What kind of RAID is that?
  5. kenc New Member

    thanks guys for answering
    as for satya's question, it's on a SAN with a raid 10 config. is there a difference if it's on a san or not? and also is there a difference what raid it's on? i know that raid 1 or raid 10 is the recommended for I/O performance if you have heavy read and write and raid 5 is only good if it's does alot of read and not write.
    Ken

  6. satya Moderator

    Good, no issues I just wanted to know whether it is a SAN or not.
    If you have lot of reads then better go with RAID5, as RAID10 is your I/O throughput and it should be very fast, since RAID 10 uses all of the drives in the array, to gain higher I/O rates, the more drives in the array will increase performance.
    Overall this purely depends on how quickly the transactions are written to disk and how your queires are optimized.
  7. kenc New Member

    is 64k only for the data partition or both data and log partition. i was chatting with someone and hey told me that the log is on a different size.
  8. satya Moderator

    In my expeirence i t is not a rule of thumb that if you change cluster size the performance will be increased.

    You must consider the options such as frequent transaction log backup and writes to the data file will have better balances the trade off between disk defragmentation due to smaller cluster size and wasted space due to a large cluster size.
  9. kenc New Member

    Hi Satya,
    Space will not be an issue. It's more of a performance that i'm worried about. This is becuase the database will be housing legal documents eg. .doc, .pdf, .rtf etc etc. there will be alot of read and write to and from the disk and also as for transaction log backup, it will be once every hour. also there will be about 500 concurrent users on the db at any given time. I've read through the tips and tricks on this site about hardware tuning to optimize the sql server performance, it just that i would like any other idea that might help and also if the 64k cluster size will give me even a 0.5% increase in performance i'll implement it. Also the application and db will be provided to us by a third party vendor, so the idea of fiddling around with indexes and also queries to optimize performance is out of the question due to the vendor's clause of "no modifications allowed to the application or database else support agreement will be void" :(

  10. satya Moderator

    Ah, third party tools shenanigans.
    In this case you have to trial and error by adjusting the relevant cluster size if the database is used extensively. In any case don't forget to test and also suggest relevant indexes changes to the vendor too, if they listen to you.
  11. kenc New Member

    Hi Satya,
    Thanks for the advice. I'll do that. This vendor has a history of not putting primary key constraint on any of their tables, which is why now in the SRS, I've made sure i've added a Requirements to have primary keys and indexes.
    Ken

Share This Page