Sql-server – Help me choose a RAID level combination for a SQL Server 2008 instance

best practicesdisk-structuresperformancesql server

I am going to rebuild one IBM 3400 server from scratch. This server is dedicated to a SQL Server 2008 instance running on Windows 2008 R2.

I am going to make new RAID configuration. I have 6 SCSI 73 GB drives inside the machine and an IBM ServerRAID 8K controler. What would be a good way to set the RAID levels? Should I have two, three or one field on my controler?

I am considering to make one of following solutions:

Use all the disk and make a RAID 10 pool.
Use 4 disks for a RAID 1e pool and use it to store the database data and OS, and use the other 2 disks in a RAID 0 pool and use that to store the database logs.
Some other combination.

Is a larger stripe unit size better?

This server will be a subscriber to a replicated database. Its primary task is going to be reporting and data retrieval, with only the replication agent making writes. The size of the database is around 90 GB.

Best Answer

I vote for option 1. Bear in mind that RAID 0 means "no protection" - do your logs matter? (yes they do).

It also has the benefit of simplicity

The SQL Server docs say:

For optimized I/O parallelism, use 64 KB or 256 KB stripe size.

But it is usually good to go with the controller default IMO

Related Solutions

Sql-server – How to quantify TEMPDB usage in relation to other databases hosted on same hardware

Scott,

Check this statement out:

SELECT
    [Database] = DB_NAME ( qt.dbid ),
    [Execution Count] = SUM ( qs.execution_count ),
    [Total Execution Time] = SUM (qs.total_elapsed_time / 1000000.0 ),
    [Total CPU Consumption] = SUM ( qs.total_worker_time ),
    [Total Reads] = SUM ( qs.total_physical_reads ),
    [Total Writes] = SUM ( qs.total_logical_writes ),
    [Average Execution Time] = SUM ( qs.total_elapsed_time / qs.execution_count / 1000000.0 ),
    [Average CPU Consumption] = SUM ( qs.total_worker_time / qs.execution_count / 1000000.0 ),
    [Average Reads] = SUM ( qs.total_physical_reads / qs.execution_count ),
    [Average Writes] = SUM ( qs.total_logical_writes / qs.execution_count )
FROM sys.dm_exec_query_stats qs     
CROSS APPLY sys.dm_exec_sql_text ( qs.sql_handle ) qt     
LEFT JOIN sys.databases o ON qt.objectid = qt.dbid
GROUP BY qt.dbid

This should give you all of the information you need. If all you're interested in is disk usage just keep the total reads/writes and average reads/writes.

Sql-server – Optimal drive configuration for SQL Server 2008R2

Variants of this question come up semi-regularly:

There are also occasional bun fights about the data/log separation "best practice".

Without more detailed analysis of what this server is doing, the same advice applies as given previously.

RAID 1 for OS
RAID 10 (6 disk) for data/logs/tempdb

There is rarely any point in a split with so few spindles available. A single array with a larger IOPs capacity will typically soak up the lumps and bumps of your workload better than 2 smaller arrays.

One variant that can be worth testing is putting tempdb on the OS drive. Only do so if you have a representative workload that you can replay repeatedly, to ensure a fair comparison of the configuration. If you go for this arrangement in production make sure tempdb growth is restricted so you don't inadvertently consume all free space on the OS drive.

Given that your OS drives are 7200RPM coasters, I'd be surprised if the tempdb on OS drive config bore any benefit.

Best Answer

Related Solutions

Sql-server – How to quantify TEMPDB usage in relation to other databases hosted on same hardware

Sql-server – Optimal drive configuration for SQL Server 2008R2

Related Question