asked on

San Disk Array set up for SQL Server

Hi,

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.

EG
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

OR

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??
Eg
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?
And
How do we maximize the Disk IO on the SAN for SQL Server.

I hope this makes sence.

Thanks

ASKER CERTIFIED SOLUTION

Member_2_231077

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

smythsit

ASKER

Thanks Andyalder,

That is helpful,

We already have a SAN here and will be Virtualising Production DB next year. We just may do it onto another SAN.

So no difference on what Controllers to put Array Disks on then - it's not an I/O Concern, only best practice for Redundancy.

would you recommend a 64 or 128 KB Strip, is there a way to mitigate the risk to the controller cache?
There is noting to say that those extra 8 pages are anything to do with what data SQL is retrieving is there?
Is there any way to group the data on the disks so that if SQL is doing a table read or an index read and its sequential on the hard Disk?

Thanks

Member_2_231077

Well, if you're virtualizing then might as well have a SAN. Splitting the LUNs across controllers improves performance, although if a controller fails the other one does all the LUNs and it generally slows down a lot as write cache probably gets disabled as can't mirror it.

I'd stick to 64K as per MS recommendations, SQL reads ahead on indexes and table scans anyway just in case it wants the data on later pages. Sometimes it's just wasted I/O but it generally helps - see http://msdn.microsoft.com/en-us/library/ms191475.aspx.

The controller automatically groups the data on the disks in the right order, SQL will request a lot of pages on table scans and they'll come some from each disk. They'll not necessarily be sent back to the server in the order they are requested as that depends on which disk gets its data first but they'll end up ordered in the disk buffer in the server anyway.