Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I know the common recommendation for the data volume in SQL Server is to use 64 KB blocks/stripes, since the I/O is typically done by entire extents. I can't find any good information regarding log file I/O, however.

I've been watching I/O activity in Process Monitor for a little while, and it appears that the log file I/O sizes range from 512 bytes to just under 64 KB. I'm guessing this is dependent on the size of the transaction being logged, and large ones get split up using multiple ~64 KB writes.

So, assuming I have the partition aligned to the RAID stripes, would it be safe to assume that 64 KB blocks/stripes will yield the best performance, all other things being equal? I would expect that the smaller transactions, i.e. the ones with 512-byte writes, aren't coming heavily enough for the large block size penalty to have a significant impact, whereas the much larger transactions writing lots of 64 KB blocks in rapid succession would be more important to tune for.

share|improve this question
add comment

2 Answers 2

up vote 5 down vote accepted

64k blocks are usually best. Log writes should actually max out at about 60k, but at 64k you are aligned with the size of the physical block on the disk.

share|improve this answer
    
Very interesting. I rebuilt the volume (the partition was misaligned, the stripe size was wrong, and it was using 4KB clusters), but saw very little change in performance according to SQLIO's results. I'm guessing the controller write cache was masking some of the slowness. But if/when the battery on that kicks the bucket, at least performance won't degrade as much. –  db2 Dec 6 '11 at 15:26
add comment

Keep in mind there are two different types of alignment: Block Size and Offset. Both of these alignment types focus on the differences between OS partitioning/formatting and underlying storage array partitioning/formatting.

A disk which has block alignment means the OS and the underlying storage array are formatted to the same blocksizes. There is some performance gains which can be made here but generally speaking these gains are negligible. Microsoft SQL recommends log and data drives be foramtted at 64k blocks.

Offset alignment is the silent killer for performance. If offsets are not configured to align properly then a single block at the OS layer could overlap two or more blocks at the storage layer. When the OS requests that single block it triggers the storage layer to pull and return all storage layer blocks. If you correct a misaligned offset, you will see a performance increase.

enter image description here

This image shows how at the various levels disk alignment could be off to include OS, vmware layer, and storage array. If you're running on a physical server, then you'd only focus on the first and last line in the chart.

See also:

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.