White Papers

10 BP1062 | Best Practices for Decision Support Systems with Microsoft SQL Server 2012 using Dell EqualLogic PS Series
Storage Arrays
3 Nature of data warehouse workloads and storage
Different types of database applications have varying needs. Understanding the models for the most
common database application workloads can be useful in predicting possible application behavior. The
most common database application workload models are online transaction processing (OLTP) and data
warehouse (DW). This paper focuses on DW workloads.
For information on OLTP models, refer “Best Practices and Sizing Guidelines for Transaction Processing
Applications with Microsoft SQL Server 2012 using EqualLogic PS Series Storage” at
http://en.community.dell.com/dell-groups/dtcmedia/m/mediagallery/20321740/download.aspx
DW applications are typically designed to support complex analytical query activities using very large data
sets. The queries executed on a DSS database typically take a long time to complete and usually require
processing large amounts of data. A DSS query may fetch millions of records from the database for
processing. To support these queries the server reads large amounts of data from the storage devices. A
DW profile contains the following pattern:
• Reads and writes tend to be sequential in nature and are generally the result of table or index
scans and bulk insert operations.
• The read I/O to storage consists of large I/O blocks, ranging from approximately 64 KB to 512 KB
in size.
The large I/O requests require high I/O throughput rates from storage to the database server to provide
optimal performance. In addition to the significant I/O throughput required, the DW queries also require
substantial processing resources (CPU and RAM). Therefore, the database server must be provided with
sufficient processing and memory resources to handle the raw query results and return useable data.
The large I/O patterns and processing necessary in DW queries warrant careful system design to ensure
that the performance requirements are met at each component in the system. These components include
database and operating system settings, server resources, SAN design and switch settings, storage
Multipath I/O (MPIO) software, storage resources, and storage design.