SQL Server IO patterns and RAID levels

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 

Read More

Notes on Performance Tuning very large databases for OLTP