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.