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.

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.

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.

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.

• Find Expensive Queries – SQL in Sixty Seconds #159
• Case-Sensitive Search – SQL in Sixty Seconds #158
• Wait Stats for Performance – SQL in Sixty Seconds #157
• Multiple Backup Copies Stripped – SQL in Sixty Seconds #156

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