SQL Server Performance, Best Practices, & Productivity
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 http://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 http://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 http://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 http://support.microsoft.com/kb/231619 Microsoft KB article
How It Works: SQLIOSim - Running Average, Target Duration, Discarded Buffers http://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 http://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 VersionI 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.
I analyze the results in Excel like this:
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:
or
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.
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.
Figure 2. The ResultsPane. Figure 3. Sample output after copy-&-pasting the results of SQLIOSim Parser into Excel & manipulating the worksheet’s format as described in the text.
Figure 2. The ResultsPane.
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.
Jimmy May, MCM, MCDBA, MCSE, MCITP: DBA + DB Dev Principal Performance Engineer: SQL Server A.C.E.: Assessment Consulting & Engineering Services http://msinfosec.com http://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
It seems I cannot download this SQLIOParserWin.exe as the hotlink "v.2.3.0" seems broken. Can you please double-check?
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.
Thanks Jimmy, now can you do one for SQLIO? :)
@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.