SQL SERVER – Enable Adaptive Join

Earlier I wrote a blog post about SQL SERVER – Adaptive Threshold Rows. After that blog post, quite a few people wrote an email to me that even though they are using SQL Server 2017 or higher they are not able to see this feature in the works. Let us learn how we can enable the adaptive join feature in SQL Server. Let us learn how we can enable the adaptive join feature in SQL Server. I often discuss this during  Comprehensive Database Performance Health Check.

Well, there can be two reasons why SQL Server 2017 or SQL Server 2019 is not using Adaptive Join.

Reason 1: Incorrect Compatibility Level

Even though you are using SQL Server 2017 or SQL Server 2019, your compatibility level may not contain values for SQL Server 2017 or 2019. If your database compatibility level is lower than SQL Server 2017, you need to set it to a higher level. Here is how you can do it.

For SQL Server 2017

ALTER DATABASE [DatabaseName]
SET COMPATIBILITY_LEVEL = 140
GO

For SQL Server 2019

ALTER DATABASE [DatabaseName] 
SET COMPATIBILITY_LEVEL = 150
GO

Reason 2: Enable Adaptive Join

It is quite possible that even though the compatibility level is the latest, it is possible that this feature is explicitly disabled.

Here is the script to enable this feature in your SQL Server.

For SQL Server 2017

ALTER DATABASE SCOPED CONFIGURATION SET DISABLE_BATCH_MODE_ADAPTIVE_JOINS = ON;

For SQL Server 2019

ALTER DATABASE SCOPED CONFIGURATION SET BATCH_MODE_ADAPTIVE_JOINS = OFF;

Please do remember that this feature is on by default when you install SQL Server so if you find it disabled in your system, someone must have done that explicitly in the past.

Summary

Once you have enabled this feature on SQL Server 2017/2019 compatibility level, you should be able to use the feature of adaptive join.

Let me know if you are interested to know more about this topic and I will write more blogs as well as create an SQL in Sixty Seconds video.

Here are my few recent videos and I would like to know what is your feedback about them.

• Find Expensive Queries – SQL in Sixty Seconds #159
• Case-Sensitive Search – SQL in Sixty Seconds #158
• Wait Stats for Performance – SQL in Sixty Seconds #157
• Multiple Backup Copies Stripped – SQL in Sixty Seconds #156

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