SQL Server performance optimization (1) using the Set function

Before you start, take a look at Microsoft's recommendations: In the overall performance optimization of the system, the TSQL optimization priority is not the highest.

This article consists of four parts:

• SET STATISTICS time on
• SET STATISTICS IO
• SET Showplan_all on
• SET STATISTICS profile on

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.

10000* from measure_heat select Top 10000 * from measure_heat ORDER by ID desc

Show Results:

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

10000* from measure_heat select Top 10000 * from measure_heat ORDER by ID 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.

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
Set STATISTICS profiles on-Each query executed 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