Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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

share|improve this question

migrated from stackoverflow.com Aug 26 '10 at 23:20

This question came from our site for professional and enthusiast programmers.

3 Answers 3

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.

share|improve this answer
    
The article that you linked states: "The performance question here is usually not one of correlation per the formula, but whether the cluster size is the NTFS default of 4,096 bytes or has been explicitly defined at 64 KB, which is a best practice for SQL Server." That seems fairly definitive to me. –  Greg Askew Aug 27 '10 at 1:55

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.

share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

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