****************************************************************
EDIT June 19, 2014 - I saw a new tool online that looks easier than using my code. Check it out -
http://www.statisticsparser.com/
****************************************************************
Recently I was performance tuning a very involved, gigantic stored procedure. Figuring out exactly where the performance problems were was a challenge, so I turned on Statistics IO. I'm not going to get into the details of Statistics IO here, you can easily find that online. The bottom line is that I wanted to figure out how to easily get the most important metric, Logical Reads. But scanning the enormous volume of text generated was tedious, and I looked online to see what options I had to make the parsing easier and faster.
Vicky Harp has an interesting
article on using Excel to parse the output of Statistics IO. I tried it out, and it works, but I preferred to stick with SQL Server tools if possible, instead of needing to copy and paste formulas into Excel.
So I wrote the stored procedure below to parse the output. After doing some data manipulations, it uses XML to easily parse the desired values out of the Statistics IO output, and returns a result set with the table name and metrics for each line of output in Set Statistics IO.
Note!! The output of Set Statistics IO includes single quotes around the table names. For instance:
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 1, logical reads 28, 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 9, 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.
Since you can't use any text with single quotes in it as a parameter to a stored procedure, you'll need to replace the single quotes with an empty string. I'm skipping the details of how to do this - if you know enough about SQL Server to be worrying about Logical Reads, you don't need step-by-step instructions.
Once you've replaced the single quotes, use that text as a parameter for the stored procedure below, sp_StatisticsIO_OutputParse. For example:
sp_StatisticsIO_OutputParse
'
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 1, logical reads 28, 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 9, 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 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 sysscalartypes. 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.
'
I hope this helps in your troubleshooting endeavors! And if you can figure out a way to avoid the replacing of the single quotes, please let me know.
Create procedure dbo.sp_StatisticsIO_OutputParse @Output varchar(max)
as
/*
This stored procedure is designed to parse out the output you get when you turn on
Set Statistics IO (under Tools, Options, SQL Server Execution, Advanced), or just run Set Statistics IO in a query window).
! IMPORTANT - I didn't figure out how to deal with the single quotes around the table name in the output of Statistics IO.
So, in order to run this properly, you need to replace all single quotes with an empty string.
Copy the output without the single quotes, then use the output as the parameter (surrounding the whole output with single quotes) for
this procedure, sp_StatisticsIO_OutputParse.
*/
Select @Output = replace (@Output, ' reads', '_reads')
Select @Output = replace (@Output, 'Table ', '<Detail Table="')
Select @Output = replace (@Output, 'reads ', 'reads="')
Select @Output = replace (@Output, '. Scan count', '" Scancount="')
Select @Output = replace (@Output, '.', '" />')
Select @Output = replace (@Output, ',', '"')
Select @Output = replace (@Output, 'lob ', 'lob_')
Select @Output = replace (@Output, '-', '_')
Declare @OutputXML xml = '<Output> ' + @Output + ' </Output>'
SELECT
A.value('@Table[1]', 'VARCHAR(20)') as table_name
,A.value('@Scancount[1]', 'int') as scan_count
,A.value('@logical_reads[1]', 'int') as logical_reads
,A.value('@physical_reads[1]', 'int') as physical_reads
,A.value('@read_ahead_reads[1]', 'int') as read_ahead_reads
,A.value('@lob_logical_reads[1]', 'int') as lob_logical_reads
into #tmpOutput
FROM @OutputXML.nodes('Output/Detail') AS A(A)
Select * from #tmpOutput
