Adaptive Joins And SARGability

There’s a famous saying

Non-SARGable predicates don’t get missing index requests.

And that’s true! But can they also stifle my favorite thing to happen to SQL Server since, well, last week?

You betcha!©

One Sided

I’m going to paste in some queries, each with something non-SARGable.

There are two tables involved: Users and Posts. Only one table will have a non-SARGable predicate in each query.

What do you think will happen?

Will all of them use Adaptive Joins?
Will some of them use Adaptive Joins?
Will none of them use Adaptive Joins?

Will you stop asking me these questions to fill space before the picture?

Half and half

I told you something would happen

The queries with non-SARGable predicates on the Users table used Adaptive Joins.

The queries with non-SARGable predicates on the Posts table did not.

Now, there is an Extended Events… er… event to track this, called adaptive_join_skipped, however it didn’t seem to log any information for the queries that didn’t get Adaptive Joins.

Bummer! But, if I had to wager a guess, it would be that this happens because there’s no alternative Index Seek plan for the Posts table with those predicates. Their non-SARGableness takes that choice away from the optimizer, and so Adaptive Joins aren’t a choice. The Users table is going to get scanned either way — that’s the nature of ColumnStore indexes, so it can withstand the misery of non-SARGable predicates in this case and use the Adaptive Join.

Thanks for reading!

Brent says: when you see stuff like this, it’s tempting to slather columnstore indexes all over your tables, including OLTP ones. Before you do that, read Niko’s post on using UPDATEs with columnstore, and watch his GroupBy session, Worst Practices and Lesser-Known Limitations of Columnstore Indexes. Yes, you can still get excited about adaptive joins, but they’re not quite a solution to bad OLTP queries – yet.

Previous Post
Adaptive Joins And Local Variables
Next Post
[Video] Office Hours 2017/05/03 (With Transcriptions)

1 Comment. Leave new

Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.