Performance testing  a new disk subsystem  from a SQL Server perspective is one of the fun aspects of DBA work. Firstly, it’s a great opportunity to influence the optimizations and give valuable feedback to the storage team. Secondly, it’s difficult to simulate the exact workloads and IO patterns. This is a good challenge to review and summarise database servers you’re managing.

SQLIO is a useful tool from Microsoft . Prior to doing SQL Server testing , I commit to generating some figures from SQLIO and working wit the storage guy. We focus on configurations to optimize various activity .

I prefer to set an IO optimal target – and see if SQLIO can verify the expectation. Some guidelines for setting optimal targets :

For tempdb aim for  highest io/sec possible.

For data file , aim for high 8KB and 64KB Random Read and Random Write along with highest possible IO\sec

For log files aim for highest possible MB\sec throughput

For backup files , aim for high MB/sec Sequentail Read and Sequential Writes

The general steps I use are  as follows

Install SQLIO on C:\

The default test file is normally not big enough  for SQL Server testing – normally I create a  25 GB file.This is  smaller then the cache – but is closer to some real performance testing.

Change the file size by adjusting the values in the SQLIO installation file. The default installation is on : C:\Program Files\SQLIO

 The file to edit is “param.txt” . You’ll see a  line entry :

 c:\testfile.dat 2 0x0 100

 Change it to the drive and size you prefer . The sizes are in MB. I’m changing it to test the E:\ with a 25 GB (25600 MB) file:

 e:\perftest.dat 2 0x0 25600

 For Sequential activity I focus on these IO sizes : 8KB, 64KB, 12KB, 256KB  and 1024 KB

For random activity I focus on these IO sizes : 8KB

 Create a Windows batch file called “perftest.bat” in the SQLIO directory. Place the code into the “perftest.bat”

The advice from Microsoft is to test each IO path individually.

In the example below I’m testing read and random , 8kb and 64 kb IO size for the E:\.

--execute the “perftest.bat”  with one input parameter  i.e an output file name

set outputfile=%1
if "%1"=="" goto syntax
sqlio -kW -t8 -s120 -o8 -frandom -b8 -BH -LS E:\perftest.dat >> %outputfile%
sqlio -kR -t8 -s120 -o8 -frandom -b8 -BH -LS E:\perftest.dat >> %outputfile%
sqlio -kW -t8 -s120 -o8 -fsequential -b64 -BH -LS E:\perftest.dat >> %outputfile%
sqlio -kR -t8 -s120 -o8 -fsequential -b64 -BH -LS E:\perftest.dat >> %outputfile%
sqlio -kR -t8 -s120 -o8 -fsequential -b128 -BH -LS E:\perftest.dat >> %outputfile%
sqlio -kR -t8 -s120 -o8 -fsequential -b256 -BH -LS E:\perftest.dat >> %outputfile%
sqlio -kR -t8 -s120 -o8 -fsequential -b1024 -BH -LS E:\perftest.dat >> %outputfile%
:syntax
echo IO performance test - include an outputfile name 
:end

key:

-kW   testing  writes

-kR    testing reads

-t8      testing with 8 threads

-o8      8 outstanding requests at once

-s240   length of test in seconds

-frandom   testing random activity   e.g data activity

-fsequential  testing sequential activity  e.g Lg files

 From the command prompt type (check your permissions):

sqlio -kW –s20 -fsequential -t8 -o8 -b8 -LS -Fparam.txt timeout /T 20

 The purpose of this short test , defined by the /T switch as 20 seconds, is to create the data file, as defined in the “param.txt” .  It will take a few minutes, be patient.

 Once the command is finished , you are ready to execute:  perftest.bat <name_of_an_output_file>

An output example

sqlio v1.5.SG
using system counter for latency timings, 2001300000 counts per second
8 threads reading for 240 secs from file E:\perftest.dat
using 8KB random IOs
enabling multiple I/Os per thread with 8 outstanding
buffering set to use hardware disk cache (but not file cache)
using current size: 25600 MB for file: E:\perftest.dat
initialization done
CUMULATIVE DATA:
throughput metrics:
IOs/sec:  1859.65
MBs/sec:    14.52
latency metrics:
Min_Latency(ms): 1
Avg_Latency(ms): 33
Max_Latency(ms): 1725
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+
%:  0  0  0  0  0  0  0  0  0  1  1  2  3  3  3  3  3  3  3  3  3  3  3  3 59

How to Read the Output

 Focus on : IOs/sec , MBs/sec , histogram

 For sequential activity focus on MB/s (throughput) . for Random activity focus on IO/sec . This equates to OLTP v DSS.

This output example was for Random writes with 8KB IO Size .

 At this point , I’ll make an assessment for performance. I was looking for better performance on this disk response – in the region of < 2 ms.   

 I’ll post tomorrow on continuing the analysis and what other steps to take to when analysing data from output.

Related Posts:

SQL Server Storage and IO performance

SQL Server IO patterns and RAID levels

Author: Jack Vamvas (http://www.sqlserver-dba.com)