SQL Server - SQLIO interpret results

13 July,2012 by Jack Vamvas

When thinking of SQLIO think : performance capacity.

Determine the IO capacity of an IO subsystem for certain types of IO usage relevant to SQL Server - which is the main reason for testing different IO usage. I can’t overstate the importance of creating an IO profile for the SQL Server usage requirements.   IT departments are under increased budget pressure. As the DBA, just blatantly requesting RAID 10 and SSD for all IO usage can cause a serious budget blow out. On the other hand the DBA should build in future usage and not under specify the requirements.

What do the results mean?

The test below is 8 threads (-t8) & 8 outstanding IOs (-o8). For random 8K reads.

I like to focus on Random Reads because they don’t benefit from the array cache.

The test file is 25 GB. I use large files because:

a)       more indicative of database sizes

b)       attempt to use wider range of the disk

Read more for similar topics

Disk IO performance and SQLIO

SQL Server Storage and IO performance

sqlio -kR -t8 -s120 -o8 -frandom -b8 -BH -LS E:\perftest.dat

sqlio v1.5.SG
using system counter for latency timings, 2001300000 counts per second
8 threads reading for 120 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
throughput metrics:
IOs/sec:  1766.69
MBs/sec:    13.80
latency metrics:
Min_Latency(ms): 1
Avg_Latency(ms): 35
Max_Latency(ms): 2348
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  1  1  1  2  2  2  2  2  2  2  2  2  3  3  3  3 65


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 - SQLIO interpret results

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