You can disable adaptive batch mode joins by using the DISABLE_BATCH_MODE_ADAPTIVE_JOINS database scoped configuration option, as shown in the following code:

ALTER DATABASE SCOPED CONFIGURATION SET DISABLE_BATCH_MODE_ADAPTIVE_JOINS = ON;

You can also use the query hint DISABLE_BATCH_MODE_ADAPTIVE_JOINS to disable this feature. To demonstrate this, recreate the stored procedure from the beginning of this section:

CREATE OR ALTER PROCEDURE dbo.GetSomeOrderDeatils
@UnitPrice DECIMAL(18,2)
AS
SELECT o.OrderID, o.OrderDate, ol.OrderLineID, ol.Quantity, ol.UnitPrice
FROM Sales.OrderLines ol
INNER JOIN Sales.Orders o ON ol.OrderID = o.OrderID
WHERE ol.UnitPrice = @UnitPrice 
OPTION (USE HINT ('DISABLE_BATCH_MODE_ADAPTIVE_JOINS'));

Now, invoke the procedure, as you already did at the beginning of this section:

EXEC dbo.GetSomeOrderDeatils 112;

Finally, observe the execution plan, shown as follows:

As expected, no Adaptive Join operator is shown. The fact that you have two options to disable the feature means that the feature is not applicable for many queries. If your queries perform well, or you know that you want to use the Nested Loop Join operator in the execution plan for your query, you might want to avoid overhead introduced by the hash join building phase of the Adaptive Join operator.

Batch Mode Adaptive Join allows SQL Server to handle one query with two different execution plans without plan recompiling. The Adaptive Join operator's threshold depends on the estimation and parameters used in the first invocation. If the estimation is wrong and initial parameters are not representative, you can still expect performance issues due to parameter sniffing. Adaptive Join in an execution plan does not guarantee usage of an appropriate join operator; you can still see Hash Match Join where you expected Nested Loop Join and vice versa. However, Adaptive Join will almost always use the expected Nested Loop Join operator for a small number of rows, which was not the case prior to SQL Server 2017.