We have a High Transaction OLAP SQL database on a SAN.
We find that the Disk IO is a bottleneck for us.
Next year we may be implementing a new SAN and I am currently trying to design the Arrays etc for it and size it correctly.
I need to know the best way to create arrays on a SAN for best performance for SQL.
We will get 4 enclosures, each capable of 16 disks.
We will have a 4 x quad core machine running SQL, I was advised to have 16 datafiles for the database
I was planning on creating 2 arrays, Say A and B both of 8 disks in a Raid 10 config, so 32 disks altogether.
within each array I was planning on LUN's - every second one on a different SAN controller.
My question is how to Stripe the LUN's on the physical Hard drives and what's the best way to mix the drives on the enclosures.
Enclosure one, Physical Disk 1 - 4, Could be the first 4 disks of Array A
Enclosure 2, Physical Disks 1 - 4 , could be the second 4 disks of Array A
Then the Raid for Enclosure 2 would be on Enclosure 1 and visa versa , thus using up all 16 physical disks
Have Enclosure1 disks 1-8 for the array A and put the raid mirror on Enclosure 2.
Then, How do we achieve a best performance Disk Read or write??
When the spindle on Disk 1 is in position, the spindle on Disk 2 is spinning into place so that once the Read or Write on Disk 1 is complete the SAN is ready with Disk 2?
Because SQL Server reads 8kb Data Pages, if we create a drive with segmentation of 64 K (or any multiple of 8) than the Server could read / Write the data to the disk and have the next physical disk waiting etc.
So my questions would be:
Is it best to Stripe Array's across Multiple Enclosures?
How do we maximize the Disk IO on the SAN for SQL Server.
I hope this makes sence.