Adaptive Query Processing and Automatic Tuning – Adaptive Joins – 2

Nagaraj Venkatesan, 2017-11-29

Continuing from the introduction written over here, let’s look at one of the aspects of Adaptive Query Processing which is Adaptive Joins.

Before we start Adaptive Joins, SQL Server’s Query optimizer internally has 3 join types.

Nested Loop Join – Usually picked by optimizer, when tables participating in the joins are small or when one of the table is big and indexed on joining column
Hash Join: One of the table is big but doesn’t have an index
Merge Join: Both the tables are big, and the joining column is indexed

So, the size of the table or the rows participating in the join plays a major role SQL’s Query optimizer picking the correct join type. Let’s say, SQL Server estimates few rows participating in the join at compile time and picks “nested join” but during runtime, the join extracts larger number of rows and thereby making the choice of plan ineffective. 

SQL Server 2017’s adaptive join addresses this problem. With Adaptive Join, SQL Server comes with plan attribute called “Adaptive Row Threshold” which is a row count threshold estimated by SQL Server.  “Adaptive Row Threshold” will help SQL Server dynamically alter the plan choice at run time. Adaptive join in short will work in the following way

  • If the number of rows participating in the join are greater than “Adaptive Row Threshold” rows, then “Hash Join” operator is used for join operation
  • If the number of rows participating in the join are lesser than “Adaptive Row Threshold” rows, then “Nested loop Join” operator is used for join operation
Consider the following query:
SELECT  [fo].[Order Key], [si].[Lead Time Days], [fo].[Quantity]
FROM    [Fact].[Order] AS [fo]
INNER JOIN [Dimension].[Stock Item] AS [si]
       ON [fo].[Stock Item Key] = [si].[Stock Item Key]
WHERE   [fo].[Quantity] = 360;

Observe the picture below:
Query Optimizer fixes 68 rows as Adaptive join threshold.
Estimated number of rows for the outer table in the join was 213. As 213 is greater than 68, estimated join type is Hash Match Join. During runtime, 206 rows from outer table participated in the join and as it was greater than “Adaptive join threshold” of 68, Hash match join was selected.
Sharing another example where Adaptive Join switches the join type in runtime. Refer to pic below
In this case, threshold was 46 rows and estimate were 87 rows. However, runtime row count was just 36 rows hence switching the join type to Nested loop join from an estimate of “Hash Join”
 The ability to alter execution plan operator in runtime would make SQL Server’s query optimizer weed out most of the incorrect plan choices during runtime.

Original post (opens in new tab)

Rate

Share

Share

Rate

Adaptive Query Processing and Automatic Tuning – Adaptive Joins – 2

Nagaraj Venkatesan, 2017-11-29

Continuing from the introduction written over here, let’s look at one of the aspects of Adaptive Query Processing which is Adaptive Joins.

Before we start Adaptive Joins, SQL Server’s Query optimizer internally has 3 join types.

Nested Loop Join – Usually picked by optimizer, when tables participating in the joins are small or when one of the table is big and indexed on joining column
Hash Join: One of the table is big but doesn’t have an index
Merge Join: Both the tables are big, and the joining column is indexed

So, the size of the table or the rows participating in the join plays a major role SQL’s Query optimizer picking the correct join type. Let’s say, SQL Server estimates few rows participating in the join at compile time and picks “nested join” but during runtime, the join extracts larger number of rows and thereby making the choice of plan ineffective. 

SQL Server 2017’s adaptive join addresses this problem. With Adaptive Join, SQL Server comes with plan attribute called “Adaptive Row Threshold” which is a row count threshold estimated by SQL Server.  “Adaptive Row Threshold” will help SQL Server dynamically alter the plan choice at run time. Adaptive join in short will work in the following way

  • If the number of rows participating in the join are greater than “Adaptive Row Threshold” rows, then “Hash Join” operator is used for join operation
  • If the number of rows participating in the join are lesser than “Adaptive Row Threshold” rows, then “Nested loop Join” operator is used for join operation
Consider the following query:
SELECT  [fo].[Order Key], [si].[Lead Time Days], [fo].[Quantity]
FROM    [Fact].[Order] AS [fo]
INNER JOIN [Dimension].[Stock Item] AS [si]
       ON [fo].[Stock Item Key] = [si].[Stock Item Key]
WHERE   [fo].[Quantity] = 360;

Observe the picture below:
Query Optimizer fixes 68 rows as Adaptive join threshold.
Estimated number of rows for the outer table in the join was 213. As 213 is greater than 68, estimated join type is Hash Match Join. During runtime, 206 rows from outer table participated in the join and as it was greater than “Adaptive join threshold” of 68, Hash match join was selected.
Sharing another example where Adaptive Join switches the join type in runtime. Refer to pic below
In this case, threshold was 46 rows and estimate were 87 rows. However, runtime row count was just 36 rows hence switching the join type to Nested loop join from an estimate of “Hash Join”
 The ability to alter execution plan operator in runtime would make SQL Server’s query optimizer weed out most of the incorrect plan choices during runtime.

Rate

Share

Share

Rate

Related content

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.

Robert Davis

2009-02-23

1,567 reads

Networking – Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I’d like to talk about social networking. We’ll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let…

Andy Warren

2009-02-17

1,530 reads

Speaking at Community Events – More Thoughts

Last week I posted Speaking at Community Events – Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I’ve got a few more thoughts on the topic this week, and I look forward to your comments.

Andy Warren

2009-02-13

360 reads