Disk arrangement

Question

Disk arrangement

Viewing 8 posts - 16 through 22 (of 22 total)

You must be logged in to reply to this topic. Login to reply

magarity kerns SSCrazy Points: 2763 More actions · Answer 1

I had this exact same situation when I started a job just a couple of years ago! There was a Dell 220 array with 7 disks all as one giant RAID 5 and only one logical partition for a data warehouse, both stage and main; logs, indexes, tables were all thrown in one pile. There were also 5 internal disks on one RAID 5 that did nothing but OS/apps leaving a LOT of space wasted. Performance was truly hideous! My final configuration including in the internal disks was:

Array 1: RAID 1 for two logical partitions, one for OS/Apps and the other for tempdb. This is because the SQL Server will only use the OS swap file very, very rarely. Mainly it does all its work that won't fit in RAM in tempdb. So there's practically no load on the OS/Apps logical partition once everything is booted up. The seperate logical partition was to make sure tempdb was never fragmented, which is an important consideration. If your sever has other apps installed that may be using the OS swap space then your mileage may vary. I can only say my setup worked very well with this configuration.

Array 2: RAID 1 for logs. This was all the logs, both the main warehouse and the stage instances kept logs here.

Array 3: RAID 1 for non-clustered indexes. Again, both warehouse and stage instances.

Arrays 4 and 5: 3 disk RAID 5 for main warehouse files and the other for the stage. Even though, yes, RAID 5 does not write so fast as other levels I couldn't afford to toss away 50% of the space to parity. I could, however, afford to toss away 33% and this worked just fine.

Compared to everything piled in one bunch, like what you have now, performance improved so much that RAID 5's write speed for the primary files was nothing at all, I assure you! It was like there was a brand new server with 5 times the performance capacity.

Since you're tearing down and rebuilding, make sure the following is done when the disks are re-built: RAID stripe size should be divisible by 64K so the RAID 1s need 64K as their stripe and the 3 disk RAID 5s need to be 32K. Then format all the partitions with 64K as the NTFS block size. SQL Server reads and writes batches of 8, 8K blocks at a time. 8x8k=64k. So that's one 64K NTFS block and if the underlying disks span that 64K to be a single stripe then every read or write operation will only be one sector of the disks. This goes a long way towards preventing fragmentation. You have to watch closely for this one though as this configuration is done when the array is built via the RAID controller's utility or BIOS. Your server admin people may very well ignore this request if they do not understand it or they think you're just being whiny and picky. Mine did but I was able to get it changed before the restore process went too far.

Big Data Man SSCrazy Eights Points: 8536 More actions · Answer 2

magarity kerns (3/20/2008)
I had this exact same situation when I started a job just a couple of years ago! There was a Dell 220 array with 7 disks all as one giant RAID 5 and only one logical partition for a data warehouse, both stage and main; logs, indexes, tables were all thrown in one pile. There were also 5 internal disks on one RAID 5 that did nothing but OS/apps leaving a LOT of space wasted. Performance was truly hideous! My final configuration including in the internal disks was:
Array 1: RAID 1 for two logical partitions, one for OS/Apps and the other for tempdb. This is because the SQL Server will only use the OS swap file very, very rarely. Mainly it does all its work that won't fit in RAM in tempdb. So there's practically no load on the OS/Apps logical partition once everything is booted up. The seperate logical partition was to make sure tempdb was never fragmented, which is an important consideration. If your sever has other apps installed that may be using the OS swap space then your mileage may vary. I can only say my setup worked very well with this configuration.
Array 2: RAID 1 for logs. This was all the logs, both the main warehouse and the stage instances kept logs here.
Array 3: RAID 1 for non-clustered indexes. Again, both warehouse and stage instances.
Arrays 4 and 5: 3 disk RAID 5 for main warehouse files and the other for the stage. Even though, yes, RAID 5 does not write so fast as other levels I couldn't afford to toss away 50% of the space to parity. I could, however, afford to toss away 33% and this worked just fine.
Compared to everything piled in one bunch, like what you have now, performance improved so much that RAID 5's write speed for the primary files was nothing at all, I assure you! It was like there was a brand new server with 5 times the performance capacity.
Since you're tearing down and rebuilding, make sure the following is done when the disks are re-built: RAID stripe size should be divisible by 64K so the RAID 1s need 64K as their stripe and the 3 disk RAID 5s need to be 32K. Then format all the partitions with 64K as the NTFS block size. SQL Server reads and writes batches of 8, 8K blocks at a time. 8x8k=64k. So that's one 64K NTFS block and if the underlying disks span that 64K to be a single stripe then every read or write operation will only be one sector of the disks. This goes a long way towards preventing fragmentation. You have to watch closely for this one though as this configuration is done when the array is built via the RAID controller's utility or BIOS. Your server admin people may very well ignore this request if they do not understand it or they think you're just being whiny and picky. Mine did but I was able to get it changed before the restore process went too far.

Nice one Magarity, in terms of formatting and aligning to 64K NTFS partitions.

Thanks,

Phillip Cox

MCITP - DBAdmin|MCTS - SQL Server 2005

magarity kerns SSCrazy Points: 2763 More actions · Answer 3

I should add that RAID 5 worked for me for my main DW files because I had very generous load windows of almost all inserts and very few updates followed by mainly reporting reads the rest of the day. Before I reconfigured the drives the load would frequently run 30 minutes over the 4 hour load window but after the reconfiguration it was more typically 3 hours on a bad day. If it even approached 4 hours after reconfiguring it was because of an outright problem or unusual backload or somesuch. Your mileage may very - weigh the advice of the others concerning RAID 5 with your load type(s) and length in mind. If you can make, or almost make, your load windows now and you already have a huge RAID 5 then cutting it up with a few small RAID 5's is the least of your concerns, IMO.

Jason Marshall SSCommitted Points: 1791 More actions · Answer 4

Thanks much everyone, I certainly have a lot to think about. Appreciate the responses. Now I have to pass the hat for more disks....:D

Joe Clifford SSCrazy Eights Points: 8739 More actions · Answer 5

Hmm... I wasn't aware that there's a 10 disk version of the 2900, may be but the rack mount and tower versions I've seen all take a maximum of 8 disks.

Is your server a rack-mount or tower and what type & number(s) of drives and controllers are you running?

The reason I ask is that the number and type of controllers and ports (SAS/SATA? I don't think you can get a SCSI 2900 anymore) and how those drives are connected to the controller (e.g. 1x8 backplane or 2x4 backplane or?) is a factor in performance as well. It does little good to seperate your logical I/O's (e.g. 2 RAID 10's, one for logs and and the other for data) if they share physical paths - it may in fact be better to allocate additional spindles on the same physical path to the same logical path...

Joe