White Papers

SQL Server design considerations
7 Dell EMC PowerVault ME4 Series and Microsoft SQL Server | 3923-BP-SQL
3 SQL Server design considerations
The I/O storage system is a critical component of any SQL Server environment. Sizing and configuring a
storage system without understanding the I/O requirements can have disastrous consequences. Analyzing
performance in an existing environment using a tool like Live Optics can help define the I/O requirements.
Your Dell EMC representative can assist with Live Optics data collection and analysis. For best results,
capture performance statistics for a period of at least 24 hours that includes the system peak workload.
3.1 OLTP workloads
While every environment is unique, an online transaction processing (OLTP) workload typically consists of
small, random reads and writes. A storage system for OLTP workloads is primarily sized based on capacity
and the number of IOPS required.
3.2 OLAP/DSS workloads
An online analytic processing (OLAP) or decision support system (DSS) workload is typically dominated by
large, sequential reads. A storage system for OLAP/DSS workloads is primarily sized based on throughput.
When designing for throughput, the performance of the entire path between the server and the drives in the
ME4 Series array needs to be considered. For best throughput, consider using 16 Gb Fibre Channel (FC) or
10 Gbps iSCSI connectivity to the array. To meet high-throughput requirements, multiple physical paths may
be required.
3.3 Mixed workloads
The most common scenario for a SQL Server environment is a mixed workload. Typically, SQL Server I/O
patterns do not strictly fall into an OLTP or OLAP pattern. This is what can make SQL Server workloads
challenging because no two workloads behave the same. In addition, the same SQL Server host or instance
may be servicing multiple applications or transaction workloads.
A mixed workload can also imply that multiple applications (in addition to SQL Server) are residing on the
same host or accessing the same storage. The combined workload of these applications invalidates any
typical application I/O usage pattern. For these reasons, it is important to gather actual performance metrics
for best sizing results.
3.4 ME4 Series configuration
Creating a balanced storage configuration is important because SQL Server workloads can vary greatly and
I/O patterns can often fluctuate due to changes in the database environment, evolving data-access patterns,
or data growth. For most SQL Server workloads, it is recommended to configure the ME4 Series array using
the virtual storage type and ADAPT as the RAID level.
For best performance, a minimum of 24 drives should be used when using the ADAPT RAID level because
this is the minimum number required to create two virtual storage pools, one per controller in a dual-controller
system. Start with 24 SSD drives and add additional drives as needed to achieve performance and capacity
requirements. An ME4 Series array with SSDs spread evenly across 2 virtual storage pools (one per
controller) configured with the ADAPT RAID level provides the best overall balance of performance, flexibility,
capacity, and data protection.