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
)
Best Answer
As it says in the documentation, adaptive join currently supports
SELECT
statements only: