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/
Make a more new posts please 🙂
___
Sanny
Few new posts has been created