Using Excel to parse Set Statistics IO output

When tuning T-SQL, “set statistics io on” is definitely your friend. However, I know I cannot be the only developer whose eyes glaze over when I flip over to the Messages tab in SSMS and am greeted with this:

Table 'sysobjrdb'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'sysschobjs'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'sysobjrdb'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'spt_values'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'sysschobjs'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'syscolpars'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'syscolrdb'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'syscolpars'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'syscolpars'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'syscolpars'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'syscolrdb'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'syscolpars'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'sysschobjs'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'sysclsobjs'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'sysidxstats'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 8, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'sysidxstats'. Scan count 1, logical reads 12, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'sysclsobjs'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#spindtab___________________________________________________________________________________________________________000000001259'. Scan count 0, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'sysclsobjs'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#spindtab___________________________________________________________________________________________________________000000001259'. Scan count 0, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'sysclsobjs'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#spindtab___________________________________________________________________________________________________________000000001259'. Scan count 0, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'sysclsobjs'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#spindtab___________________________________________________________________________________________________________000000001259'. Scan count 0, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#spindtab___________________________________________________________________________________________________________000000001259'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'sysschobjs'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'sysidxstats'. Scan count 0, logical reads 4, physical reads 1, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#spcnsttab__________________________________________________________________________________________________________00000000125A'. Scan count 0, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#spcnsttab__________________________________________________________________________________________________________00000000125A'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 1, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'sysobjvalues'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'syscolpars'. Scan count 2, logical reads 14, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'sysschobjs'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#spcnsttab__________________________________________________________________________________________________________00000000125A'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 1, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'syscolpars'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#spcnsttab__________________________________________________________________________________________________________00000000125A'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#spcnsttab__________________________________________________________________________________________________________00000000125A'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'syssingleobjrefs'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
No foreign keys reference table 'Person', or you do not have permissions on referencing tables.
Table 'sysobjvalues'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'sysmultiobjrefs'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
No views with schema binding reference table 'Person'.

That 58-line monstrosity is brought to us by our friend sp_help. Scanning through all that output to find the meaningful few values that I am trying to tweak can make my head spin. So, like most developers would, I got lazy.  No, not lazy in that I stopped using set statistics io.  Rather, I got so lazy that I started to use Excel.

Aha, much easier!

SQL Server set statistics io output formatted into columns in Excel (click for enlarged version)

It’s a quick and dirty couple of formulas, but it has worked reasonably well for my purposes. Here they are, for the interested. These start at row 2 (assuming a header row) and should be filled down. Note that I am not an Excel developer so these may not be the world’s most efficient formulas.


Object Name: =IFERROR(MID(A2,FIND("'",A2,1)+1,FIND("'",A2,FIND("'",A2,1)+1)-FIND("'",A2,1)-1),"")

Scan Count: =IF(LEN(A2)>0,IFERROR(MID(A2,FIND("Scan count",A2,1)+10,FIND(",",A2,1)-FIND("Scan count",A2,1)-10)+0,""),"")

Logical Reads: =IF(LEN(A2)>0,IFERROR(MID(A2,FIND("logical reads",A2,1)+13,FIND(",",A2,FIND("logical reads",A2,1))-FIND("logical reads",A2,1)-13)+0,""),"")

Physical Reads: =IF(LEN(A2)>0,IFERROR(MID(A2,FIND("physical reads",A2,1)+14,FIND(",",A2,FIND("physical reads",A2,1))-FIND("physical reads",A2,1)-14)+0,""),"")

If you’re interested in read-ahead reads, lob logical reads, lob physical reads, or lob read-ahead reads it would not take a lot of doing to add columns for those as well. I’m usually just using this to narrow down an IO issue in a large dump so I have not included them.

If you feel like downloading the Excel workbook I use, it is here. I can make no warranty and warn you to use at your own risk, but it’s really just a nicely formatted version of the formulas above.

March 7, 2012 Posted by: 10 Comments - Permalink
10 Responses to Using Excel to parse Set Statistics IO output
  1. Phil says:

    Good stuff Vicky. Unfortunately, when I cut and paste to excel the formulas don’t work due to the “smart quotes” in your sample code.

    • Vicky says:

      You’re right, I had not noticed that! I’ve just moved that section into a code block – not as pretty but much more functional. Sorry about that!

  2. Phil says:

    Looks like there’s a typo in the formulas…

  3. [...] As far as I know all the credit for the stroke of brilliance to parse and sort SQL's SET STATISTICS IO in Excel goes to Vicky Harp. You can find her post on how to do it over at her vickyharp.com. [...]

    • Andrew says:

      This is pretty awesome, thanks for sharing! I was about to write a regex parser to do the same thing, but this is much easier to use!

  4. Chris Davis says:

    Vicky, AWESOME! I love this spreadsheet. I have been a developer for many years and just now getting into some DBA functions. That is one awesome piece of work…I love it.

  5. […] (which still isn’t exactly plain English) is on the Messages tab.  My coworker pointed me HERE where Vicky Harp has created a simple Excel document with some formulas to parse out the response […]

  6. Hi Vicky – your post inspired me to do something similar, but using a stored procedure to parse the output (a little tricky, but it can be done with XML). Check it out: http://databasesarefun.blogspot.com/2014/04/easily-parse-output-of-set-statistics.html.

    thanks,
    Sylvia

  7. Mark Freeman says:

    I have been using http://www.statisticsparser.com. You just paste the output from SSMS into the web page and click the Parse button and you get a nicely formatted table, sortable (ascending or descending) on each column and with totals at the bottom. It handles output from SET STATISTICS TIME as well, and the output from multiple statements. It even displays error messages that are buried in all the text.

  8. Vicky says:

    Absolutely, that’s a great site, and one which didn’t exist yet back when I first wrote this post. I still use my Excel sheet just since it makes it easy for me to keep multiple sheets for a single query as I change it over the course of a debug session.

Leave a Reply

%d bloggers like this: