Automating SQLIO Benchmarking with Powershell

powercli

Recently I’ve been doing a great deal of SQLIO benchmarking on Nutanix for an upcoming SQL on Nutanix Best Practices document (hush hush :P).  In this post I’ll go over how I automated these SQLIO tests using Powershell to drive the testing and automatically output to a consumable CSV.

This test will take parameters specified by the user and automate the test piping the output to a CSV document specified by the user.  I just used this to automate over 2,000 SQLIO runs on Nutanix (still running as we speak!).

Also, here’s an awesome post by Jose Barreto which motivated me to automate and served as a foundation for running SQLIO via Powershell: LINK.  The article also has a plethora of information on how to utilize SQLIO to identify performance bottlenecks and breaking points.

Here’s an example of calling the function (below).  NOTE: you can also utilize loops in Powershell (this is what I do using a hash table of various test params) to drive multiple iterations.

  • NTNX-Run-SQLIO -Iterations 10 -Duration 30 -RorW R -RandOrSeq random -BlockSize 512 -Threads $_ -OutstandingOps 1 -TargetFile F:\test.dat -TargetType file -OutputCSV X:\results.csv

The script

Now for the good stuff, here’s the Powershell script (formatting and tabbing stripped since I removed the code plugin since it was causing long load times):

 

Driving script with params

Here’s an example of how to automate this for all possible test variations.  This will take a array of files and perform every test and combination on those and output results to CSV for consumption.

 

Legal Mumbo Jumbo

Copyright © Steven Poitras, The Nutanix Bible and StevenPoitras.com, 2014. Unauthorized use and/or duplication of this material without express and written permission from this blog’s author and/or owner is strictly prohibited. Excerpts and links may be used, provided that full and clear credit is given to Steven Poitras and StevenPoitras.com with appropriate and specific direction to the original content.