First practice of SQL Server performance optimization Execution Plan (1)

Since the last article was published, I have received a lot of attention from my friends. Many friends require more practices than pure theories. Indeed, I started to work out this series based on practical ideas! At the same time, in order to make everyone better understand and digest this knowledge, I will regularly give online video lectures on the content, and my friends can join this group: Workshop

The agenda is as follows:

• Practice Overview
• Graphic execution plan practice
• Explanation of execution plan information

Practice Overview

The execution plan can help us write highly efficient T-SQL code, but also can find out the problem of the existing T-SQL Code, also can monitor the database! Of course, it is up to us to use the execution plan at the end. But no matter what, we first learn to parse the information contained in the execution plan. The fastest way to learn is practice. Next, let's start with a practice.

In order to make it easy to understand, the examples here are not too complex. As the course continues to deepen, the subsequent examples will become more and more complex. At the same time, if you want to work together, you may want to install SQL2005 or a later version, and remember to install the AdventureWorks database. For: http://msftdbprodsamples.codeplex.com

In addition, it should be noted that, due to the relationship between data, operations, and time in the database, the execution plan generated by running the script may be different from that generated by me, there is no problem!

Graphic execution plan practice

Next we will officially enter the topic to be discussed.

First, to allow us to view the execution plan, at least ensure that we are granted permissions when logging on to the database, as shown in the following statement:

 
 

  
  
GRANT SHOWPLAN TO [username] 
 
 

To focus the discussion on the Execution Plan (estimation execution plan and actual execution plan), we run a simple query here, as shown in the following code:

 
 

  
  
SELECT * FROM [dbo].[DatabaseLog]; 
 
 

Next, let's take a look at the estimated execution plan of this statement, as described in the previous article: The estimated execution plan is the plan produced by the metadata and cost analysis algorithms used by the optimizer, this plan is an analysis before the query statement execution!

Show estimated execution plan

We can display the estimated execution plan in the following ways:

The graphic estimation query plan displayed for the preceding query statements is as follows:

Display the execution query plan

Unlike the estimated execution plan, the actual execution plan is not generated by the optimizer, and the actual execution plan is generated by the underlying storage engine during execution, this plan contains a large amount of actual underlying data and related information.

We can use the following methods to obtain the actual execution plan:

The actual execution plan of the preceding query is as follows:

In the first day, we thought there was no difference between the two, but they contained many different data information.

Next, we will begin to interpret the graphic execution plan.