Sql-server – 64K Cluster size for SQL Server Log Files

sqlsql serversql-server-2005

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

Best Answer

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.

Related Topic