SQL SERVER – Disable Adaptive Joins

Earlier I wrote few blog posts on SQL SERVER – Adaptive Threshold Rows and SQL SERVER – Enable Adaptive Join. After reading them my client of Comprehensive Database Performance Health Check sent me an interesting scenario where a query with adaptive join was performing badly. Let us learn how to disable adaptive joins.

Disable Adaptive Joins

From what I know and what I have seen, in SQL Server 2017 and 2019 adaptive joins have actually blessings for the query as they are helping them to perform better. However, there are always some unique situations and as they say – Never say Never.

My client found some queries which are working fine before in SQL Server 2016 are running poorly in SQL Server 2019. After lots of research and testing, they figured out when the query has adaptive join kick in the performance of the query was very poor. While they have seen most of the queries getting amazing benefit from this feature they found that there were 3 queries that needed these features to be disabled.

Let us learn how we can do that. We will be using the same example which we have earlier used in this blog post.

If you want to disable the feature of adaptive join, you can do it with the additional option command.

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
OPTION (USE HINT('DISABLE_BATCH_MODE_ADAPTIVE_JOINS'));
GO

Well, that’s it for today. Let me know if you liked this topic and would want me to create a video on this topic at SQL in Sixty Seconds series.

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