SQL Server IO patterns and RAID levels

25 June,2011 by Tom Collins

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


Author: Tom Collins (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on SQL Server IO patterns and RAID levels


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer