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
smythsitAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

andyalderCommented:
Are you clustering? If not you'll probably get more bang for your buck by using rop of the range PCIe RAID controllers plus external enclosures rather than using a SAN.

Compare the folowing 2 ESRP solutions:
h20195.www2.hp.com/v2/GetPDF.aspx/4AA1-5510ENW.pdf
h20195.www2.hp.com/v2/GetPDF.aspx/4AA2-0300ENW.pdf

0.38IOPS * 8000 users = 3040 user IOPS on the SAN solution with 34 disks
0.48IOPS * 6250 users  = 3000 user IOPS on the DAS solution with 32 disks

Near enough the same performance and near enough the same number of disks but the DAS solution is half the price of the SAN solution. If you're not using shared storage clustering (and SQL has other ways to provide redundancy) and you don't need snapshots then DAS wins every time. Doesn't matter that the example is with Exchange but you're using SQL, it applies to everything.

>Have Enclosure1 disks 1-8 for the array A and put the raid mirror on Enclosure 2.
Generally you spread the disks on all enclosures so it can tolerate an enclosure failure, but that depeends on the best practices of your particular vendor.

>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?
You don't have any control over that, you might be able to tweak read-ahead though but you can do that during test as it doesn't involve moving disks about.

>if we create a drive with segmentation of 64 K...
64K is the recommended stripe size for SQL, you should use that for stripe element size on the SAN/DAS (as you already proposed) and if Windows pre 2008 use diskpart to aligh the partition to that (2008 does it for you). If you use 128K for example you automatically read ahead an extra 8 SQL pages but you also run out of cache on the controller more quickly.


0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
smythsitAuthor Commented:
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
0
andyalderCommented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.