SQL Server performance optimization (1) using the Set function

Source: Internet
Author: User
Tags disk usage

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.