In praise of StatisticsParser.com

Here’s a great free addition to your query tuning toolbox that you may well not have heard about: StatisticsParser.com. It hasn’t been mentioned nearly enough in my opinion.

Here’s how the author, Richie Rump, puts it:

Sometimes reading SQL Server ouput from Statistics IO and Statistics Time can be a total drag. This page will help with that. Just paste in the output of Statistics IO and/or Statistics Time and press Parse. Your output will be formatted and totaled. Enjoy.

Here’s a quick look at what it does.

If you want to see how long each part of a query takes or what logical reads are involved you can set a couple of options on: statistics io and statistics time.

set statistics io on;
set statistics time on;

-- List all indexes in the current DB
select	t.name as TableName
	, i.name as IndexName
	, i.*
from	sys.indexes i
	inner join sys.objects t on t.object_id = i.object_id
where	i.type > 0
	and t.type = 'U'
	and t.is_ms_shipped = 0
	and t.name not like 'sys%'
order by t.name asc
	, i.type desc
	, i.name asc;

This adds extra output to the Messages tab when you run your query:

SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 14 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

(1161 row(s) affected)
Table 'syspalvalues'. Scan count 0, logical reads 2322, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'syssingleobjrefs'. Scan count 0, logical reads 2403, 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 2566, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'sysidxstats'. Scan count 634, logical reads 1472, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 47 ms,  elapsed time = 190 ms.

It’s not that readable, even for a simple example like this. Here’s where StatisticsParser comes in. Paste in this output and hit the Parse button to see the information laid out nice and clearly:

20160122_statisticsparser

I think this is brilliant.

Thank you, Richie.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s