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.
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.
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:
Further complicating things is if one of the columns being output is used in a join.
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!
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.
- Things SQL Server vNext Should Address: How Did I Do?
- Things SQL Server vNext Should Address: Add Lock Pages In Memory To Setup Options
- Things SQL Server vNext Should Address: Add Cost Threshold For Parallelism To Setup Options
- Changes Coming To SQL Server’s STRING_SPLIT Function: Optional Ordinal Position