SQL Server Performance Tuning Execution plan (execution plan) tuning

Three Join strategies in the execution plan

There are three join strategies for SQL Server: Hash join,merge join,nested Loop join.

hash Join: Used to handle data that is not ordered/not indexed, it creates a hash table in memory of the data (the associated key) on both sides of the Join. For example, with the following query statement, the associated two tables are not indexed, and the execution plan is displayed as a hash Join.

SELECT  sh.*from  salesordheaderdemo as Shjoin  Salesorddetaildemo as Sdon  Sh. Salesorderid=sd. Salesorderidgo

Merge Join: Used to process indexed data, which is lighter than hash join. We index the associated columns of the previous two tables, and then again the above query, the execution plan changes to the merge Join

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

Nested Loop Join: On the basis of meeting the merge join, if there is less data on one side, SQL Server takes the one with less data as an outer loop and the other as an internal loop to complete the join process. Continuing with the previous example, adding a where statement to the query statement to reduce the amount of data on the Join side, the execution plan appears as a nested Loop Join.

SELECT  sh.*from  salesordheaderdemo as Shjoin  Salesorddetaildemo as Sdon  Sh. Salesorderid=sd. Salesorderidwhere  Sh. salesorderid=43659

Improvements in the execution plan (Table/index scan)

On many occasions we need to extract a small amount of data from a table that contains a lot of data, so scan should be avoided because scan processing will traverse each line, which is time consuming. Let's look at an example:

SELECT  Sh. Salesorderidfrom  Salesordheaderdemo as Shjoin  Salesorddetaildemo as Sdon  Sh. Salesorderid=sd. Salesorderidwhere  Sh. Orderdate= ' 2005-07-01 00:00:00.000 ' GO

The red circle in the diagram marks the table scan, and the execution plan is intelligently recommended for indexing. Let's first try to build an index on the Salesordheader table:

CREATE UNIQUE CLUSTERED INDEX idx_salesorderheaderdemo_salesorderid on Salesordheaderdemo (SalesOrderID) GO

Then execute the same query statement again, and the execution plan becomes the following:

Table Scan changes to index scan and continues to index another table:

CREATE UNIQUE CLUSTERED INDEX idx_salesdetail_salesorderlid on Salesorddetaildemo (salesorderid,salesorderdetailid) GO

The following changes have occurred in the execution plan:

While it is not possible to say that Scan is worse than seek, seek is a better choice for most occasions (especially when finding small amounts of data in many data). For example, if you have a table of billions of data and you want to take 100 of them, you should make sure that you use Seek, but if you need to take out most of the data (say 95%), Scan might be better.

In addition, you may notice that both tables are indexed but one table is represented as Clustered Index scan in the execution plan, and the other is Clustered index seek, and we are not looking for two Clustered index seek? This is because the previous table does not have an assertion (predicate), and the latter table asserts the SalesOrderID with the on keyword.

Key Lookup in the execution plan

For the following example, we first set up two different indexes on the same table:

CREATE UNIQUE CLUSTERED INDEX idx_salesdetail_salesorderlid on Salesorddetaildemo (salesorderid,salesorderdetailid) gocreate nonclustered INDEX idx_non_clust_salesorddetaildemo_modifieddate on Salesorddetaildemo (ModifiedDate) GO

Execute the following query:

SELECT  modifieddatefrom salesorddetaildemo  WHERE modifieddate= ' 2005-07-01 00:00:00.000 ' GO

Execution plan For example, he used the non-clustered index, which we previously established on the ModifiedDate field, to become an index Seek treatment.

Let's change the query statement by adding two additional fields to the SELECT:

SELECT  ModifiedDate,  SalesOrderID,  salesorderdetailidfrom salesorddetaildemowhere modifieddate= ' 2005-07-01 00:00:00.000 ' GO

The execution plan, like, basically unchanged:

The above selected field is not belong to non-clustered index is Clustered index, if you add a few other fields?

SELECT  ModifiedDate,  SalesOrderID,  salesorderdetailid,  ProductID,  unitpricefrom Salesorddetaildemowhere modifieddate= ' 2005-07-01 00:00:00.000 ' GO

Baby, execute the plan a little more. Two processing (Key Lookup, Nested Loop):

Key lookup is a heavy processing, and we can avoid key lookup by using the keyword with to specify the use of Clustered Index.

SELECT  ModifiedDate,  SalesOrderID,  salesorderdetailid,  ProductID,  unitpricefrom Salesorddetaildemo with (index=idx_salesdetail_salesorderlid) WHERE modifieddate= ' 2005-07-01 00:00:00.000 ' GO

The execution plan turns into a Clustered Index Scan:

Previously mentioned Scan does not seem to be a good deal, then the dwarf in a higher, using SET STATISTICS IO on to compare:

SET STATISTICS IO ongoselect  ModifiedDate,  SalesOrderID,  salesorderdetailid,  ProductID,  Unitpricefrom salesorddetaildemowhere modifieddate= ' 2005-07-01 00:00:00.000 ' goselect  ModifiedDate,  SalesOrderID,  salesorderdetailid,  ProductID,  unitpricefrom Salesorddetaildemo with (index=idx_ Salesdetail_salesorderlid) WHERE modifieddate= ' 2005-07-01 00:00:00.000 ' goselect  ModifiedDate,  SalesOrderID,  salesorderdetailid,  ProductID,  unitpricefrom Salesorddetaildemo with (index=idx_non_ clust_salesorddetaildemo_modifieddate) WHERE modifieddate= ' 2005-07-01 00:00:00.000 ' GO

Compared with the clustered index query performance is the worst, and the SET STATISTICS IO output Data clustered index query on logical reads spent more time.

Looks like the non-clustered index + Key lookup execution plan is good, but if you can avoid Key lookup is perfect, let's revise non-clustered index to include it in the index with the Include keyword His fields are:

DROP index idx_non_clust_salesorddetaildemo_modifieddate on salesorddetaildemogocreate nonclustered index Idx_non_ Clust_salesorddetaildemo_modifieddate on Salesorddetaildemo (ModifiedDate) INCLUDE (  ProductID,  UnitPrice) go--clear the cache, only for the development environment! DBCC FREEPROCCACHEDBCC Dropcleanbuffersgo

Execute the previous query again:

SELECT  ModifiedDate,  SalesOrderID,  salesorderdetailid,  ProductID,  unitpricefrom Salesorddetaildemowhere modifieddate= ' 2005-07-01 00:00:00.000 ' GO

This is perfect because our query fields are included in the index, so the execution plan is eventually optimized to index Seek.