SQL SERVER – Adaptive Threshold Rows

SQL Server 2019 was released almost 2 years ago and now most of the clients of Comprehensive Database Performance Health Check have started to switch to it. Today we are going to learn about what drives the decision to join Adaptive Join. Well, the answer is very simple – Adaptive Threshold Rows.

SQL SERVER - Adaptive Threshold Rows AdaptiveThresholdRows-800x488

While I find SQL Server 2019 very stable and robust, few of my clients face performance regression after the upgrade. Fortunately, that can be easily handled with few configuration changes which we discuss during Health Check Consultancy. When I was demonstrating to my client how adaptive joins help improve SQL Server performance, the client asked me what drives the decision taken by adaptive joins. Well, the answer is pretty straight forward and I will try to explain you with an example.

Adaptive Threshold Rows

First, enable SQL Server Execution Plan in SSMS. Here is how you can do that 3 Different Ways to Explore Actual Execution Plans. Next, make sure that you have installed the sample database WideWorldImporters. Now run the following two queries one after another and see their execution plan.

Query 1: Adaptive Join – Nested Loop

Let us run the following query and check the execution plan.

USE [WideWorldImporters]
GO
SELECT ol.OrderID,ol.StockItemID,ol.Description,
ol.OrderLineID,
o.Comments, o.CustomerID
FROM Sales.OrderLines ol
INNER JOIN Sales.Orders o ON ol.OrderID = o.OrderID
WHERE ol.StockItemID = 226
GO

The query above will give return 150 rows. Now let us examine the execution plan.

SQL SERVER - Adaptive Threshold Rows adaptive2

In the execution plan, we can see that the Adaptive Threshold Rows is more than an actual number of rows. SQL Server Engine has preferred nested loop join.

Query 2: Adaptive Join – Hash Join

Let us run the following query and check the execution plan.

USE [WideWorldImporters] 
GO
SELECT ol.OrderID,ol.StockItemID,ol.Description,
ol.OrderLineID,
o.Comments, o.CustomerID
FROM Sales.OrderLines ol 
INNER JOIN Sales.Orders o ON ol.OrderID = o.OrderID
WHERE ol.StockItemID = 168
GO

The query above will give return 972 rows. Now let us examine the execution plan.

SQL SERVER - Adaptive Threshold Rows adaptive1

In the execution plan, we can see that the Adaptive Threshold Rows is less than an actual number of rows. SQL Server Engine has preferred Hash Match join.

SQL Server engine makes the run time decision for the join when it evaluates both sides of Join Input. Based on the logic of the Threshold mentioned above, it makes the decision of which type of join to be used in the query.

Well, that’s it for today. I will write more about this in future blog posts.

Let me know if you are interested to know more about this topic and I will write more blogs as well as create an SQL in Sixty Seconds video.

Here are my few recent videos and I would like to know what is your feedback about them.

Reference: Pinal Dave (https://blog.sqlauthority.com)

, , ,
Previous Post
SQL SERVER – Table or View – OBJECTPROPERTY
Next Post
Kali Linux Installation Error Fix: An installation step failed. You can try to run the failing item again from the menu, or skip it and choose something else

Related Posts

2 Comments. Leave new

  • Hello Pinal Sir,

    I have saw a typo in the below statement –

    “In the execution plan, we can see that the Adaptive Threshold Rows is less than an actual number of rows. SQL Server Engine has preferred nested loop join.”

    It should be –
    “In the execution plan, we can see that the Adaptive Threshold Rows is less than an actual number of rows. SQL Server Engine has preferred Hash Match Join.”

    Thanks & Regards,
    One of your good reader.

    Reply

Leave a Reply

Menu