SQLIO is one of my favorite free tools for storage benchmarking. For SQL Server, one of the most important factors in performance is disk throughput. How fast is your storage? What happens to performance if your workload doubles or something runs in the background? Where is your system running today, and what baseline do you have to compare the performance against?
SQLIO provides a command-line tool that can simulate OLTP traffic on a storage system. Most people complain that it is too hard to use for large batch operations. I created a tool a while back for my personal use to overcome this obstacle and to save myself great amounts of time. After showing it to multiple clients, each of them asked to have a copy for their internal use. After some time spent cleaning up the tool, I have now released it to the general public for you all to use, free of charge.
This tool provides a test bundle that you can download and stage on a server that you wish to evaluate. Configure your workload file location and size and test runtime, and sit back while your system runs through a full set of performance tests. Upload the output file and my tool performs a quick analysis for you and presents you with a spreadsheet with nice charts and graphs that are suitable for all audiences.
Your average SQLIO test output looks like the following. Try extracting the useful data out of 192 tests for a storage system analysis…
C:\sqlio>sqlio -kW -t4 -s10 -dD -o128 -fsequential -b64 -BH -LS -Fparam.txt
sqlio v1.5.SG
using system counter for latency timings, -1800967296 counts per second
parameter file used: param.txt
file d:\sqliotestfile.dat with 2 threads (0-1) using mask 0x0 (0)
2 threads writing for 10 secs to file d:\sqliotestfile.dat
using 64KB sequential IOs
enabling multiple I/Os per thread with 128 outstanding
buffering set to use hardware disk cache (but not file cache)
using specified size: 8000 MB for file: d:\sqliotestfile.dat
initialization done
CUMULATIVE DATA:
throughput metrics:
IOs/sec: 2226.23
MBs/sec: 139.13
latency metrics:
Min_Latency(ms): 0
Avg_Latency(ms): 104
Max_Latency(ms): 2451
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 96
|
My analyzer presents you with graphs that look like this:
And charts like this:
The items are great for a quick glance. If something does not look right, further analysis is always warranted.
I present you my first free tool – the SQLIO Analyzer. Experiment, test, baseline, and enjoy. Please let me know if you run into any bugs with the tool.
