White Papers

SQL Server design considerations
9 Dell EMC PowerVault ME4 Series and Microsoft SQL Server | 3923-BP-SQL
Figure 1 compares the OLTP performance for each RAID type.
Maximum OLTP IOPS by RAID level
3
3.5 Validating the storage design
Once the I/O requirements have been defined, it is easy to determine whether the hardware can provide the
desired performance by running some simple tests. Diskspd is a free Microsoft utility that can simulate I/O
patterns generated by SQL Server. There are several other utilities available as well. When selecting a utility
to simulate I/O, verify that it meets the following requirements:
• Ability to configure block size
• Ability to specify number of outstanding requests
• Ability to configure test file size
• Ability to configure number of threads
• Support for multiple test files
• Does not write blocks of zeros during tests
3.5.1 Validating the I/O path
The first thing to test on a new configuration is the path between the server and the array. Running a large
block sequential read test using small files should saturate the path between the server and the array. This
test verifies that all paths are fully functional and can be used for I/O traffic. Run this test on a dedicated
server and array; a live system could cause significant performance issues.
3
An OLTP workload is defined as having an 8k block size and a 70/30 read/write mix.
99,000
115,000
192,000
0
50,000
100,000
150,000
200,000
250,000
ADAPT RAID 5 RAID 10
Max OLTP IOPS by RAID Level