InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comFri, 24 May 2013 04:28:19 GMT20http://www.sqlservercentral.com/Forums/Topic1382555-1550-1.aspxI usually use 256KB for data and backup drives and 64KB for log filesFri, 16 Nov 2012 15:25:46 GMTPerry Whittlehttp://www.sqlservercentral.com/Forums/Topic1382555-1550-1.aspx[quote][b]Perry Whittle (11/16/2012)[/b][hr]the stripe size specifically, the optimal stripe sizes for SQL Server are 64KB and 256KB.As i mentioned above you need to vary your tests as data files are typically 8KB random heavy.[/quote]Considering a situation where the logs, data and tempdb files are on seperate drives, what stripe sizes are suggested for each?Fri, 16 Nov 2012 09:32:08 GMTscogebhttp://www.sqlservercentral.com/Forums/Topic1382555-1550-1.aspxVerified that the strip-size is 64k.I think for the next few days I am going to try running it as a RAID 5, then move things around, redo the array to RAID 10 and compare the results, this way I can more effectively gauge performance.I'll post back then with some resultsFri, 16 Nov 2012 07:51:40 GMTMyDoggieJessiehttp://www.sqlservercentral.com/Forums/Topic1382555-1550-1.aspxthe stripe size specifically, the optimal stripe sizes for SQL Server are 64KB and 256KB.As i mentioned above you need to vary your tests as data files are typically 8KB random heavy.Fri, 16 Nov 2012 06:28:30 GMTPerry Whittlehttp://www.sqlservercentral.com/Forums/Topic1382555-1550-1.aspx[b]8KB random read and write - 90% read, 10% write ratio[/b]TOT IO/sec: 2245.5Total MB/sec: 17.6Average IO Response Time: 7.22MAX IO Response 153.3CPU: 30.9[b]64KB sequential read and write - 90% read, 10% write ratio[/b]TOT IO/sec: 6337.5Total MB/sec: 396.1Average IO Response Time: 2.52MAX IO Response 177.4CPU: 36.8[b]up to 128KB sequential write - 90% read, 10% write ratio[/b]TOT IO/sec: 3598Total MB/sec: 440.7Average IO Response Time: 4.75MAX IO Response 206.7CPU: 14.3[b]up 256KB sequential read - 100% read ratio[/b]TOT IO/sec: 4479.3Total MB/sec:1118.4Average IO Response Time: 3.59MAX IO Response 12.7CPU: 22.81Fri, 16 Nov 2012 06:26:19 GMTMyDoggieJessiehttp://www.sqlservercentral.com/Forums/Topic1382555-1550-1.aspx[quote][b]Perry Whittle (11/16/2012)[/b][hr]Despite the advances in RAID5 i would expect a well defined RAID10 array to have superior write performance. I'm sure others will jump in here.[/quote]You've mentioned this before "well defined" - how can there be a well vs. poorly defined RAID? What factors would need to be considered to ensure our Tech Services people have configured it properly?Regarding the write performance, RAID 10 will always beat out a RAID 5 due to the way the parity needs to be kept across all the drives, the more spindles, the more overhead needed to write to each drive, whereas in the 10, there's less drive heads to spin up that need writing to - isn't this correct?Fri, 16 Nov 2012 06:14:39 GMTMyDoggieJessiehttp://www.sqlservercentral.com/Forums/Topic1382555-1550-1.aspx[quote][b]MyDoggieJessie (11/16/2012)[/b][hr]Again as mentioned above this storage array will only be used for data and index files (NO log or tempdb files)[/quote]in that case for data and index files you'll definitely need to test these8KB random read and write64KB sequential read and writeup to 128KB sequential writeup to256KB sequential readFri, 16 Nov 2012 06:08:22 GMTPerry Whittlehttp://www.sqlservercentral.com/Forums/Topic1382555-1550-1.aspxAgain as mentioned above this storage array will only be used for data and index files (NO log or tempdb files)Fri, 16 Nov 2012 05:45:19 GMTMyDoggieJessiehttp://www.sqlservercentral.com/Forums/Topic1382555-1550-1.aspxyou need to benchmark for a little more than 4KB sequential even for the T-log. Test for the following8KB random read and write4-60KB sequential write64KB sequential read and writeup to 128KB sequential writeup to256KB sequential readDespite the advances in RAID5 i would expect a well defined RAID10 array to have superior write performance. I'm sure others will jump in here.Fri, 16 Nov 2012 05:38:28 GMTPerry Whittlehttp://www.sqlservercentral.com/Forums/Topic1382555-1550-1.aspxSorry, I did mean SQLIO - but for these tests I am only using IOMeter.Results below for RAID 5[b]MAX IOPS[/b] - uses 4KB transfer request size, 100% reads, 100% sequential dist.:TOT IO/sec: 121,432Total MB/sec: 479Average IO Response Time: 0.13MAX IO Response 1.85CPU: 26.5[b]MAXIOPS with 90% read, 10% write ratio[/b] - uses 4KB transfer request size, 90% reads, 10% sequential dist.:TOT IO/sec: 7000Total MB/sec: 90Average IO Response Time: 2.28MAX IO Response 37.2CPU: 27.6I am very surprised with the outcome of the RAID 10, as I thought the performance would be slower:[b]MAX IOPS[/b] - uses 4KB transfer request size, 100% reads, 100% sequential dist.:TOT IO/sec: 69,445Total MB/sec: 271Average IO Response Time: 0.23MAX IO Response 10.77CPU: 13.08[b]MAXIOPS with 90% read, 10% write ratio[/b] - uses 4KB transfer request size, 90% reads, 10% sequential dist.:TOT IO/sec: [b]23,565[/b]Total MB/sec: 92Average IO Response Time: [b]0.68[/b]MAX IO Response 49.9CPU: 3.02So it would seem to me that in a RAID5, the more writes you have, you lose IOPS due to the performance hit of maintaining parity across the disks, the [b]main benefits[/b] of a RAID 5 would be if and only if you could be sure the writes were kept to an absolute [u]minimum[/u] or for a read only file altogether.At our company, we will have replicated data being written to this new storage array (along with other minor writes from custom tables used for reporting, etc)...so I would think that we wouls ee the best performance in keeping this new array a RAID 10.Would you agree with this?Thu, 15 Nov 2012 19:59:55 GMTMyDoggieJessiehttp://www.sqlservercentral.com/Forums/Topic1382555-1550-1.aspx[quote][b]MyDoggieJessie (11/15/2012)[/b][hr]Running some tests using IOMeter (preferred the GUI to this one over SQLIOsim)[/quote]Errm, for benchmarking your I\O capacity (which is what you're doing here) you need to be using SQLIO or IOMeter.SQLIOsim is specifically for immitating SQL server I\O patterns to stress test your storage solution ;-)Thu, 15 Nov 2012 15:42:42 GMTPerry Whittlehttp://www.sqlservercentral.com/Forums/Topic1382555-1550-1.aspxRunning some tests using IOMeter (preferred the GUI to this one over SQLIOsim) and using some of the predefined options here's the results:ON a 12-disk, RAID 5, 1 hot-swappable spare, this uses 1 worker, 40,000,000 sectors (20GB file)[b]MAX IOPS[/b] - uses 4KB transfer request size, 100% reads, 100% sequential dist.:TOT IO/sec: 121,432Total MB/sec: 479Average IO Response Time: 0.13MAX IO Response 1.85CPU: 26.5[b]MAX IOPS[/b] - uses 4KB transfer request size, 100% reads, 100% sequential dist.:TOT IO/sec: 58,294Total MB/sec: 227Average IO Response Time: 0.27MAX IO Response 86.9CPU: 12.1[b]MAXIOPS with 90% read, 10% write ratio[/b] - uses 4KB transfer request size, 90% reads, 10% sequential dist.:TOT IO/sec: 7000Total MB/sec: 90Average IO Response Time: 2.28MAX IO Response 37.2CPU: 27.6I don't really know if this is good or not, when i ran it against on of the other RAID 10 arrays the numbers were much slower...so i assume this is good - Is this considered ok?Thu, 15 Nov 2012 15:32:58 GMTMyDoggieJessiehttp://www.sqlservercentral.com/Forums/Topic1382555-1550-1.aspxtest a couple of smaller arrays too as well as RAID5 vs RAID10Thu, 15 Nov 2012 14:03:26 GMTPerry Whittlehttp://www.sqlservercentral.com/Forums/Topic1382555-1550-1.aspxYes, this is the option I am going with first (lumping all disks into a single RAID 5 array). I believe keeping one as a hot spare leaves me with 10 working spindles - with any luck I'll be able to test with SQLIO and SQLIOsim in the next couple days.Thanks for everyone's help!Thu, 15 Nov 2012 12:30:19 GMTMyDoggieJessiehttp://www.sqlservercentral.com/Forums/Topic1382555-1550-1.aspx[quote][b]TheSQLGuru (11/15/2012)[/b][hr]I think with so few spindles carving them up into smaller sets just sets yourself up for one or more of those smaller groups to be a bottleneck. I would lump them all together, and chose RAID 5 or 10 based on a) needed formatted disk size and b) IO testing metrics verifying I could get the read/write performance mix I required.[/quote]i agree, hence my previous comment to test various array types and number of disksThu, 15 Nov 2012 11:55:21 GMTPerry Whittlehttp://www.sqlservercentral.com/Forums/Topic1382555-1550-1.aspxI think with so few spindles carving them up into smaller sets just sets yourself up for one or more of those smaller groups to be a bottleneck. I would lump them all together, and chose RAID 5 or 10 based on a) needed formatted disk size and b) IO testing metrics verifying I could get the read/write performance mix I required.Thu, 15 Nov 2012 08:09:54 GMTTheSQLGuruhttp://www.sqlservercentral.com/Forums/Topic1382555-1550-1.aspxI spoke with our Tech Services guy and he said there'd be no issue using a RAID5 with 12 disks, it would give us ample space and yield 11 available spindles. This should provide quick performance with a 9-1 read/write ratio.Unfortunately I'm not much of a hardware guy :( But I suppose I'll just need to test it out to seeWed, 14 Nov 2012 10:40:38 GMTMyDoggieJessiehttp://www.sqlservercentral.com/Forums/Topic1382555-1550-1.aspxI personally wouldn't use 12 disks in a single raid 5 array, recently i've used Raid 50 for similar sized arrays, basically it's creates two 6 disk raid 5 arrays and stripes them together.Tue, 13 Nov 2012 20:54:22 GMTdavidwarnerhttp://www.sqlservercentral.com/Forums/Topic1382555-1550-1.aspxControllers and disks differ. The only sure fire way is to create test arrays and spin up SQLIO to test your throughput and latency. Try different arrays and number of disks to see which combination will serve you best.Mon, 12 Nov 2012 17:16:21 GMTPerry Whittlehttp://www.sqlservercentral.com/Forums/Topic1382555-1550-1.aspxThere's a lot to consider when you're debating on spindle splits... but I wouldn't worry as much about RAID 5 vs. 10 for read/write anymore. All the newer "Big Iron" systems are cache-write based and they've optimized the heck out of Raid 5 now. Really, the question between 5 and 10 has come down to how many hardware failures are allowable before operational loss. 5 is one spindle slow, two spindles dead. 10 is from 2 drives (mirror dies on both sides) to n/2 if you have enough raid controllers because of the split mirrors.At this point, with a brand new system, I'd be looking towards other forms of optimization. An example, do you intend to do filegroup manipulations within a large database? If so, you need them on separate physical spindles to get the best speed out of them. If you're simply dealing with a bunch of smaller databases this isn't as much of an impact.Since you're not looking to replace the existing storage arrays, you've got a nice amount of 'give' to work with here. I'd start with looking for current I/O bottlenecks, if you have any. Splitting the log file(s) off to their own spindle set definately couldn't hurt.My knee-jerk reaction for this would be a 4 drive RAID 10 and 2 4 drive RAID 5s. Use the RAID 10 for your log file over-runs and/or split some of the log files off the old system on onto this one to reduce write competition. Use the split RAID 5s to break up your databases (try to split based on heavy usage times) and give yourself the option to filegroup some nonclustered indexes in case you desire parallel access on some rather large table(s) eventually.One other thing you might find useful is if you don't currently have a dedicated spindle set for your tempdb, do so now. Take one of those sections and make it TempDB mdf/ndf only. Particularly for reporting servers that need to do massive sorts and the like, tempdb tends to get hammered pretty hard. Getting it out of the way of everything else is usually very useful.Mon, 12 Nov 2012 16:51:13 GMTEvil Kraig Fhttp://www.sqlservercentral.com/Forums/Topic1382555-1550-1.aspxThat should work.Was thinking to place the 12-disks into one large RAID 5 (so that should leave me with 11 spindles) - I will place Data files and Index files here.The existing array is another MD1200 with 12-disks as well, carved out into 2 separate drives, both RAID-10. So I will place LDF files on 1 RAID 10 and tempdb on another...Mon, 12 Nov 2012 16:50:23 GMTMyDoggieJessiehttp://www.sqlservercentral.com/Forums/Topic1382555-1550-1.aspxHi,I think you can still stay on your RAID5The following link [url]http://www.dell.com/downloads/global/solutions/tradeoffs_RAID5_RAID10.pdf?c=[/url]can explain you more. However if you can place your log and tempdb on a RAID 10then it will be a good plus.But it depends, if your reads increase then a RAID10 may be better option, if not then you'll not obtain a significant gain, moreover considering your log and tempdb already on a RAID10.RegardsIgorMiMon, 12 Nov 2012 15:39:36 GMTIgorMihttp://www.sqlservercentral.com/Forums/Topic1382555-1550-1.aspx90% reads, 10% writes...Reading a couple of chapters from the link you posted pretty much narrows things down to a RAID5 or 10 (which is pretty much were I got to before posted this thread), I guess my main concern now is, would the 10% write ratio really degrade the read performance of the RAID altogether due to the parity necessity? I had a RAID 5, spanning across 12 spindles, that would give me over 6TB and 11 usable spindles, correct? I'd probably move my Data and Index files to this storage array, placing my log files and tempdb on the other RAID 10 array.Mon, 12 Nov 2012 11:43:28 GMTMyDoggieJessiehttp://www.sqlservercentral.com/Forums/Topic1382555-1550-1.aspxHi,If you have quite higher percentage of writes against reads, then RAID 5 is better than RAID 10.However, you can download the following book by Jonathan Kehayias:[url]http://www.red-gate.com/community/books/accidental-dba?utm_source=simple-talk&utm_medium=webpage&utm_content=troubleshooting_ebook&utm_campaign=sqlmonitor[/url]and decide what configuration to choose.RegardsIgorMiSun, 11 Nov 2012 09:48:08 GMTIgorMihttp://www.sqlservercentral.com/Forums/Topic1382555-1550-1.aspx12x600gb is a serious amount of storage, what do you intend to use the new disks for? Have you considered what config you would use if you do split the array?Sun, 11 Nov 2012 06:29:30 GMTPerry Whittlehttp://www.sqlservercentral.com/Forums/Topic1382555-1550-1.aspxAnyone want to take a stab at this? ;-)Thu, 08 Nov 2012 18:10:11 GMTMyDoggieJessiehttp://www.sqlservercentral.com/Forums/Topic1382555-1550-1.aspxWe have a brand new DELL MD1200 with 12-spindles - These are 15Krpm, 600GB SAS drivesThe server this is going to be running on is a fairly busy server, has about 90% read, 10% writes (according to a recent evaluation performed by DELL). Important to note, this is "additional" storage and isn't replacing any existing storage arrays.This server is a subscriber in a transactional replication setup and is primarily used for reporting (so there's heavy stored-procedure executions). So 10% of the "writes" on this server occur from replicated data being stored, custom data tables being written to, and log files.Here's our existing set up:MD1200 array (has 12 spindles) in RAID 10C - 68GBD - 136GBE - 1.3TBF - 1.3 TBObviously I'd like to have the best configuration that would give me: #1 - as many spindles as possible #2 - best speed for queries that run against itI was thinking RAID 10, but someone from our Tech Services department made a comment that since the writes are so little overall, that we might benefit from the extra space and performance of a RAID 5 - [b]giwever[/b], depending on which article you read online, some says DO NOT USE RAID 5, some say the performance gain over either choice is negligible.My initial thoughts were again a RAID 10, using all available disks to add a new drive - G:\ or would it be smarter to split the array up?Any advice from the experts?Thu, 08 Nov 2012 09:04:45 GMTMyDoggieJessie