Jaa

SQLIOSim Parser by Jens Suessmeyer & Yours Truly

  • Artikkeli

New! Improved! v.3.3.0 Now Available on CodePlex!  

Preamble
SQLIOSim Parser shreds the results of SQLIOSim.log.xml in seconds, allowing easier & more accurate interpretation of the output of SQLIOSim.

SQLIOSim is a tool written by CSS Engineer Bob Dorr.  Many of you know Bob from the excellent insights he shares on the CSS SQL Server Engineers blog.  Note that SQLIOSim is not a performance tool; it is designed to validate disk I/O subsystem correctness.

Back during MCM training I was asked about interpreting the output of SQLIOSim.  I cited these references:

Understanding SQLIOSim Output
https://sqlblog.com/blogs/kevin_kline/archive/2007/06/28/understanding-sqliosim-output.aspx 
MVP Kevin Kline’s conversation with yours truly

More Tidbits on SQLIOSim
https://sqlblog.com/blogs/kevin_kline/archive/2008/12/14/more-tidbits-on-sqliosim.aspx 
Kevin’s conversation with Bob Dorr & Jose Fortuny

How It Works: SQLIOSim - Running Average, Target Duration, Discarded Buffers
https://blogs.msdn.com/psssql/archive/2008/11/12/how-it-works-sqliosim-running-average-target-duration-discarded-buffers.aspx
Bob Dorr documents key SQLIOSim metrics

How to use the SQLIOSim utility to simulate SQL Server activity on a disk subsystem
https://support.microsoft.com/kb/231619
Microsoft KB article

I’ll document these & other references in a subsequent post.

The Genesis of SQLIOSim Parser
During MCM training I lamented that, as cool as the tool is, I explained my practice of manually parsing the output, laboriously copying-&-pasting values into Excel in order to better wrap my brain around the results.

Serendipitously, Jens Suessmeyer was there & offered to write a parser.

Jens is not only a great friend, but one of the Microsoft’s rising stars.  Before joining Microsoft he was an MVP, he’s a prolific contributor to our internal DLs, & he has an impressive depth & breadth of knowledge.  Check out & subscribe to his blog.  And now he’s provided yet another means to make our lives easier. 

Fruitful Collaboration Results in Downloadable Version
I provided guidelines & Jens has followed through by writing SQLIOParserWin.exe.  We’ve labored through several enhancements.  Be clear, Jens did the heavy lifting here.  The fruits of our collaboration are now available for review.  Here's a link to the latest-&-greatest, currently v.3.3.0. 

The parser has saved me loads of time whenever I use SQLIOSim & it will do the same for you.  I’m on-site with a customer right now.  I ran & parsed the real-life results of numerous tests in mere seconds instead of tedious & error-prone copying-&-pasting.

SQLIOSim Parser Usage

Note: A step ahead of me as usual, the latest-&-greatest version from Jens provides more sophisticated output than I've described here; stay tuned for details.

    1. Download & execute SQLIOParserWin.exe
    2. Point to a SQLIOSim log file (Figure 1)
    3. Click the “Parse input file and show results” button to instantly display the ResultsPane window (Figure 2)

I analyze the results in Excel like this:

  • Type select & copy the results (Ctrl+A, Ctrl+C)
  • Paste into Excel
    • Delete the empty first column
    • Select all cells by typing Ctrl+A or clicking the Sheet Selector button (that’s the button at the intersection of the rows & columns)
      • Right-click a row button, click Row Height…, & change the value to 15 for all rows
      • With the sheet still selected, type Ctrl+1 to invoke the Format Cells dialog
        • On the Number tab, click Number, change “Decimal places” to 0 & check “Use 1000 Separator”
        • Click the Font tab & select something such as Consolas 9
        • Click OK to accept your changes & close the dialog
      • With the sheet’s contents still selected, “right size” the columns by double-clicking the boundary between columns A & B.
      • Select the TestTime column, type Ctrl+1 again, click the Number tab, select the Custom category, & enter this value for the Type: mm/dd/yyyy hh:mm:ss
      • Click OK to accept your changes & close the dialog

See Figure 3 for the final result.  I’ll post the macro code on request.  Note that Jens has even added row headers for us.  He’s also added a row id so we can sort the results but still if we desire restore the original order.

Alternative Output Formats
There are two alternative ways to generate output:

  • Save the result in XML format by providing a path & file in the “Result OutputFileName” textbox & clicking the “Export result file to XML” button.

or

  • Click the “Display result Report” button to invoke an intermediate window which allows you to enter:
    • CustomerName
    • Path to customer logo
    • Additional project information

The final report includes many relevant columns.

Feedback
We invite your feedback.  Let us know what you think.  Jens always eager to flex his coding skills.  SQLIOSim Parser is great for ad hoc analysis.  Future versions may include columns from the SQLIOSim config file which enhance enterprise compatibility.

Note: These images reflect: SQLIOParserWin.exe v.0.2.3.0.  We'll be posting updated images sometime soon.

Figures
Here are the figures cited above.

image
Figure 1.  Launch SQLIOParserWin.exe to expose the SQLIOSim Parser interface.  Point to a SQLIOSim log file & click the “Parse input file and show results” button.

image
Figure 2.  The ResultsPane.

image 
Figure 3.  Sample output after copy-&-pasting the results of SQLIOSim Parser into Excel & manipulating the worksheet’s format as described in the text.

Columns
SQLIOSim Parse provides the following columns.  As stated, we may incorporate columns from the SQLIOSim config file in future versions.

  • RowId
  • DriveLetter
  • TestTime
  • FileName
  • Compression
  • Encryption
  • Sparse
  • TargetIODuration
  • RunningAverageIODuration
  • NumberOfTimesIOThrottled
  • IORequestBlocks
  • Reads
  • ScatterReads
  • Writes
  • GatherWrites
  • TotalIOTime
  • SectorSize
  • Cylinders
  • MediaType
  • SectorPerTrack
  • TracksPerCylinder
  • ReadCacheEnabled
  • WriteCacheEnabled
  • ReadCount
  • ReadTime
  • WriteCount
  • WriteTime
  • IdleTime
  • BytesRead
  • BytesWritten
  • SplitIOCount
  • StorageNumber
  • StorageManager

Jimmy May, MCM, MCDBA, MCSE, MCITP: DBA + DB Dev
Principal Performance Engineer: SQL Server
A.C.E.: Assessment Consulting & Engineering Services
https://msinfosec.com
https://blogs.msdn.com/jimmymay
www.twitter.com/aspiringgeek
Don’t practice in front of the CIO. A professional prepares ahead of time.
—Pond's 12th Law

Comments

  • Anonymous
    October 08, 2009
    It seems I cannot download this SQLIOParserWin.exe as the hotlink  "v.2.3.0" seems broken. Can you please double-check?

  • Anonymous
    October 09, 2009
    MSDN was undergoing maintenance yesterday.  The link seems to be working now.  Contact me again if you continue to have difficulty. And stay tuned for v.next!  Jens is laboring feverishly to incorporate additional features.

  • Anonymous
    October 23, 2009
    The comment has been removed

  • Anonymous
    October 23, 2009
    @Andy Ha-Ha!  I'll make sure Jens gets right on it! ;-)  Seriously, are you familiar with SQLIO Automation by Steven Wort?  I'll forward some info if you'd like.