White Papers

SQL Server design considerations
9 Dell EMC PowerVault ME4 Series and Microsoft SQL Server | 3923-BP-SQL
3.5.2 Validating the drives
Once the I/O path has been validated, the next step is to test the drives. For best results when testing drives
on an ME4 Series array, use the following guidelines when configuring the test:
• In a dual-controller system, use at least one volume per pool with each pool on a separate controller.
This ensures that I/O will be distributed across both controllers. Using both controllers provides a
more accurate simulation of real-world activity. For best results, use the same number of volumes on
each controller.
• When performing I/O tests on any storage platform, it is important to use files that are larger than the
controller cache. For more accurate results, use a file size that matches the amount of data being
stored. In an environment where that is not practical due to a large data set, use a file size of at least
100 GB.
• Avoid using test utilities to generate files full of zeros for drive validation. Some I/O test tools,
including Diskspd, SQLIO and IOMeter, can be used to write zeroes for drive validation, which causes
inaccurate results when testing with files containing only zeros. The contents of the test file can be
verified by viewing the test file with a hex editor after different stages of a test. For example, create a
small test file and view it after the initial creation, as well as after the test has run for a few seconds. If
the file is filled with zeros, select another utility. Diskspd and IOMeter initially create test files filled
with zeros, and then write random characters when performing write tests. To properly initialize a
Diskspd or IOMeter test file, run a sequential write test until the entire file has been overwritten with
non-zero data. Unfortunately, SQLIO writes zeros during write tests and therefore is not
recommended for drive validation.
The purpose of this drive testing is to validate that the storage design will provide the required throughput and
IOPS with acceptable latency. It is important that the test does not exceed the designed capacity of the array.
For example, an array designed for a workload of 5,000 IOPS is likely to perform poorly with a workload of
10,000 IOPS. If a test is generating a workload higher than the designed capacity, adjust the workload being
generated by reducing the number of threads or outstanding I/Os.
The results of the Live Optics analysis provide an I/O target to simulate using these tests. To estimate the
performance capabilities of the array, run I/O tests with a range of I/O sizes commonly seen with SQL Server.
When testing random I/O, test with an I/O size of 8 KB and 64 KB. When testing sequential I/O, start with I/O
sizes of 8 KB and 64 KB. Since processes like read-ahead scans and backups can issue much larger
sequential I/O, it is a good idea to also test block sizes up to 1024 KB.