SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


RAID and Its impact on your SQL performance


RAID and Its impact on your SQL performance

Author
Message
Steve Jones
Steve Jones
SSC Guru
SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)

Group: Administrators
Points: 61803 Visits: 19099
Perry Whittle (11/24/2012)
The differences between RAID 1+0 and 0+1 are not academic. Let's just review that again, not academic!!!

If you do decide to deploy a 0+1 array for your mission critical data and you're unlucky enough to encounter a drive failure in each array you'll realise just how important the differences are.

As you're sitting there typing your resume you'll have time to reflect on the storage decision you made ;-)


+1

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
okbangas
okbangas
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1303 Visits: 1387
Raid 1+0 and 0+1 isn't even identical in performance, 1+0 performs better when the array is degraded.



Ole Kristian Velstadbråten Bangås - Virinco - Facebook - Twitter

Concatenating Row Values in Transact-SQL
GregoryAJackson
GregoryAJackson
Old Hand
Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)

Group: General Forum Members
Points: 314 Visits: 506
this article is just being republished.....sounds like I need to update it and publish again....

amazing what you learn from others eh?



GAJ

Gregory A Jackson MBA, CSM
Neha05
Neha05
Mr or Mrs. 500
Mr or Mrs. 500 (516 reputation)Mr or Mrs. 500 (516 reputation)Mr or Mrs. 500 (516 reputation)Mr or Mrs. 500 (516 reputation)Mr or Mrs. 500 (516 reputation)Mr or Mrs. 500 (516 reputation)Mr or Mrs. 500 (516 reputation)Mr or Mrs. 500 (516 reputation)

Group: General Forum Members
Points: 516 Visits: 60
Nice article.
Bill Talada
Bill Talada
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1139 Visits: 1992
I see serious problems with taking a RAID chart from IT guys and accepting it as being applicable in any way to SQL Server. SQL Server requires almost the exact opposite of what IT likes to implement for their file servers. The RAID chart says RAID 1 (mirror) is only "good" at both reading and writing when RAID 1 is the fastest for general SQL Server apps. What I've found is sql server needs a setup optimized for 8k random reads and writes and you can and should ignore virtually all other disk ratings. An SSD will perform between 10x and 100x better than mechanical disks. Hands down, RAID 1 will perform the best for SQL Server but most of my customers have chosen the worst setup of using RAID 5 and then blame me when their RAID is 4x slower than my laptop performance of no RAID.

Actually, having many RAID 1 arrays is the best solution. Having many separate I/O channels will dramatically speed up SQL Server.
tony rogerson
tony rogerson
SSC Rookie
SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)

Group: General Forum Members
Points: 38 Visits: 76
Only getting a couple of hundred IOps per 15K drive isn't really accurate because in reality we don't do a fully random workload across the entire disk.

I wrote the blog post here a few weeks ago because I got fed up with SAN / Storage engineers I couldn't possibly get the number of IOps I am getting out of the kit at one of my clients: http://dataidol.com/tonyrogerson/2014/04/07/maximum-iops-for-a-10k-or-15k-sas-hard-disk-drive-is-not-170/

For a 100% read work load of 64KiB on a 20GiB file on a pair of 300GB 15K disks in RAID 1 with a Queue Depth of 32 I easily get 2,281 IOps. For 8KiB I get 14K!

It really does depend on what you are doing, if you are doing sequential scans and you've performed the correct defrag maintenance on your tables, set them up correctly the disk head doesn't need to move too far so IOps goes up - dramatically.

Hope that helps.
Nadrek
Nadrek
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1864 Visits: 2726
I might recommend looking at my post earlier in this thread, as I list a table of SQLIO result from actual systems in a variety of RAID setups, both spindle and SSD disks, over different IO sizes and IO queue depths.

If you want to know the difference between RAIDx and RAIDy on your particular setup, I would very much recommend running an exhaustive SQLIO set over the metrics appropriate to your workload; it's not unknown to see a particular setup have one or another odd quirk for a specific data transfer type at a given RAID level (and/or stripe size); keep your firmware, drivers, SAN controllers, etc. updated.

In some older hardware, I sometimes saw a cap on writes that was very unexpected to me.
Karl Klingler
Karl Klingler
Right there with Babe
Right there with Babe (720 reputation)Right there with Babe (720 reputation)Right there with Babe (720 reputation)Right there with Babe (720 reputation)Right there with Babe (720 reputation)Right there with Babe (720 reputation)Right there with Babe (720 reputation)Right there with Babe (720 reputation)

Group: General Forum Members
Points: 720 Visits: 2100
Hello,

regarding Raid configuration you also have to count in the time/cost of a rebuild.

If your smallest unit is 1 mirrored drive the controller only needs to read and write the content of 1 drive to rebuild the mirror.

If you have let's say a 4 drive raid 5 configuration the controller has to read the content of 3 disks to rebuild the raid array.

Now consider a SAN box having a raid 5 or a raid 0 with say 20 drives...
Reading the content of 19 drives to rebuild the array - how long will the array be degraded? When will the next drive fail?

Best regards
karl
kwirz
kwirz
SSC-Enthusiastic
SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)

Group: General Forum Members
Points: 198 Visits: 136
Why is the formula to determine how long it takes to perform a single IOP = Seek Time + Rotational Latency?

Wouldn't you be seeking at the same time that you are rotating? Thereby making it the max of one or the other?
GregoryAJackson
GregoryAJackson
Old Hand
Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)

Group: General Forum Members
Points: 314 Visits: 506
No.
generally speaking the disk spins to the correct sector before the actuator moves the head to its location.


GAJ

Gregory A Jackson MBA, CSM
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search