I have put the finishing touches on the latency graphs in my SQLIO Analyzer. It takes all of the latency numbers from all of the tests and performs a quick analysis so you know where you stand on the two major variables on the test – read/write and sequential/random. Check it out!
Kent Chenery (b | l | t) has sent me some fantastic ideas for ways to present the storage latency metrics in my SQLIO Analyzer tool. I’ve been struggling with ways to present the metrics in ways that make sense without having people drown in the details. I hope to absorb and get them in the SQLIO Analyzer by the end of next weekend! Thanks again Kent! I really do appreciate it. I owe you one!
Once again, the community around SQL Server is phenomenal. I get good, solid, constructive feedback on all sorts of things, and it’s the largest reason why I decided to focus on data using SQL Server. You all are truly amazing.
Thanks Hayden for submitting a note that I had a bug in the 128 thread formulas in my spreadsheet in my SQLIO Analyzer. It is now fixed. Thanks again!
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.
Tonight I gave a presentation on the tool that I constructed for rapid-fire SQLIO results analysis. I have attached the files to this post for you all to share. I also am in the works of moving the web-based results extraction tool to a new site that I will publicly release within the next week.
Until then, please use http://sqlio.180overe.com for a quick means to upload your SQLIO output file and receive the results in a CSV file.
SQLIO test analysis bundle: SQLIO Disk Benchmarking Bundle
I will post here soon once I get the site moved over. Thanks everyone for attending the sessions tonight after the long day of training, and thank you Paul for your kind words.
UPDATE: The new site is complete, and ready for you. Kindly surf to http://tools.davidklee.net/sqlio.aspx for a much more user friendly and informative version of the site.