Tell me more ×
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.
up vote 3 down vote favorite

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

1 Answer

up vote 4 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

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.