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)ASSELECT o.OrderID, o.OrderDate, ol.OrderLineID, ol.Quantity, ol.UnitPriceFROM Sales.OrderLines olINNER JOIN Sales.Orders o ON ol.OrderID = o.OrderIDWHERE ol.UnitPrice = @UnitPrice OPTION (USE HINT ('DISABLE_BATCH_MODE_ADAPTIVE_JOINS')); ...
