Is it possible to force an adaptive join with a query hint?

Question

Is it possible to force a query to use an adaptive join? The query in question produces an adaptive join when run as a SELECT, but when run as SELECT INTO instead, it doesn't.

I have a parameterised query which has a sub-query that typically selects almost no rows (0 to 4), but occasionally picks 100k.

I believe that this is exactly the type of scenario that an adaptive join is designed for, but SQL Server doesn't pick this as a query plan.

A query plan of always-hash-join is server-destroying, so my only recourse at the moment is to force the join to be nested loops, and accept that individual worst-case requests will take a bunch of extra seconds to run.

I have an Azure SQL database at compatability 150, and BATCH_MODE_ON_ROWSTORE=1

Further details

For some reason paste-the-plan doesn't think the XML for this query is legit.

The query at issue is:

with
hashes as (select h.[ContactHash]
    FROM HashTable h
        join someTable p on h.id=p.id
    group by [ContactHash]
    having count(distinct key) >1 
        and count(distinct p.thing) between 1 and 20),
dupeContacts as 
    (select distinct c.key,c.ContactHash from someTable c 
    where c.[ContactHash] in (select t.[ContactHash] from hashes t ) and c.key =  @id 
    )
    insert  into @MyTableVar
select * from dupeContacts

The two tables involved are similar to:

HashTable (
    int id ,
    ContactHash varbinary(16)
) -- millions, typically 4:1 to someTable by ID

and

SomeTable (
    int id Primary Key , 
    varchar(100) thing
)

Paul White · Accepted Answer · 2021-10-08T11:44:12.270

As it says in the documentation, adaptive join currently supports SELECT statements only:

Adaptive join eligible statements

A few conditions make a logical join eligible for a batch mode Adaptive Join:

The database compatibility level is 140 or higher.

The query is a SELECT statement (data modification statements are currently ineligible).

The join is eligible to be executed both by an indexed Nested Loops join or a Hash join physical algorithm.

The Hash join uses Batch mode, enabled through the presence of a Columnstore index in the query overall, a Columnstore indexed table being referenced directly by the join, or through the use of the Batch mode on rowstore.

The generated alternative solutions of the Nested Loops join and Hash join should have the same first child (outer reference).

Is it possible to force an adaptive join with a query hint?

1 Answers1

Adaptive join eligible statements