White Papers

Sizing for SQL Server
9 Dell EMC SC Series: Microsoft SQL Server Best Practices | CML1057
2.5 OLAP/DSS workloads
An online analytic processing (OLAP) or decision support system (DSS) workload is typically dominated by
large, sequential reads. A storage system servicing this type of workload is primarily sized based on
throughput. When designing for throughput, the performance of the entire path between the server and the
disks in the SC Series array need to be considered. For best throughput, consider using at least 16Gbps
Fibre Channel (FC) or 10Gbps iSCSI connectivity to the array, and 12Gbps SAS connectivity from the
controllers to the disk enclosures. To meet high throughput requirements, multiple HBAs may be required in
the server, the SC Series array, or both.
2.6 Sizing tiers
When sizing an SC Series array with two tiers, start by sizing tier 1 for all of the IOPS with at least 30 percent
of the capacity and tier 3 for 70 percent of the capacity with at least 30 percent of the IOPS. In a two-tier
system, the fastest disks are tier 1 and the slower disks are tier 3. When sizing each tier for SQL Server,
consider the following:
• Accessible pages on a volume are not eligible to move to a lower tier until they are at least 12 days
old.
In environments where indexes are rebuilt frequently, accessible pages on the database volume may
never be older than 12 days. All accessible pages will always be in tier 1. In those environments, tier
1 needs to be sized for 100 percent of the capacity required by SQL Server.
• The automated tiering feature of SC Series arrays automatically moves infrequently accessed data to
slower, cheaper storage (tier 3), reducing storage costs without impacting performance.
For environments where the access pattern is even across the entire data set, all data may need to
reside on tier 1 to ensure good performance. In those environments, size tier 1 for 100 percent of the
capacity required by SQL Server.
2.7 Using SSDs
SSDs offer significantly better performance than traditional hard drives for most I/O patterns. From a
performance perspective, SSDs are suitable for storing any type of SQL Server file. While SSDs can support
a large number of IOPS, it is easy to exceed throughput limits of the drive or other components in the path
with the large I/Os that can be generated by SQL Server. It is important to understand the performance
characteristics of SSDs as well as the I/O requirements of the SQL Server environment before implementing
SSDs.
Both write-intensive and read-intensive SSDs can be used in an SC Series array. Although they provide lower
capacity than read-intensive drives, write-intensive drives can perform a larger number of write cycles, making
them better suited for heavy write workloads. Read-intensive drives offer a much higher capacity than write-
intensive drives, but cannot endure as many write cycles, making them better suited for read-intensive
workloads. Traditional SSD configurations contain only write-intensive drives used as a tier in the same way
as spinning drives.