White Papers

13 BP1062 | Best Practices for Decision Support Systems with Microsoft SQL Server 2012 using Dell EqualLogic PS Series
Storage Arrays
5 Baseline I/O profiling using IOMeter
A series of I/O profiling tests were executed using IOMeter, to establish the baseline I/O performance
characteristics of the test storage configuration before deploying any databases. Since DSS workloads are
mostly sequential with larger I/O block sizes, the baseline throughput numbers were established by
running three types of large block I/O workload. They were:
100% Sequential reads of large I/O block sizes: This test determined the read I/O throughput of the array
with a sequential I/O workload. This provided a baseline for workloads such as DSS query processing and
report generation operations.
100% Sequential writes of large I/O block sizes: This test determined the write I/O throughput of the
array with a sequential I/O workload. This provided a baseline for workloads such as bulk, or batch updates
to table data and backup.
100% Random reads of large I/O block sizes: DSS database environments exhibit an I/O pattern of
random read I/O with large blocks due to multiple users submitting large running queries to the system.
Even though each user’s query generates table scans involving sequential I/O, a collection of queries from
multiple simultaneous users make the resulting I/O more parallel and random. This test provided a
baseline for a random read workload.
In these tests, throughput was measured while evaluating the EqualLogic PS6110X array running I/O
patterns with large block sizes of 100% sequential (reads and writes) and 100% random (reads) on a single
PS6110X array. The volume size and the number of volumes used for the IOMeter test were chosen to
roughly match the array capacity utilization when the actual database (300 scale) would be deployed. The
IOMeter test file occupied the entire volume (number of sectors set to 0) and the volumes were exposed
as NTFS volumes with a drive letter. The configuration parameters for the tests are shown in Table 1.
Test parameters: I/O workload studies using IOMeter Table 1
Configuration Parameters
EqualLogic SAN
One PS6110X (2.5", 24 10 K SAS drives,900 GB)
Volume configuration Ten volumes, 300 GB each
RAID type
RAID 50
I/O mix
I/O block size
100% Sequential Reads
64K,128K,256K,512K,1MB (Queue depth-128 , worker per volume-1, number
of sectors-0)
100% Sequential Writes 64K,128K,256K,512K,1MB (Queue depth-128 , worker per volume-1, number
of sectors-0)
100% Random Reads 64K,128K,256K,512K,1MB (Queue depth-128 , worker per volume-1, number
of sectors-0)
The results collected from the tests are illustrated in Figure 3 and Figure 4. Figure 3 shows sequential I/O
throughout reported by IOMeter for the tested I/O block sizes. The theoretical throughput for a 10 GbE