Storage Systems - SQL Server 2012 Internals and Troubleshooting - page 108

Be sure to coordinate testing with your storage administrator before you start these tests, as they
are designed to heavily stress your I/O system. It is best to begin by coni rming any calculated
assumptions. If you have 100 10K SAS disks coni gured in a RAID 1+0 set, you can assume random
I/O will perform 14,000 reads. To test these assumptions run a single SQLIO.exe instance:
sqlio -kR -s300 -frandom -o1 -b8 -LS -Fparam.txt
The -k option sets either R (read) or W (write). Set the test run length using the -s function. We
recommend testing for at least a few minutes (this example uses 5 minutes). Running a test for too
short a time may not accurately demonstrate how a large RAID controller cache will behave. It is
also important to pause in between similar tests. Otherwise, the array cache will reuse existing data,
possibly skewing your results.
The -f option sets the type of I/O to run, either random or sequential. The -o parameter dei nes
the number of outstanding I/O requests. You use the -b setting to dei ne the size of the I/O
request, in KB. This example tests very small I/O. SQL Server normally reads at least a full 64KB
extent.
You use the -LS setting to collect system latency information. On a 64-bit system you can add the
-64 option to enable full use of the 64-bit memory system. Finally, you dei ne the location for the
param.txt i le using the -F option. Following is the test output:
sqlio v1.5.SG
using system counter for latency timings, 14318180 counts per second
parameter file used: param.txt
file c:\testfile.dat with 2 threads (0-1) using mask 0x0 (0)
2 threads reading for 300 secs from file c:\testfile.dat
using 8KB random IOs
enabling multiple I/Os per thread with 8 outstanding
size of file c:\testfile.dat needs to be: 104857600 bytes
current file size: 0 bytes
need to expand by: 104857600 bytes
expanding c:\testfile.dat ... done.
using specified size: 100 MB for file: c:\testfile.dat
initialization done
CUMULATIVE DATA:
throughput metrics:
IOs/sec: 13080.21
MBs/sec: 102.18
latency metrics:
Min_Latency(ms): 0
Avg_Latency(ms): 0
Max_Latency(ms): 114
histogram:
ms: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24+
%: 37 59 3 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
This single 8K random test started by creating the 100MB test i le. In this case, the test was gener-
ated on a VM guest using a l ash drive, so the results are not representative of what you might see
in a real-world test. The test ran for i ve minutes with one outstanding I/O, and generated 13,080
IOPS and 102 MB/sec. It peaked at 114 milliseconds of latency but averaged under a millisecond
for access.