Things SQL Server vNext Should Address: Lookup Placement

All Looked Up

Lookups are interesting. On the one hand, I think the optimizer should be less biased against them, and on the other hand they can cause a lot of issues.

They’re probably the most common issue in queries that suffer from parameter sniffing that I see, though far from the only unfortunate condition.

Under the read committed isolation level, lookups can cause readers to block writers, and even cause deadlocks between readers and writers.

This isn’t something that happens under optimistic isolation levels, which may or may not have something to do with my earlier suggestion to make new databases use RCSI by default and work off the local version store associated with accelerated database recovery.


Leafy Greens

One thing that would make lookups less aggravating would be giving the optimizer the ability to move them around.

But that really only works depending on what the lookup is doing. For example, some Lookups just grab output columns, and some evaluate predicates:

SQL Server Query Plan
all one word

Further complicating things is if one of the columns being output is used in a join.

SQL Server Query Plan
bad movie

Outside Chance

There are likely other circumstances where decoupling the lookup and moving the join to another part of the plan would be impossible or maybe even make things worse. It might even get really weird when dealing with a bunch of left joins, but that’s the sort of thing the optimizer should be allowed to explore during, you know, optimization.

Thanks for reading!

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.