• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 718
  • Last Modified:

San Disk Array set up for SQL Server


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.

  • 2
1 Solution
Handy HolderSaggar maker's bottom knockerCommented:
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:

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.

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?

Handy HolderSaggar maker's bottom knockerCommented:
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.

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now