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
GregoryAJackson
GregoryAJackson
Mr or Mrs. 500
Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)

Group: General Forum Members
Points: 592 Visits: 506
sqlquery-101401 (5/7/2012)
Thanks , Yes i have couple of servers on SAN , what other utilities you refer?


I have never administred a SAN\NAS so i've always had to go to our IT Team or the Systems Engineers to obtain this data.



GAJ

Gregory A Jackson MBA, CSM
paul.knibbs
paul.knibbs
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4150 Visits: 6240
I'm a little confused. Surely the Disk Transfers/second counter in Performance Monitor will already be limited by the hard disk subsystem you have installed, so you can't use it to determine if you need to upgrade or not? Ermm
yazalpizar_
yazalpizar_
Old Hand
Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)

Group: General Forum Members
Points: 302 Visits: 626
Steve Jones - SSC Editor (5/1/2012)
yazalpizar_ (5/1/2012)
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.


Make sure you have good backups and be careful here. When SSDs die, they die ,and you may or may not get good notice.

A quick read: http://www.codinghorror.com/blog/2011/05/the-hot-crazy-solid-state-drive-scale.html


:-D Very good article that one on CodingHorror. Sorry for not replying, was no aware of the thread and it has grown a lot with useful information. I indeed have backups of everything on external hard drive. At the office we finally didn't change the HD on the servers, is not a priority for our manager for now.

PS: failed the MCITP sql server 2008 exam yesterday... Sick need to work harder on it
Brian Souder
Brian Souder
Valued Member
Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)

Group: General Forum Members
Points: 60 Visits: 22
Great Article - very helpful. Especially calculating the IOPS.
Toby Harman
Toby Harman
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1173 Visits: 670
Good article and some excellent comments

Not much in here on RAID 50, which is rapidly becoming one of my favourite RAID levels for Data and Backups

Logs and Tempdb are definitely destined for RAID 10 (1+0, not 0+1 as has been pointed out), but RAID50 gets around a lot of the write penalty associated with RAID5 by spreading the writes out across multiple arrays.

As an example, if you have a 5 disk RAID5 array which can handle 100 IOPS, then striping this with an identical RAID5 array gives you 200 IOPS (in a perfect world, realistically probably only 190!).

One area which isn't discussed in here, and can have an enormous impact on RAID performance is stripe size. Ensuring that your stripe sizes are multiples of each other as you stack the RAID levels is crucial to maintaining performance. Ensuring that the OS disk cluster size is a multiple will also help things along nicely.
GregoryAJackson
GregoryAJackson
Mr or Mrs. 500
Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)

Group: General Forum Members
Points: 592 Visits: 506
Excellent info....

thanks


GAJ

Gregory A Jackson MBA, CSM
okbangas
okbangas
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2579 Visits: 1387
It appears that this article has been changed (or at least republished). Perry Whittle's comment should have been taken into consideration, the difference between RAID 1+0 and 0+1 is indeed not academic. RAID 1+0 is a stripe of mirrors, if one disk fails, that mirror has only one disk left, but all the other mirrors may still lose a disk each, and your RAID array is still available. RAID 0+1 is a mirror of stripes, if one disk fails, that whole stripe is available, and you're left with a singe stripe. If one of the disks in this stripe fails, your RAID array is not available anymore.

As far as I understand it, the physical storage of RAID 1+0 and 0+1 are identical, the difference lays in how these data are used logically (or internally if you wish) by the RAID controller.



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

Concatenating Row Values in Transact-SQL
Ray Herring
Ray Herring
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1557 Visits: 607
btd (5/1/2012)
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.........



My personal recollection is that the "I" did stand for inexpensive. Prior to the days of cheap, large, fast, hard disks one of the key metrics was MTBF (Mean Time Between Failures). In those days a lot of the data was stored "off-line" on tape. When a job ran the operator would mount the tape and the data would be transfered to disk as working storage (sort of a cache).
Disk drive failures on early main frame and mini computers were fairly common. Disks with high MTBF were very expensive. Early RAID configurations used the relatively inexepensive Winchester architecture drives to construct fault-tolerant solutions without breaking the bank on expensive high MTBF drives. At a time when CPU cycles were measured in milliseconds, the added disk write penalties did not seem so bad.
GregoryAJackson
GregoryAJackson
Mr or Mrs. 500
Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)

Group: General Forum Members
Points: 592 Visits: 506
T.C.
I'm not sure I understand your question completely.....care to rephrase or expand?


GAJ

Gregory A Jackson MBA, CSM
Perry Whittle
Perry Whittle
SSC Guru
SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)

Group: General Forum Members
Points: 52683 Visits: 17672
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 ;-)

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

"Ya can't make an omelette without breaking just a few eggs" ;-)
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