To analyse SQL Server IO requirements and decide on RAID levels for different applications is complicated – with some expensive decisions. As the DBA I have to make recommendations with analytics to justify decisions.

Firstly, my approach is end-to-end – look at everything from tuning, disk distribution, memory,cache size,IO subsystem.

In the review process – say example, RAID 5 v RAID 10 configurations. Matching I\O patterns is the basis of the review in identifying the RAID configurations. Your acceptance levels maybe Data files < 3-4 ms and Log Files < 2 ms.

As part of the review process – we’re testing out internally and in labs with vendors the following scenarios – with some target acceptance levels is a good starting point.

Operation | Sequestial\Random | Write/Read
--------------------------------------------------
Bulk Insert    Sequential    Write
Read Ahead    Sequential    Read
Backup    Sequential    read\Write
Restore    Sequential    Read\Write
Reindex (read)    Sequential    read
Reindex(write)    Sequential    Write
OLTP -Log    Sequential    Write
OLTP-data    Random    Rea/Write

SQL Server IO patterns and RAID levels is critical to performance but must be balanced against budgets

Republished from http://www.sqlserver-dba.com.