Monday, July 22, 2013

How to quickly create a large test file for SQLIO?

Easiest way is to use FSUTIL.EXE tool which comes with all versions of Windows.

Following command will create a 2TB file.

PS C:\> FSUTIL.EXE File CreateNew D:\TestFile.DAT (2TB)
File D:\TestFile.DAT is created

Please note that CREATENEW will take longer time since it actually initializes the block to the size you mentioned (2TB in example) so easier way is to use SETVALIDDATA. This just moves the end of file marker. 

!!!Be very careful while doing this as it will overwrite already existing data!!!

PS C:\> FSUTIL.EXE File SetValidData D:\TestFile.DAT (2TB)
Valid data length is changed

To use the file size in GB or TB you must run these commands thru PowerShell. Else you must give size in bytes

Thursday, July 18, 2013

IOPS Calculator and SQLIO

I was asked by my storage admins what is SQLIO and how does it calculate IOPS throughput. And here is the how (What is SQLIO is none of their business)

So this is how output of SQLIO looks like

C:\Program Files (x86)\SQLIO>sqlio -kR -t64 -s120 -o128 -frandom -b64 -BH -LS -Fparam.txt 
sqlio v1.5.SG
using system counter for latency timings, 14318180 counts per second
parameter file used: param.txt
file D:\testfile.dat with 2 threads (0-1) using mask 0x0 (0)
file E:\testfile.dat with 2 threads (2-3) using mask 0x0 (0)
file S:\testfile.dat with 2 threads (4-5) using mask 0x0 (0)
6 threads reading for 120 secs from files D:\testfile.dat, E:\testfile.dat and S:\testfile.dat
using 64KB random IOs
enabling multiple I/Os per thread with 128 outstanding
buffering set to use hardware disk cache (but not file cache)
using specified size: 100000 MB for file: D:\testfile.dat
using specified size: 100000 MB for file: E:\testfile.dat
using specified size: 90000 MB for file: S:\testfile.dat
initialization done
CUMULATIVE DATA:
throughput metrics:
IOs/sec: 10717.27
MBs/sec:   669.83
latency metrics:
Min_Latency(ms): 8
Avg_Latency(ms): 71
Max_Latency(ms): 122
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  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0 100

And here is the formula used to calculate IOPS

IOPS = (MBps Throughput / KB per IO) * 1024

Based on above throughput of SQLIO will be

10717.27 = (669.83 * 64) / 1024