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.
6 comments:
I'm new to PowerShell, but not new to SQLIO. I would love to get this going but I'm having problems.
From a PowerShell window I'm execting:
$result = ls "results.txt" | %{Parse-SQLIOOutput -file $_}
I'm in a dirctory: C:\SQLIO
Both the .ps1 and .txt are in that directory.
I forgot to post the error:
The term 'Parse-SQLIOOutput' is not recognized as a cmdlet, function, operable program, or script file. Verify the
term and try again.
At line:1 char:96
+ $result = ls "result.txt" | %{Parse-SQLIOOutput.ps1 <<<< -file $_}
I'm not 100% sure if this is your problem, but powershell won't run a script just because it's located in the current directory. I presume that this is for security reasons, and follows the model of unix/linux shells like bash.
Normally, I have everything in my search path, which gets around the problem. For your particular command, I suggest that you try referencing the current directory with ".\", like this:
$result = ls "results.txt" | %{.\Parse-SQLIOOutput.ps1 -file $_}
Failing that, you might try providing the full path to the script file, including the .ps1 extension.
Let me know how you make out and thanks for reading. -d
This is a great post but i am not having luck to get it working. I can get the script to run but it complains about the method split:
Has anybody been able to get it to work?
Has anyone gotten this to work? I have no clue how to run this from the powershell command prompt. I am getting the same errors as Koyote and everyone else.
Hm. It's funny how long an blog posting can live.
alex: The split methods used in the script should be standard calls; I don't see why they should fail. Do you have an error message, with line numbers.
Splynde: I would guess that Powershell is not finding 'Parse-SQLIOOutput.ps1' because that file is not on your search path. Either copy that file to a folder in your search path or add the folder that contains 'Parse-SQLIOOutput.ps1' to your search path. Failing that, modify the script and hard-code the full path to the 'Parse-SQLIOOutput.ps1' script.
Post a Comment