White Papers

Sizing for SQL Server
7 Dell EMC SC Series: Microsoft SQL Server Best Practices | CML1057
2 Sizing for SQL Server
The I/O subsystem 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. Monitoring
performance in an existing environment using a tool like
Live Optics can help define the I/O requirements. For
best results, capture performance statistics for a time period of at least 24 hours that includes the system
peak workload.
2.1 Key I/O performance metrics
A good understanding of the key metrics used to describe I/O performance is required to successfully define
I/O requirements and size storage systems.
2.1.1 IOPS
I/O operations per second (IOPS) describe the number of reads and writes occurring each second. This is the
primary metric used when designing OLTP systems. For arrays using spinning disks, this metric is key for
determining the number of disks required in the array. Arrays using SSDs typically provide enough IOPS once
throughput and capacity requirements are met. The number of IOPS is reported by Windows Performance
Monitor using the disk reads/sec, disk writes/sec and disk transfers/sec physical disk counters.
2.1.2 Throughput
Typically expressed in megabytes per second (MB/s), throughput describes the amount of data transferred
between the server and the storage. Throughput can be calculated using the number of IOPS and the
average I/O size (throughput = IOPS * average I/O size). This is the primary metric used to design the path
between the server(s) and the storage as well as the number of drives required. A small number of SSDs can
often meet IOPS requirements but may not meet throughput requirements. Throughput is reported by
Windows Performance Monitor using the disk read bytes/sec, disk write bytes/sec and disk bytes/sec physical
disk counters.
2.1.3 Latency
Typically expressed in milliseconds (ms), latency describes the amount of time an I/O operation takes to
complete. When a server experiences high latency, this generally indicates an I/O bottleneck in the system.
Latency is reported by Windows Performance Monitor using the avg. disk sec/read, avg. disk sec/write and
avg. disk sec/transfer physical disk counters.
2.2 The write penalty
RAID technology is used to protect data from a disk failure on SC Series arrays. For each write sent to an SC
Series volume, additional I/O is performed on the physical disks to provide the redundancy needed to protect
the incoming data. The amount of additional I/O is determined by the RAID configuration for the volume. The
following table shows the number of I/Os required when performing a write on the volume. The number of
I/Os required to complete a write is referred to as the write penalty.