SQL Server - Disk IO performance and SQLIO

23 August,2011 by Jack Vamvas

SQL Server 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=%1if "%1"=="" goto syntaxsqlio -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%:syntaxecho IO performance test - include an outputfile name :end


-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.SGusing system counter for latency timings, 2001300000 counts per second8 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.datinitialization doneCUMULATIVE DATA:throughput metrics:IOs/sec: 1859.65MBs/sec: 14.52latency metrics:Min_Latency(ms): 1Avg_Latency(ms): 33Max_Latency(ms): 1725histogram: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

Notes on Performance Tuning very large databases for OLTP

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


Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.


Post a comment on SQL Server - Disk IO performance and SQLIO

sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer