Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

RAID and Its impact on your SQL performance Expand / Collapse
Author
Message
Posted Monday, April 30, 2012 10:56 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 1:41 PM
Points: 110, Visits: 497
Comments posted to this topic are about the item RAID and Its impact on your SQL performance

Gregory A Jackson MBA, CSM
Post #1292935
Posted Tuesday, May 1, 2012 12:55 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:34 AM
Points: 6,518, Visits: 14,039
Nice article, incidentally RAID 1+0 and 0+1 are not identical. As 0+1 is essentially 2 stripes the loss of 1 disk in each portion of the array is catastrophic. 1+0 can lose up to half the disks in the array as long as no mirrored pair fail.

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs"
Post #1292948
Posted Tuesday, May 1, 2012 1:36 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, September 26, 2014 8:15 AM
Points: 45, Visits: 341
A questoin; out of curiosity:
You quoted some PerfMon stats on your production environment.
Given your analysis, would I be correct in assuming your produciton environment already runs an array of > 100 disks?
In fact given your preference for RAID 1+0, are you running > 200 disks?
Post #1292958
Posted Tuesday, May 1, 2012 1:41 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 7:15 AM
Points: 144, Visits: 501
With the proliferation of SSD disk and its costs going down, some RAID levels with low write performances are no longer so low. Put an SSD in your life. I've done it, both home, laptop and work. Is the best money can buy right now in order to increase performance several degrees. We are planning to do it also on our local servers, first on the test server and then on the main one if evertyhing is ok.
Post #1292960
Posted Tuesday, May 1, 2012 1:58 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, February 20, 2013 6:42 AM
Points: 1, Visits: 15
I do agree a well put together document BUT I wish we could get away from the interpretation of RAID. 'inexpensive discs'... The 'I' certainly did not mean inexpensive discs when RAID first came out way back when...'Independent' ... why does the computer world keep changing things when it does not need to, we have plenty of change without it...... and as for 'tables' instead of files.........

Post #1292968
Posted Tuesday, May 1, 2012 2:13 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 30, 2013 12:44 AM
Points: 9, Visits: 38
The IOPs calculation for the disk needs further explanation, there are a lot more factors involved in how many IOps you can get out of a physical disk, for example in a comparison between a commodity 7.2K disk (density 1TB per platter) compared with a enterprise 15K disk (density 150GB per platter) for 100% sequential read from a 40giB file located on the outer edge of the disk you can easily get 20K-30K IOps at an average latency of less than a millisecond.

More disks in a RAID means the file is split up over more disks thus giving implicity shortstroking.

There is also a massive difference in where the file is located on the disk - sequential read from the outer edge compared to the inner can differ significantly in throughput with a slight increase in latency as well.

RAID doesn't quite go away with SSD, you'd not use RAID 5 so you'd just use RAID 1 or RAID 10, you'd only RAID 10 or RAID 0 to get round say the single channel bandwidth (approx 550MiB/sec) you get with SAS 600 and SATA 3 but also if you need more storage than a single SSD can give.

Tony.

Post #1292973
Posted Tuesday, May 1, 2012 3:57 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, August 22, 2013 9:13 AM
Points: 496, Visits: 601
Very good summary article with some condensed information that's useful to know - thanks! Although I'm familiar with RAID and the effect on SQL Server I didn't know that read penalties were far lower than write penalties - not that it matters, I suppose, unless we're talking about read-only databases. I didn't know there was a direct correlation between disk transfers/sec in perfmon and IOPS rates but thinking about it, it does make sense!

Would have been nice to explore the topic of 1) virtualisation and 2) SANs and how the IOPS rates differ when using these different technologies. Any more articles planned along these lines?


---

Note to developers:
CAST(SUBSTRING(CAST(FLOOR(NULLIF(ISNULL(COALESCE(1,NULL),NULL),NULL)) AS CHAR(1)),1,1) AS INT) == 1
So why complicate your code AND MAKE MY JOB HARDER??!

Want to get the best help? Click here http://www.sqlservercentral.com/articles/Best+Practices/61537/ (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.
Post #1293003
Posted Tuesday, May 1, 2012 4:16 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 5:56 AM
Points: 338, Visits: 1,432
We use SAN with massive amounts of memory installed on them. This seems to be a whole different kettle of fish. The bottlenecks move from the disk to other areas like the throughput of ports on the SAN being able to cope with the volume of data and the SAN's CPUs.
Post #1293006
Posted Tuesday, May 1, 2012 5:52 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, July 6, 2014 8:37 AM
Points: 3, Visits: 48
I have a question. Everyone talks about Raid levels and performance but what about performance in terms of the number of logical luns? Is it better from a pure performance stand point to show say 8 LUNS to Windows Server as oppsed to one large LUN? I know it is bound by the number of actual spindles but I was under the impression that more LUNS is better. I know it is harder from a maintainane stand point. But since Windows Server see it as a physical disk. can it more efficiently utilize the large SAS pipe. My raid setup has 5 drive in a RAID1 setup. They are 15,000 RPM
600 gig drive with a SAS 6g pipe. I show one Logical LUN would I get better performance if I showed 4 LUNS. Can Windows have more outstanding requests?

Thanks
Pleas help.
Post #1293054
Posted Tuesday, May 1, 2012 5:55 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, October 23, 2014 8:02 AM
Points: 68, Visits: 426
Greg - good post, but the explanation of IOPs is not correct. You write:

In basic terms IOPS is a measurement for the amount of time, on average, required to service an input or ouput request (a read or a write).


That's the definition of total storage latency, not IOPs. (I mention total storage latency because your post also mentions rotational latency.)

I like to explain it using FedEx. Let's pretend you're sitting at work, and you want to find out how fast FedEx delivers packages. You seal up one envelope, write New York City on the label, and call for a FedEx pickup. The driver arrives, and the next morning, you get confirmation that your envelope arrived in New York City.

That's one operation per day, one package delivered - however, that doesn't mean FedEx can only deliver one package per day. To really test FedEx, you have to put together LOTS of packages and envelopes, then call for the truck. One package still takes the same amount of time to be delivered, but they can handle many at once.

IOPs = the number of packages you can move.
Latency = how long it takes to move each package.

The size/speed/quantity of the truck also come into play to make the total storage picture, plus the size/speed/quantity of the truck on the other end, and the office's size on the other end - I talk through that in my day-long storage classes.

I'd also disagree strongly about your recommendation for RAID 5 for backups. I see a lot of shops that use a shared RAID 5 array for backups from multiple servers, and the backup times are unacceptable to the business. Simply by switching that target to RAID 10, I've seen backups drop from >4 hours to <30 minutes, for example, without raising the spindle count. Granted, capacity drops, so you can keep less backups online and they have to go to tape quicker, but if your goal is backup performance, RAID 5 won't cut it.



Post #1293055
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse