Cool free tool to parse and analyze SQLIO results

During every one of our Immersion Events, we designate Thursday evening as 'open mic' night where anyone can do a 15-minute presentation on anything they want (to do with SQL Server) to the class. We usually have 4 or 5 people who entertain us with interesting talks, and our recent classes in Chicago were no different.

One of the talks really impressed me. David Klee (b|t) demonstrated an automated analysis tool he's written for SQLIO result file parsing to save him time. He mentioned he was going to put it online and I encouraged him to do so as I could see the benefit to many people out there of not having to write their own analysis tools/spreadsheets.

You can get to David's free analysis site at http://tools.davidklee.net/sqlio.aspx. Clicking on the link at bottom right allows you to upload a SQLIO results text file. Once you've clicked ANALYZE, select the option to output the results to a spreadsheet and one will be automatically generated for you. If you look in the Analysis pane of the spreadsheet, you'll see something like below (using David's supplied example SQLIO output).

Very cool stuff – thanks David!

9 thoughts on “Cool free tool to parse and analyze SQLIO results

    1. Allen, I have this scripts uploaded onto a SQL 2008 R2 server. However the stored procedure is not pushing any data out to the SQLIP_TestPass table. Everything is working until the following section of the SP is reached;

      ,( SELECT REPLACE(resulttext,’sqlio ‘,”)
      FROM dbo.sqlio_import impsqlio_version
      WHERE imp.rowid + 1 = impsqlio_version.rowid
      ) AS sqlio_version
      ,( SELECT LEFT(resulttext,( CHARINDEX(‘ threads’,resulttext) ))
      FROM dbo.sqlio_import impthreads
      WHERE imp.rowid + 3 = impthreads.rowid
      ) AS threads
      ,( SELECT UPPER(SUBSTRING(resulttext,( CHARINDEX(‘threads ‘,resulttext) ) + 8,1))
      FROM dbo.sqlio_import impreadorwrite
      WHERE imp.rowid + 3 = impreadorwrite.rowid
      ) AS readorwrite
      ,( SELECT SUBSTRING(resulttext,( CHARINDEX(‘ for’,resulttext) ) + 4,
      ( CHARINDEX(‘ secs ‘,resulttext) ) – ( CHARINDEX(‘ for’,resulttext) ) – 4)
      FROM dbo.sqlio_import impdurationseconds
      WHERE imp.rowid + 3 = impdurationseconds.rowid
      ) AS durationseconds
      ,( SELECT SUBSTRING(resulttext,7,( CHARINDEX(‘KB’,resulttext) ) – 7)
      FROM dbo.sqlio_import impsectorsizekb
      WHERE imp.rowid + 4 = impsectorsizekb.rowid
      ) AS sectorsizekb
      ,( SELECT SUBSTRING(resulttext,( CHARINDEX(‘KB ‘,resulttext) ) + 3,
      ( CHARINDEX(‘ IOs’,resulttext) ) – ( CHARINDEX(‘KB ‘,resulttext) ) – 3)
      FROM dbo.sqlio_import impiopattern
      WHERE imp.rowid + 4 = impiopattern.rowid
      ) AS iopattern
      ,( SELECT SUBSTRING(resulttext,( CHARINDEX(‘with ‘,resulttext) ) + 5,
      ( CHARINDEX(‘ outstanding’,resulttext) ) – ( CHARINDEX(‘with ‘,resulttext) )
      – 5)
      FROM dbo.sqlio_import impiosoutstanding
      WHERE imp.rowid + 5 = impiosoutstanding.rowid
      ) AS iosoutstanding
      ,( SELECT REPLACE(CAST(resulttext AS NVARCHAR(50)),’buffering set to ‘,”)
      FROM dbo.sqlio_import impbuffering
      WHERE imp.rowid + 6 = impbuffering.rowid
      ) AS buffering
      ,( SELECT SUBSTRING(resulttext,( CHARINDEX(‘size: ‘,resulttext) ) + 6,
      ( CHARINDEX(‘ for ‘,resulttext) ) – ( CHARINDEX(‘size: ‘,resulttext) ) – 9)
      FROM dbo.sqlio_import impfilesizemb
      WHERE imp.rowid + 7 = impfilesizemb.rowid
      ) AS filesizemb
      ,( SELECT RIGHT(resulttext,( LEN(resulttext) – 10 ))
      FROM dbo.sqlio_import impios_sec
      WHERE imp.rowid + 11 = impios_sec.rowid
      ) AS ios_sec
      ,( SELECT RIGHT(resulttext,( LEN(resulttext) – 10 ))
      FROM dbo.sqlio_import impmbs_sec
      WHERE imp.rowid + 12 = impmbs_sec.rowid
      ) AS mbs_sec
      ,( SELECT RIGHT(resulttext,( LEN(resulttext) – 17 ))
      FROM dbo.sqlio_import implatencyms_min
      WHERE imp.rowid + 14 = implatencyms_min.rowid
      ) AS latencyms_min
      ,( SELECT RIGHT(resulttext,( LEN(resulttext) – 17 ))
      FROM dbo.sqlio_import implatencyms_avg
      WHERE imp.rowid + 15 = implatencyms_avg.rowid
      ) AS latencyms_avg
      ,( SELECT RIGHT(resulttext,( LEN(resulttext) – 17 ))
      FROM dbo.sqlio_import implatencyms_max
      WHERE imp.rowid + 16 = implatencyms_max.rowid
      ) AS latencyms_max
      FROM dbo.sqlio_import imp
      INNER JOIN dbo.sqlio_import impfulltest ON imp.rowid + 20 = impfulltest.rowid
      AND impfulltest.resulttext = ”
      WHERE imp.rowid = imp.parameterrowid
      ORDER BY imp.parameterrowid

      Any suggestions?

Leave a Reply

Your email address will not be published. Required fields are marked *

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.