Sixth--optimizing performance based on execution plan (1)--understanding hash, merge, nested loops join strategy

Original: Sixth--optimize performance based on execution plan (1)--understand hash, merge, nested loops join strategy

Objective:

The articles in this series include:

1, understand the hash, Merge, Nested Loop Association strategy.

2. Find and resolve the table/index scan in the execution plan.

3. Introduce and find keys in the execution plan to find and resolve them.

For performance optimizations, the following issues need to be addressed centrally:

1. Create a performance baseline for your environment.

2, monitor the current performance and find bottlenecks.

3, solve the bottleneck to get better performance.

An estimate execution plan is a blueprint that describes how a query will be executed, and an actual execution plan is a mirror that actually occurs when a query executes. By comparing the two execution plans, you can see whether the query is actually executed according to the estimated execution plan.

In the execution plan, there are some very important operators that need to be clear:

1. Join policy: SQL Server has 3 strategies-hash, merge, nested loops. Each strategy has its pros and cons, and this chapter describes this section.

2. Scanning and lookups are two ways SQL Server reads data, both of which are core concepts in performance optimization. will be described in the next article.

3. Key lookup can sometimes become a major performance issue. The value of a non-key column in a nonclustered index is found when the store causes the need to jump from the nonclustered index to the clustered index. Such behavior is usually time-consuming.

Understanding hash, Merge, nested loop join policies

SQL Server provides a 3-in-a-join strategy with no absolute good or bad points.

1, hash (hash Join): SQL Server selects a hash association as a physical operator, while querying for bulk data, unordered, or not indexed. Two processes are associated with hash associations, which are "build" and "probe", in the "build" process, from the establishment of the input (that is, the left table of the join, but perhaps the left table will exchange positions during the optimization process, making not necessarily the actual left table.) ) reads all rows, and then creates a hash table in memory that matches the associated condition. In the probe process, all rows are read from the probe table (the right table in the input) and match the previously created memory hash table based on the associated condition.

2. Merge Join: If the associated table is sorted, SQL Server chooses to merge the associations. Merging associations requires that at least one of the associated conditions is already sorted. This is more efficient than hash correlation if the amount of data is small, and it is not a way of heavy-load correlation.

3. Nested loops (Nested loop): In a minimum of two result sets, the use of nested loops is more efficient, both of which are smaller as collections of external tables, and the inner loop result set has a valid index. This approach does not apply to large result sets.

Preparatory work:

The following creates two tables and then looks at the execution plan for each of the associated ways:

Use Adventureworksgoif object_id (' Salesordheaderdemo ') was not NULL     BEGIN        DROP TABLE salesordheaderdemo    Endgoif object_id (' Salesorddetaildemo ') is not NULL     BEGIN        DROP TABLE salesorddetaildemo    endgoselect  *into    salesordheaderdemofrom    sales.salesorderheadergoselect  *into    salesorddetaildemofrom    Sales.salesorderdetailgo

Steps:

1. Execute the query and open the execution Plan (CTRL+M):

SELECT  sh.*from    salesorddetaildemo as SD        INNER JOIN Salesordheaderdemo as sh on sh.salesorderid = Sd.salesorderidgo

2. Then you can see that the hash connection is used from the execution plan:

3. Now create a unique clustered index in two tables:

CREATE UNIQUE CLUSTERED INDEX idx_salesorderheaderdemo_salesorderid on Salesordheaderdemo (SalesOrderID) gocreate UNIQUE CLUSTERED INDEX Idx_salesdetail_salesorderid on Salesorddetaildemo (salesorderid,salesorderdetailid) GO

4. Execute the statement of Step 1 again:

5, is the second execution of the execution plan, can be found to become a merge connection, and the table scan becomes a clustered index scan:

6. Now look at nested loop associations, adding a Where condition in the query above to qualify the result set of the query:

SELECT  sh.*from    salesorddetaildemo as SD        INNER JOIN Salesordheaderdemo as sh on sh.salesorderid = Sd.salesorderidwhere   Sh.salesorderid = 43659GO

7. From the execution results, we see that the association becomes a nested loop:

Analysis:

As mentioned earlier, the hash Association works in an association where there is a large amount of data and the associated field has no sort. Therefore, in step 1, because there is no index or pre-ordering, the data association uses a hash association.

In step 3, a unique clustered index was created, so the table has been sorted by a clustered index, at which time the optimizer chooses to merge the associations.

In step 6, a nested loop association is used because the where condition limits the size of the dataset and because it is already sorted.

Each association method has its advantages and disadvantages, depending on how it is optimized. Sometimes hash associations have a very important role to play, but if you can, it is strongly recommended that each table should have a unique clustered index that uses the merge association, and if not, never try to change the association to merge or nested loops using the option prompt, which may degrade performance. Nested loops are best run only when the small result set is running.

Sixth--optimizing performance based on execution plan (1)--understanding hash, merge, nested loops join strategy