I was wondering if we put the SQL Server Database Log file and tempdb on RAID 1, should be format it with 64K cluster for better performance?
Currently the database and log file are on RAID5 which I think is the worse for database log if you are doing a lot of inserts as it double writes.
This is for SQL Server 2005
RAID 1 will generally give better read performance but worse write performance, so I would not use it for heavily written logs and TempDB. Ideally, you should go with RAID 10 for SQL Server when possible. See: RAID Levels and SQL Server.
As for your cluster size question, see Disk Partition Alignment Best Practices for SQL Server for an excellent discussion of all the considerations that should be taken into account. The article says it's for SQL 2008 but it is equally relevant for 2005. Here's the most important take away from the article:
There are two correlations which when satisfied are a fundamental precondition for optimal disk I/O performance. The results of the following calculations must result in an integer value:
Partition_Offset ÷ Stripe_Unit_Size
Stripe_Unit_Size ÷ File_Allocation_Unit_Size
Of the two, the first is by far the most important for optimal performance. The following demonstrates a common misalignment scenario: Given a starting partition offset for 32,256 bytes (31.5 KB) and stripe unit size of 65,536 bytes (64 KB), the result is 0.4921875. This is not an integer; therefore the offset & strip unit size are not correlated. This is consistent with misalignment.
However, a starting partition offset of 1,048,576 bytes (1 MB) and a stripe unit size of 65,536 bytes produces a result of exactly 8, an exact integer, which is consistent with alignment.
It depends (like everything else) on your database. If you're mostly doing smaller writes then you should use an allocation unit that matches the stripe size, and make sure the partition offset is a multiple of the stripe size. In general, larger strips are faster.
RAID 1 and RAID 10 are almost always faster at small writes than RAID 5 (RAID 5 has to read-write ever time you write something smaller than the stripe size, when the write is a whole stripe, there's less penalty as the disk with the parity is the only one that has to read-write and write-back buffers will take care of it).
Joe's advice is good; I'd go with that unless you've got a good reason not to.
64K. And the logs need to be on dedicated spindles. If the sequential log writing is interrupted with other activity, this will adversely impact performance. And do not put tempdb on the same spindles as the logs if you need to optimize performance.
