MS SQL has introduced adaptive query processing to get the rid of inaccurate cardinality estimates. It includes batch mode memory grant feedback,interleaved execution and batch mode adaptive joins.

INTRODUCTION:

SQL Server 2017 and Azure SQL Server has new join operator - Adaptive Join. Based on the number of rows(Threshold), It decides whether to use HASH Join or Nested Loop join at the time of actual execution.

Requirement(s):

1. Adaptive join need the column store index to perform column store index scan
2. SSMS 17.0 or newer
3. Database compatibility level 140

DEMO:

I am using AdventureWorks2016CTP3 Database for demo.

Lets change the compatibility level to 140

USE [master]
GO
ALTER DATABASE [AdventureWorks2016CTP3] SET COMPATIBILITY_LEVEL = 140


Create a column store index

USE [AdventureWorks2016CTP3]
GO
CREATE NONCLUSTERED COLUMNSTORE INDEX [CIX_OT_SalesOrderID] ON [Sales].[OrderTracking]
(
[SalesOrderID]
)
GO


Clear the procedure cache

ALTER DATABASE SCOPED CONFIGURATION CLEAR procedure_cache;
GO


Now execute the below T-SQL with actual execution plan


DECLARE @EventDetails VARCHAR(150)='Order has been processed and packaged for shipment'
SELECT
Count(OT.SalesOrderID)
FROM
Sales.SalesOrderDetail SOD
INNER JOIN
sales.OrderTracking OT
ON OT.SalesOrderID=SOD.SalesOrderID
Where
OT.EventDetails=@EventDetails
GO


As you can see that the actual execution plan has Adaptive join. Lets check the properties of adaptive join

Physical Operator: Adaptive Join

Actual Join Type: HashMatch

Estimated Join Type: HashMatch

Adaptive Threshold Rows 1547.82

Actual Number of Batches: 485120

Estimated Number of Rows: 415929

As you can see, estimated join type is HashMatch and Actual join is also HashMatch because the Actual number of rows are more than Adaptive threshold rows.

What if Actual number of rows are less than Threshold?

Lets run the same query again with different parameter value


ALTER DATABASE SCOPED CONFIGURATION CLEAR procedure_cache;
GO
DECLARE @EventDetails VARCHAR(150)='invalid address, package is undeleverable'

SELECT
Count(OT.SalesOrderID)
FROM
Sales.SalesOrderDetail SOD
INNER JOIN
sales.OrderTracking OT
ON OT.SalesOrderID=SOD.SalesOrderID
Where
OT.EventDetails=@EventDetails


Actual execution plan still looks same but lets check the property of Adaptive join.

Physical Operator: Adaptive Join

Actual Join Type: NestedLoop

Estimated Join Type: HashMatch

Adaptive Threshold Rows 1547.82

Actual Number of Batches: 5

Estimated Number of Rows: 415929

Estimated number of rows are less than the threshold so the actual join type was converted to NestedLoop while estimated join type is still HashMatch.

Follow me:

Blog: http://Sqldepository.com

YouTube : Sql depository

Facebook: https://www.facebook.com/sqldepository/

Google+ :https://plus.google.com/u/0/107093915534620159617