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 physical disks in one LUN and then allocate it into five different volumes that are presented to the OS. Each of the five volumes would actually have 10 spindles, which is the key to SAN performance. However, if two or more of the volumes are under duress, there's contention for the underlying 10 spindles. This situation occurs often if you haven't made it clear to the SAN engineer everything that will be running on the SAN and the unique needs 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 to create separate volumes on separate physical disks for the data files, TempDB, and the log files. For parallelization and future expansion, the number of files in a file group should be equivalent to the number of processors and located 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 data files occur in 64K blocks or extents, so the stripe size and OS should be aligned to avoid multiple stripe reads per I/O request. You or the SAN engineer should adjust the SAN "offset" or use the Windows Server 2003 Diskpart utility to set disk alignment. Also, BI workloads on SQL Server usually benefit if 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.
