Adaptive Threshold Rows
source link: https://blog.sqlauthority.com/2021/03/25/sql-server-adaptive-threshold-rows/?utm_campaign=sql-server-adaptive-threshold-rows
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
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 nested loop 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)
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK