White Papers

Sizing for SQL Server
10 Dell EMC SC Series: Microsoft SQL Server Best Practices | CML1057
2.7.1 Traditional SSD configurations
A single SSD type can be used as the high-performance tier in an SC Series array either alone (all-flash) or
combined with spinning drives (hybrid). When using SSDs as a tier that participates in automated tiering,
there needs to be enough drives in the tier to handle 100 percent of the IOPS and at least 30 percent of the
capacity of the volumes that use the SSD tier. Since SSDs can perform a large number of IOPS, a small
number of drives may meet the IOPS requirement. However, the capacity or throughput requirements may
need many drives. It is very important to have enough SSDs to meet the IOPS as well as the throughput and
capacity requirements. If the SSD tier becomes full, performance will degrade significantly.
In cases where SSD capacity is limited, it may be more cost effective to place a subset of the SQL Server
files on SSDs, instead of placing all files on SSDs with automated tiering. Storage profiles allow an
administrator to easily control which volumes reside on SSDs. While transaction log files and tempdb files
require high performance and tend to be small enough to fit on a small number of SSDs, they are not always
the best choice for SSDs. In some cases, it may be better to place data files on SSDs. Analyze the access
patterns of the database files to determine the best fit. If a data file is too large to fit on the available SSD
drives, the portion of the data requiring high performance can be moved to a separate file group and placed
on a volume that resides on SSDs.
2.7.2 Flash-optimized configurations
By leveraging the characteristics of both write-intensive and read-intensive SSDs, a flash-optimized
configuration can provide both high capacity and high performance at an affordable price point, making it
practical to store a large SQL Server data set solely on SSDs. When sizing a flash-optimized configuration, it
is important to use enough write-intensive drives to handle the volume of writes and enough read-intensive
drives to provide sufficient capacity. When working with traditional SSD configurations, the number of drives
was determined primarily by capacity requirements that were often reduced due to cost considerations. Using
read-intensive drives makes it cost effective to provide a large amount of capacity. However, additional write
or read-intensive drives may be needed for the configuration to keep from exceeding the throughput limit of
the drives.