Geek City: Format STATISTICS IO Output

I was in Wrocław Poland a couple of weeks ago, delivering a precon and two regular sessions at the SQLDay conference. I also got to attend a couple of sessions while I was there and I learned some new things. 

One thing was how cool the new Spotlight Presentation Remote from Logitech is. Two different presenters were using it. It allows you to magnify a section of your screen even when you're not at your computer. Just point to the big display monitor, press a magic button, and whatever area you've pointed to is enlarged, so you can call out details. So I ordered one before I got home and it was waiting for me. The problem is, I don't have another conference scheduled until the end of July at SQL Saturday Sacramento, so I won't be able to play with this new toy in front of an audience for almost 2 months!

One other new thing I learned about was a cool utility called Statistics Parser, built and maintained by Richie Rump (blog | twitter) and the fine folks at Jorriss LLC.

The tool lets you paste the output from STATISTICS IO into a text box on the web page, and then it displays the information about each of the tables accessed in a very nice readable format.  It works with multiple queries, and will give you the STATISTICS IO from each query, and then a total for all the queries. The only thing I wished it did differently was to not show the totals section if there is only one query, because then the two output sections are just duplicates. 

Here's an example. I wanted a query with lots of tables, so I found the Sales.vIndividualCustomer view in the Adventureworks2016 database that is a join of 10 tables. When I query the view looking for email addresses for people named 'Jim' or 'Jimmy', all 10 tables are accessed. (This query also works with Adventureworks2014.) 

SET STATISTICS IO ON;
GO
SELECT  *
FROM    AdventureWorks2016.Sales.vIndividualCustomer
WHERE   EmailAddress LIKE 'jim%';
GO

The STATISTICS IO output in the results looks like:

Table 'PhoneNumberType'. Scan count 1, logical reads 53, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'PersonPhone'. Scan count 26, logical reads 52, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'AddressType'. Scan count 0, logical reads 52, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'CountryRegion'. Scan count 0, logical reads 52, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'StateProvince'. Scan count 0, logical reads 52, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Address'. Scan count 0, logical reads 61, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'BusinessEntityAddress'. Scan count 26, logical reads 72, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Person'. Scan count 0, logical reads 87, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Workfile'. 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 'Customer'. Scan count 1, logical reads 123, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'EmailAddress'. 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.

So I pasted the above into StatisticsParser, and got the following: 

StatisticsParser.jpg

Actually, I got the above image twice, once for the query, and once for the total. The StatisticParser can also parse and format STATISTICS TIME output if you capture that as well. 

Way cool, right?

Have fun!

Ambigram.jpg