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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
SELECT u.Id, p.Score FROM dbo.Users_cx AS u JOIN dbo.Posts AS p ON LTRIM(RTRIM(p.OwnerUserId)) = u.Id --I'm icky and gross to Posts WHERE u.LastAccessDate >= '20160101'; SELECT u.Id, p.Score FROM dbo.Users_cx AS u JOIN dbo.Posts AS p ON p.OwnerUserId = LTRIM(RTRIM(u.Id)) --I'm a loser to Users WHERE u.LastAccessDate >= '20160101'; SELECT u.Id, p.Score FROM dbo.Users_cx AS u JOIN dbo.Posts AS p ON p.OwnerUserId = u.Id WHERE YEAR(p.LastActivityDate) >= 2016; --I'm antiPostial SELECT u.Id, p.Score FROM dbo.Users_cx AS u JOIN dbo.Posts AS p ON p.OwnerUserId = u.Id WHERE YEAR(u.LastAccessDate) >= 2016; --Sacre bleUser! |
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?
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.
2 Comments. Leave new
Whaddayanow, smarter tools are not (always) a solution for bad code writing. We might not be out of a job just yet 😉
I tested this on 2019 where we don’t need Column store Indexes on users table and the behavior is exactly the same. My question why Adaptive joins only on users table but not on Posts table.