Performance Stats Analysis with Statistics Parser

I have seen a lot of people using STATISTICS IO and STATISTICS TIME commands to measure query performance and these are generally very useful and effective for measuring or comparing the performance stats. However it gets tricky when you start using these to troubleshoot your lengthy script that references multiple tables resulting in a very large output and it gets quite difficult to conclude whether the change you have made had a positive effect or not. I might be able to help you out here.

Let’s go through the below process to see if there is an easy way to assess the stats.

If you are assessing performance of a query or stored procedure, do not forget to run following commands to clean all buffers from buffer pool and remove plans from plan cache (it is not recommended to run following commands on Production):

DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE

Set statistics IO and Time on with following command:

SET STATISTICS IO, TIME ON

I executed my script to showcase an example. You can see following statistics output generated from my script:

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

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 78 ms, elapsed time = 93 ms.
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 2, logical reads 1741, 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 ‘Address’. Scan count 0, logical reads 605, 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 1791, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘Employee’. Scan count 579, logical reads 1449, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘BusinessEntity’. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘BusinessEntityContact’. Scan count 1, logical reads 5, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘SalesOrderHeader’. Scan count 1, logical reads 689, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘BillOfMaterials’. Scan count 1, logical reads 22, 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 = 32 ms, elapsed time = 132 ms.

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

It is quite evident that the statistics output is quite long and in such a case you would need to sum all the relevant numbers manually to measure Logical Reads, Physical Reads and other parameters which becomes bit of a pain.

Alternatively, I would suggest to use free web-based application called Statistics Parser.

This application will make your life much easier as it is very simple and easy to use, please see following screenshot:

You need to copy the Statistics output from SSMS into big textbox and click “Parse” button.

The application will convert the raw statistics in an easily readable format as below:

Clearly this has become quite simpler to read. Now to compare the results, copy them to excel in below format:

	Before
Logical Reads	6,306
Scan count	585
Physical Reads	0
CPU Time	0.22
Elapsed Time	0.45

To capture “After” stats, I have optimised my query by adding a missing index which will reduce the numbers subsequently, so I would need to follow the same process to get the stats again as below:

We can clearly notice the differences between “Before” and “After” stats, however to present them nicely, I would suggest you to paste them in following format so that both the stats can be compared together and presented to non-technical audience as well if required.

	Before	After
Logical Reads	6,306	5,442
Scan count	585	297
Physical Reads	0	0
CPU Time	0.22	0.188
Elapsed Time	0.45	0.42

You can add “percentage improvement” column next to “After” column if you want to show the difference too.

Thanks

Advertisement