Understanding SQL Server Execution Plans

The previous article summarizes SQL Server Profiler, which is used primarily to monitor the database and to track the generated SQL statements. But it's no use just to get the generated SQL statements, we can use these SQL statements and then analyze the performance of the SQL statements in conjunction with the execution plan, which is our ultimate goal, so how do we use the execution plan? I am prepared to summarize from the following points.

1. How to start an execution plan
2. What to look for in the execution plan results
3. Five ways to find SQL Server
4. View more specific execution procedures

How to start an execution plan

Run a SQL and select the ' Include Actual Execution Plan ' button in the toolbar to launch the execution plan, such as.

　　

SQL statements:

 use   TSQLFundamentals2008;  go  --  query all orders from July 1, 2006 to July 31, 2007, and Sort by RequiredDate field  select  orderid,orderdate,requireddate,shippeddate,shipname,shipaddress Span style= "color: #0000ff;" >from   sales.orders  where  Orderdate>=   20060701    and  orderdate<    20070801    order  by  RequiredDate; 

　　Execute the query, and you can see the execution plan results in the results bar, such as.

　　

What to look for in the execution plan results

The results of the execution plan come out, so how do we look at it? In general, we only need to focus on the following parameters.

1. Which step is expensive. More expensive places indicate that this place is time-consuming to query.
2. Which steps affect more rows of data. This can be judged by the thickness of the connection line.
3. Do something at every step.

Five ways to query SQL Server

To understand the five ways SQL Server is queried, we need to figure out two concepts, both indexes in SQL Server, clustered and nonclustered indexes. A "clustered index" directly determines where a record is stored, or it can be obtained directly from a clustered index, and the primary key of our table is usually a clustered index. The nonclustered index holds two messages, 1. The value of the corresponding indexed field. 2. Record the location corresponding to the clustered index (save the record pointer if the table does not have a clustered index). Therefore, if you can find records by using a clustered index, the speed is the fastest.

Here are five ways SQL Server queries data, which is important for us to understand the execution plan. Five ways are as follows.

1. Table Scan: Traverse the entire table to find matching rows of data at the slowest speed.
2. Index Scan: Filters out a subset of records from the table by index, then finds all matching rows of data. The query speed is slightly faster than the table scan.
3. "Index Seek": According to the index, locate the location of the record, and then get the record, so its query speed than the previous two kinds of faster.
4. "Clustered index Scan": traverses the entire table by a clustered index (typically the primary key), because its records are stored sequentially in the clustered index. Note that it differs from the table scan in that they all perform a full table scan, except that the table scan is a scan without an index, and the clustered index scan is scanned by a clustered index.
5. "Clustered index Seek": The clustered index gets the record, it takes the record directly, and does not perform a full table scan, so its query speed is the fastest.

When we look at the execution plan results, if we see "Table Scan", it means that the table does not have any indexes, including the clustered index. But often see more is "Clustered Index Scan", that the query or scan the speed of the table, but by the clustered index, the actual effect or "Table Scan" is no different, so, at this time we may want to consider establishing a ' composite field index '.

View more specific execution procedures

The result diagram of the execution plan we can only see which one is time consuming, but we don't see how SQL Server executes. To see more clearly, we can look at the following code with a SQL command.

SET STATISTICS on;

Execution results in addition to returning datasets and execution plans, a table is returned showing the specific execution process, such as.

This result we focus on the following points:

1. STMT Text: The steps performed are described in detail and generally look outward from the inner layer.
2. Rows: Indicates the number of records produced by the execution step.
3. Executes: Indicates the number of records that an execution step was executed.

Resources

1,fish Li's Read SQL Server query plan

2, the Pastoral cricket programmer in the eyes of the SQL server-execution plan teaches me how to create an index?

Understanding SQL Server Execution Plans