White Papers

Sizing for SQL Server
8 Dell EMC SC Series: Microsoft SQL Server Best Practices | CML1057
Write penalty by RAID type
RAID type
Write penalty
I/O description
RAID 10
2
2 writes
RAID 10 DM
3
3 writes
RAID 5
4
2 reads, 2 writes
RAID 6
6
3 reads, 3 writes
By default, writes to SC Series volumes use RAID 10. Since RAID 10 has the lowest write penalty, it is
strongly recommended to always use RAID 10 for writes. All standard SC Series storage profiles write using
RAID 10.
When sizing an array, it is important to factor in the write penalty when determining the number of disks
required. The number of IOPS reported by the server when gathering I/O statistics does not include the write
penalty. If the IOPS target for the array design does not include the write penalty, it will be undersized. To
adjust the number of IOPS to include the write penalty, use the following formula:
Number of IOPS + (Number of IOPS * Write Percentage * (Write Penalty – 1))
Definitions:
Number of IOPS
=
Number of IOPS generated by the server
Write Percentage
=
Percent of the I/O attributed to writes
Write Penalty
=
Number of I/Os required to complete a write
As an example, consider a server that is performing a workload of 10,000 IOPS with a mix of 70 percent
reads and 30 percent writes. Since the default storage profile is used, writes use RAID 10, which has a write
penalty of two. For this workload, the SC Series array needs enough disks to support 13,000 IOPS (10,000 +
(10,000 * .30 * (2-1)) = 13,000).
2.3 Random I/O compared to sequential I/O
Disks can perform more sequential I/O than random I/O. This is because sequential I/O requires less
overhead on the disk. While I/O statistics show how much I/O is occurring, they do not show how much is
sequential I/O compared to random I/O. The virtualized storage architecture of SC Series arrays may cause
sequential I/O requests from the server to become large-block random I/O on the physical disks. Performance
will still be good because large-block random I/O provides comparable performance to sequential I/O. For
best results when sizing an SC Series array, assume all of the I/O is random. This will ensure good
performance for both random and sequential operations.
2.4 OLTP workloads
While every environment is unique, an online transaction processing (OLTP) workload typically consists of
small, random reads and writes. A storage system servicing this type of workload is primarily sized based on
the number of IOPS required. Different drive types have different performance capabilities. Faster spinning
drives are able to provide more IOPS with lower latency than slower spinning drives. For best performance,
SSDs are recommended for OLTP workloads (see section 2.7).