Friday, May 30, 2008

Parsing SQLIO output with Powershell

Anyone who has used sqlio knows that it's output files are not particularly readable. I used to manually run sqlio tests, then retype the output into a spreadsheet so that I could more easily analyze the data. This proved time-consuming, error-prone and boring, so I wrote a script that parses all of the data that I wanted from the sqlio output file. This post provides that code, and some background on how I use it. I will return to my series on my Powershell set up next time.

The first thing to do is to run your tests with sqlio and get it's results into files. This is left as an excercise to the reader. I have a .cmd file that I use to do this. I use a .cmd file because most of the machines that I test do not have Powershell installed on them. I am including this file in the SQLIO.scripts.zip file.

The reason that I break the running of the tests and the parsing of the output into two seperate processes is to decouple them. It can take quite a while for sqlio to run, especially if you are running tests at multiple block sizes or varying between random and sequential or read and write. The idea is to get all the data first, then I can parse at my leisure. If something goes wrong with the parsing, I don't have to rerun my entire test. This also allows me to tweak the parsing script as I go along, without having to re-run the data collection phase.

Running tests tedious to do. If all you want to do is try the parsing script, I have included some sample output that you can use in the sqlio.results.zip file.

The second thing to do is to run the script that parses a raw output file and store the parsed data in a Powershell object.

That command might look something like this:
$result = ls "*.txt" | %{Parse-SQLIOOutput -file $_}

The last thing that we need to do is to analyze the data. Storing the data in a Powershell object allows us to use all of Powershells features for producing reports, or to funnell the data into another tool for analysis.

For example, we can use the where keyword to restrict the output or we could store the data in an Excel file or a SQL Server table. A command to look at the data on a command line might look something like this:
$result | sort Drive,RandomOrSequential,ReadOrWrite,BlockSize,QueueDepth | format-list *

Or we could just sort it and send it to Microsoft Excel: $result | sort Drive,RandomOrSequential,ReadOrWrite,BlockSize,QueueDepth | Out-Excel

That's all there is to my script. I hope that you download the files and give it a try.

It is worth mentioning that Linchi Shea has a perl script that does more-or-less the same thing as my script. I haven't tried it myself, but Linchi does good stuff and you should definitely check it out if you are into perl.
Post a Comment