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.