What's the Best Way to Carve Up a SAN?

Business intelligence (BI) really messes up most SAN engineers. They're storage gurus first and foremost; to them, a database is a database. They can carve up or configure the SAN and optimize its layout for OLTP databases to play reasonably well with backups and miscellaneous file serving, but their good intentions get muddled when you add BI.

A SAN engineer might join 10 physicaldisks in one LUN and then allocate it intofive different volumes that are presented tothe OS. Each of the five volumes wouldactually have 10 spindles, which is the key toSAN performance. However, if two or moreof the volumes are under duress, there's contention for the underlying 10 spindles. Thissituation occurs often if you haven't made itclear to the SAN engineer everything thatwill be running on the SAN and the uniqueneeds of each workload.

Relational OLTP databases are the sweet spot for SAN engineers, who like to combine many disks into large LUNs and join numerous LUNs into still-larger virtual LUNs or "meta-LUNs," then slice off dynamic volumes at will, mountable by Windows and usable by SQL Server. Shared disk spindles work well for spreading out the random I/O that applications such as active OLTP databases generate. But this standard configuration philosophy goes south when you apply it to a relational BI database. BI databases are batch loaded, then pounded on by aggregation queries generating large sequential reads rather than random reads. Because of these differences, you need to make sure some standard best practices are observed when the SAN is carved up.

BI workloads are bulk/contiguous operations that benefit from dedicated spindles.Using dedicated spindles avoids disk flutter,contention, and hotspots. Thus, it's better tocreate separate volumes on separate physicaldisks for the data files, TempDB, and the logfiles. For parallelization and future expansion,the number of files in a file group should beequivalent to the number of processors andlocated on different volumes when possible.SQL Server will then essentially stripe data,spreading it out across the allocated disks.

It's critical to put log files on separate physical disks with a smaller stripe size (16K32K) to handle the contiguous writes. Log files usually generate less I/O, so RAID 1 is typically sufficient. If your log files are too large for one physical disk, then go to RAID 10; never use RAID 5—logs generate many write operations and RAID 5's parity writes will degrade performance immensely.

Write operations to SQL Server datafiles occur in 64K blocks or extents, so thestripe size and OS should be aligned to avoidmultiple stripe reads per I/O request. You orthe SAN engineer should adjust the SAN"offset" or use the Windows Server 2003Diskpart utility to set disk alignment. Also,BI workloads on SQL Server usually benefitif configured with a heavy write cache.

SQL Server Analysis Services MDX queries perform selective aggregation reads across large I/O ranges, so the more spindles per volume the better. Volumes can be shared with idle spindles during periods of high query activity, but make sure you know how and when the idle spindles are used. Partition co-alignment helps to achieve better I/O throughput where queries require measures or aggregates from multiple measure groups across the same date range. For example, put your January SSAS Sales partition on Volume 1 and January SSAS Inventory partition on Volume 2. Then alternate, being sure not to put all January partitions on the same volume. And Analysis Services typically benefits from weighted read cache on the disk side.

Carving up your SAN for BI databases requires a different approach than for other databases. That approach starts with you being involved in the SAN allocation process. Doing so might make the difference between the failure of your BI initiatives and success.

A special thanks to Erik Veerman for sharing his BI SAN configuration best practices.