SQL Server performance optimization (1) using the Set function

The SET function is primarily intended to show the SQL execution query plan, CPU, and hard disk usage.

1. Set STATISTICS time on: When SET STATISTICS Times is on, the temporal statistics of the statement are displayed. When OFF, time statistics are not displayed.

SET STATISTICS  on Select Top 10000 *  from measure_heat Select Top 10000 *  from Order  by desc

Show Results:

2. SET STATISTICS IO: Information on the amount of disk activity generated.

SET STATISTICS  on Select Top 10000 *  from measure_heat Select Top 10000 *  from Order  by desc

Show Results:

For concepts such as logical reads, physical reads, and LOB reads, Microsoft's explanation is:

For LOB concept, I looked for a long time on the internet did not find, and finally found in the official MSDN Libiary, once again to prove the information to go to the official website AH.

3. SET Showplan_all on. You can use this statement if you want to display a statement that executes a schedule at query time. The execution plan is a strong basis for our index optimization.

SET  on Select Top 10000 *  from Measure_heat

Show Results:

The results are rather ugly and clear, we can use the graphical visualization of the way to view, the display is more intuitive.

4. There are also set functions related to performance analysis, such as

SET STATISTICS XML  --XML format output query plan SETSTATISTICS profile on  --  Each query that executes returns its regular result set

For example, when profile is set to ON, the result is much like set SHOWPLAN_ALL on, with rows and executes two columns, respectively, the actual number of rows generated by the operator and the number of operator executions:

SQL Server performance optimization (1) using the Set function