Go Premium for a chance to win a PS4. Enter to Win


Need SAN configuration advice

Posted on 2010-08-18
Medium Priority
1 Endorsement
Last Modified: 2013-11-14
Good afternoon Experts,

This isn't so much of a problem, more like just needing some advice.

I'll start with a little information about our environment.  We have a few MSSQL servers (version 2005/2008) that we need to setup in a failover cluster for fault tolerance.  We'll call them Production, Staging and DocumentStorage.

The Production server is the back-end database for an Internet portal that end-users access to run reports, generate statistics, etc.  Fairly high activity of mostly reads from SSRS reports running against it.  Database size is approximately 500 GB.

DocumentStorage is simply a document imaging system.  Database full of metadata, and a path to a document stored on a shared drive.  Database size only a few GB and probably 700GB of documents stored in file shares. Pretty low activity.

The Staging server loads raw data from multiple sources such as CSV's, XML, fixed-width text files, DBF, PDF, MS Access, pretty much any format you can imagine.  Data is loaded from their raw sources, transformed, scrubbed, cleaned up, etc. on the Staging server via SSIS packages that run through the SQL Server Agent and then sent to Production.

The database size on this server isn't enormous, maybe 80GB or so, but only because data is deleted XX days after it is sent to production, we don't keep alot of history here.  The amount of data that passes through this machine each day is pretty massive; between all the different types of text files and other raw data, I would estimate we are loading maybe 100-200 million records per day.

Disk I/O has always been the biggest bottleneck on the Staging server (as is with most SQL server instances).  I'm looking for some advice on how we should configure out SAN for the best performance on the Staging server.

The Staging server is on the SAN currently, with drives like this:

Array 1:
2x73GB SAS 15k in RAID1 (73GB usable space)
   73GB LUN dedicated for SQL Logfiles

Array 2:
5x300GB SAS 15k in RAID5 (1,200GB usable space)
   215GB LUN for SQL Data files
   80GB LUN for "raw data" (drive holding txt/csv/dbf/etc. files for loading)

The SAN is actually much larger than this, we have other machines using it for other stuff as well, I'm just explaining what my current SQL configuration is.  As a part of this project, I am authorized to COMPLETELY re-allocate EVERYTHING on the SAN to provide maximum performance on the Staging server.  Data loading on-time is critical for us and right now we're lagging quite a bit.

The SAN is a Dell MD3000i (1GbE iSCSI).  Available on the SAN for this project is:
4x73GB 15k SAS drives
17x300GB 15k SAS drives
6x2TB 7.2k SATA drives

In the past I've always chopped up SAN's into many smaller arrays.  Say for example 3x300gb drives in RAID5 for SQL data on Server 1, 2x300GB drives in RAID1 for SQL Logs on Server 1, etc.  This keeps dedicated spindles available for SQL logs and SQL data files one each server.

I'm wondering if I'm better off instead creating LARGE arrays of drives and then carving smaller LUN's for each server instead.  Say for instance, creating a RAID10 array of 8x300GB drives (1.2TB usable space) and carving into several LUN's for SQL Data files on each server, and another RAID10 array of 8x300GB drives and carving into LUN's for SQL logfiles on each server..

Which is the better solution for maximum IO performance?  Large arrays, split into many LUNs, or smaller arrays that dedicate drives to a single machine?

Question by:mortimer452
LVL 47

Accepted Solution

David earned 2000 total points
ID: 33468226
I/O performance is measured in throughput and I/Os per second.  These two numbers are pretty much mutually exclusive.  Optimize for one by sacrificing performance on the other.  To a lesser extent, optimize for reads by sacrificing write performance.   Optimize for large block I/O, you sacrifice small block I/O.

Controllers, file systems, NTFS file system settings all weigh in.  You didn't bother to mention much of anything other than a RAID level & number of disks in the arrays.  

So, bottom line, it is not that simple, and certainly nobody can tell you with authority which will be "faster".    In situations such as this, best to run perfmon as a starting point and model your I/O to know what your applications are requesting, and where the delays are.  The storage doesn't care anything about what applications you are running, whether the files are PDFs, or MS access, or even configuration of the file system.

The RAID sees read and write requests for X blocks at a time.  So, measure X, the read/write mix, and so on, then you tune so that the most amount of I/Os take the fewest amount of time.  Nobody is going to be able to tell how this will shake out, because you have not actually described the I/O itself.


Expert Comment

ID: 33468358
I would probably define separate logical volumes (LVs) for SQL Server data, logs, tempdb, and file landing area (what you call raw data). Each LV would have its dedicated set of LUNs to minimize contention at the LUN level. If you have enough drives, go with RAID10 instead of RAID5. At a minimum, tempdb and log LVs must be RAID10.
LVL 18

Expert Comment

ID: 33468529
I don't know what options you have available as far as formatting of the LUNs, but look into the block size.  Some SANs (IBM, for instance) allow you to specify a different block size for each physical volume.  Build the physical volumes individually, selecting RAID level based on read/write performance.  I personally favor RAID 10, but that gets pricey quick at 50% usable..  Then, when you format the volume after it's presented to the server, make sure to format NTFS with the same block sizes.

There are lots of little things you can do to optimize performance.  However, a 1Gig iSCSI SAN just isn't going to give you any kind of REAL horsepower.  After Ethernet and TCP/IP overhead, a fully saturated 1GB iSCSI link will only get you ~400Mb/s.  Either move to 10Gig, or look at FC.

Just my $.02.  YMMV.

LVL 56

Expert Comment

ID: 33473767
Apart from the MS best practice of putting transaction logs on different physical disks from the data for a no-loss restore there isn't really any advantage of having dedicated sets of spindles for different databases, tempdb etc. In your case I'd make 3 sets of spindles rather than split the 17*300GB disks up. Sure you get disk contention between two DBs that way buy you also get the benefit of having all those spindles available to each DB so if one is quiet the other has more IOPS available to it.

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how the fundamental information of how to create a table.

879 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question