SQL Server: Performance Analysis of T-SQL statements using set statistics Io and set statistics time

We can use set statistics Io and set statistics time to analyze the performance of T-SQL statements.

The method is as follows:

In the SQL Server Query analyzer, run:

 
Set statistics profile onset statistics Io onset statistics time ongo -- // The SQL statement to analysisgo
 

Replace "-- // The SQL statement to analysis" with the SQL statement we want to analyze.

The following two screenshots show the execution results:

Let's take a closer look at messages:

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 execution times: 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 = 218 MS, elapsed time = 223 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. (5 row (s) affected) 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 'tblpanel '. scan count 1, logical reads 78, physical reads 3, read-ahead reads 78, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. table 'tblversion '. scan count 1, logical reads 26, physical reads 2, read-ahead reads 24, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. table 'tbllayer '. scan count 5, logical reads 25, physical reads 8, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. table 'tblcontrols '. scan count 5, logical reads 30, physical reads 13, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. table 'tblbuttoncontrol '. scan count 5, logical reads 30, physical reads 10, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. table 'tblbuttonstate '. scan count 5, logical reads 30, physical reads 9, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. table 'tblbuttoncommand '. scan count 1, logical reads 11383, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. (22 row (s) affected) CPU time = 125 MS, elapsed time = 509 Ms.

This information is very useful for our analysis and query performance.

Especially:

I. CPU execution time

 
SQL Server execution times: CPU time = 125 MS, elapsed time = 509 Ms.
 

We can see that elapsed time is a relatively large value, because the database is busy at the moment and many other tasks may be executed behind it, however, the CPU time reflects the CPU time spent in the SQL statements currently analyzed. Basically, for the same query and the same database, whether the database server is busy or not at the moment, the CPU time is a relatively stable time, which can be used to measure the actual CPU time consumption of SQL statements.

II. I/O consumption

See the following information:

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 'tblpanel '. scan count 1, logical reads 78, physical reads 3, read-ahead reads 78, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. table 'tblversion '. scan count 1, logical reads 26, physical reads 2, read-ahead reads 24, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. table 'tbllayer '. scan count 5, logical reads 25, physical reads 8, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. table 'tblcontrols '. scan count 5, logical reads 30, physical reads 13, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. table 'tblbuttoncontrol '. scan count 5, logical reads 30, physical reads 10, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. table 'tblbuttonstate '. scan count 5, logical reads 30, physical reads 9, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. table 'tblbuttoncommand '. scan count 1, logical reads 11383, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. (22 row (s) affected)

For more detailed explanations, see this article.Article:

SQL query performance debugging, with set statistics Io and set statistics time --- explain more detailed http://blog.csdn.net/leamonjxl/article/details/6639978