To disable adaptive join for one query, add a USE HINT to it. This post also shows a Query Store hint for code you cannot edit and the database setting that switches the feature off for everything. Each switch is tested on a small demo database and checked in the plan.
Enable Adaptive Join in SQL Server: Why It Does Not Appear
To enable adaptive join in SQL Server, the compatibility level, the database setting and the query must all agree. This post builds a demo database and probes the plan under each condition. It also corrects older advice whose statements switch the feature off.
Adaptive Threshold Rows: How an Adaptive Join Chooses
Adaptive threshold rows is the row count at which an adaptive join switches from nested loops to a hash join. This post runs two queries on WideWorldImporters, reads each threshold from the cached plan, and shows which branch ran. The first query stays below its threshold and the second goes above it.
Auditing Query Hints Left in Production Code
Start auditing query hints in modules and Query Store, rank the busiest statements, and test removal with measured plans and rollback.
Ten Plan Operators Explained: An Operator Cheat Sheet
Seek, scan, lookup, three joins, sort, aggregate, spool and parallelism, each shown with a tiny demo. Learn to judge a plan by rows and executions.
Find a Table in Execution Plan – SQL in Sixty Seconds #151
Finding a table in a huge execution plan is a nightmare. In this SQL in Sixty Seconds video, I show how to find any table in execution plan with SSMS.
Search a Query Plan for a Table: Find Node and XQuery
Search a query plan for a table with the Find Node feature in SSMS, or with XQuery over the plan XML. A demo query reads one table twice, and both methods find both visits. The post also shows how to save a plan file for later.