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”
It seems like a valuable tool to have but I am not able to download the tool from http://tools.davidklee.net/sqlio.aspx. I got an error page. Can you check to see if the web site is up. Thanks.
Works for me – make sure you’re not including the full stop in the URL.
Not worked for me
Link no longer working. Any other scripts to parse the results?
Not that I know of. David’s moving the facility to a new location AFAIK.
I always use Jonathan Kehayias Powershell script to parse the SQLIO output. You can find his script on this link: http://sqlblog.com/blogs/jonathan_kehayias/archive/2010/05/25/parsing-sqlio-output-to-excel-charts-using-regex-in-powershell.aspx
I created a PowerShell script to serialize the SQLIO results and display charts to help with data analysis. More details at: http://sunitjoshi.wordpress.com/2013/11/15/updated-automating-charting-sqlio-using-powershell/
Doesn’t seem to work still, so I’ll share a series of simple scripts I use as well as the RDL you can use to do analysis on your SQLIO results.
Import
http://allen-mcguire.blogspot.com/2014/01/importing-sqlio-data.html
RDL
http://allen-mcguire.blogspot.com/2014/01/sqlio-reporting-rdl.html
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?