Hello everybody!
All quiet with you?
In this post, I would like to present to you a very useful tool in the daily life of those who work with Performance Tuning and constantly need to enable the STATISTICS TIME and / or IO options to view some information about these queries that may assist in the activity of query optimization (Want some tips on Performance Tuning? Access this post).
Anyone who has used these parameters of the SET command knows that often, the formatting of the output is not very intuitive, especially when there are several queries in the statement, using several tables, making it difficult to understand this information.
To help us in this Performance Tuning study, Richie Rump has developed a web application where you paste the output from the Messages tab of SQL Server Management Studio (SSMS) and this tool converts the information into HTML table format, allowing you to view it. clear, objective and clean, and breaking, allowing us to sort the information, as by the amount of logical reads, for example.
If you want to know and start using Statistics Parser, just access the link http://statisticsparser.com/.
Example of use
I will demonstrate a practical example of how the Statistics Parser tool makes it easy to parse this output.
STATISTICS TIME and STATISTICS IO standard output
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
SQL Server parse and compile time: CPU time = 31 ms, elapsed time = 103 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. (10000 rows affected) Table 'Tabela'. Scan count 1, logical reads 556, physical reads 1, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'Sites_RSS'. Scan count 1, logical reads 33, physical reads 1, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'Historico_Deadlocks'. Scan count 1, logical reads 27, physical reads 2, read-ahead reads 0, lob logical reads 7923, lob physical reads 0, lob read-ahead reads 7279. Table 'Feriado_ES'. Scan count 1, logical reads 2, physical reads 1, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'Feriado'. Scan count 1, logical reads 1, physical reads 1, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'Fato_Venda'. Scan count 1, logical reads 1, physical reads 1, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'Dim_Forma_Pagamento'. Scan count 1, logical reads 1, physical reads 1, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'Enderecos'. Scan count 1, logical reads 2, physical reads 1, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'Dim_Produto'. Scan count 1, logical reads 1, physical reads 1, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'Dia_Util'. Scan count 1, logical reads 2, physical reads 2, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'Dim_Cliente'. Scan count 1, logical reads 1, physical reads 1, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'Conteudo_RSS'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 22, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'Clientes'. Scan count 1, logical reads 2, physical reads 1, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. SQL Server Execution Times: CPU time = 203 ms, elapsed time = 3486 ms. |
Output Using the Statistics Parser Tool
Other alternatives
If you are looking for other, more complete alternatives to Statistics Parser, I recommend SQLSentry Plan Explorer, an excellent tool that I personally enjoy and use whenever I need to optimize a query (soon I will post only about it)
Example of use
That's it folks!
Hope you enjoyed this post, a hug and see you next time!
