You’ll Go Blind
Video Summary
In this video, I delve into startup expression predicates and their often misunderstood role in SQL Server query execution plans. I highlight how these can appear as clever attempts to guide the optimizer but frequently lead to suboptimal performance due to cached plan issues. By examining specific query examples, I demonstrate how startup expression predicates can cause misleading execution plans, making troubleshooting a challenging task. This video wraps up a series where we explored various aspects of SQL Server’s optimization and caching mechanisms, offering insights into what to watch out for when analyzing query plans.
Full Transcript
Ah, sweet relief. If you’re watching this video, you’re watching, I think, the last video in this series. So this is about startup expression predicates, and I’ve seen people use these in vague attempts to be clever with the optimizer, but they often don’t turn out so well. So let’s look at this query plan right quick. We can see, looking at this, that we did about 300 milliseconds worth of work from here over to here, right? So if we pop on in, we can see we did some stuff here, right? We also have this filter operator. This filter operator is a startup expression predicate. So what these allow SQL Server to do is you can position them fairly early in an execution plan, and only if, like, you know, something meets this filter condition, do we do stuff on the other side of this filter. Down here we have another one. Alright, we have another startup expression predicate, and you can see that for this portion of the plan, we didn’t do anything, right? If we look at stuff from this portion of the plan, there are no rows that travel through here, and there is no time spent in here. So this startup expression predicate did not fire. This one does.
So this one does. If we did. If we scroll down a little bit further, we can see where we spent a little bit more time in the plan joining to this users table. There’s no startup expression predicate here. Based on the logic of the query, we join to the users table. Let’s get rid of some of those squiggles. We join to the users table no matter what, and then our left join, it only comes if certain expressions match these filters. So that’s one thing. That’s one execution of the plan. Well, let’s look at another one. And this one is going to take slightly longer. Let’s, um, I opened that. That felt very silly. I was like, I’m going to go do something. I’m not going to go do something. Never mind. This is silly of me. I don’t know why I would have done that. Anyway, so this one runs for around about 22 seconds. And if we look at the execution plan, if we look at the execution plan, uh, we can see differences now, right? So this portion of the plan now has zero percentage cost or zero, zero rows coming through it.
And we spent no time in any of these things, right? No, zero time, no rows. Now all of the plan cost time is spent in this one. You can see this, this startup expression predicate did, did get met. The problem is that the problem is that the cached plan reflected the first iteration. So SQL Server did a very, didn’t, just didn’t do a happy, healthy job of figuring out exactly what we were going to, um, what we were going to get from this portion of the branch. SQL Server created a plan, estimated stuff for this, for the first execution, which touched this branch. And this branch got kind of a crappy execution plan.
So we ended up doing, oh, I don’t know, four, five, nine, eight, six, five, seven. Uh, that’s, that’s, that’s seven fingers. So we ended up doing about almost 4.6 million key lookups out of an estimated one. And of course that one estimate comes from the prior execution with the parameters that we passed in. Alan is with the program your EBS has vida.
If we run the query, given what we have to look at over here, right, where we check comments, check posts, blah, blah, blah. If we run that, it’s not going to be too bad. It’s going to be fairly quick.
That query finished in, I forget how long, 11 seconds. Oh, no, well, less than that because we’re doing some averaging in here. So not a good example.
Sorry about that. Math is hard, right? So we would run this first query again. It would run fairly quickly. We would say, you’re on drugs, end user. There’s no way.
This is slow. Looking at this cached execution plan, we would focus on this branch and this branch, and we would have this branch might not jump out at us. We might be like, oh, we got to fix stuff in here. We got to do all sorts of things to this branch.
It’s all broken. It’s a broken fix it. I don’t know. So there’s also another, I’d like to speak to the manager moment here, where for some reason, we gather streams and then do a nested loops join and then distribute streams. But I would imagine that’s because of the crazy cardinality estimate based on it being part of that branch that didn’t fire.
Now, again, quite deceiving and quite frustrating. Thankfully, I don’t see people doing this a lot. And by this, I mean the thing with the startup expression predicates like we looked at over here.
Oops, that didn’t turn out well. That’s not formatted well at all. And I don’t have SQL prompt on this one, so that’s fun.
We just got to hit enter a few times, though. We’ll be all set on that. Yay, fun times. Thank you for formatting that for me. Anyway, so I don’t see people doing things like this a lot, where their join has some conditional predicate in there.
But when I do see it, I know that I’m in for a wild ride figuring out exactly how things executed and when. Anyway, sorry. I had a jungle moment and I thought back to a situation where I had to troubleshoot one of these.
And it was like two days of digging through stuff and interrogating end users and trying to figure out exactly what happened. It wasn’t enjoyable. So anyway, I need to go drink now.
This is terrible. So that was that. And this is another way in which the planned cache can lie to you. Not lie to you. It can be very deceiving. It can be very difficult to figure out exactly what you should be troubleshooting.
Anyway, thank you for watching. That is, for now, the entire series. So I’m going to say thank you for watching the entire series. Who knows?
Maybe I’ll add stuff on later. And then I’ll get to… It’ll be like a dramatic cliffhanger. Or I’ll do an encore. But for now, thank you for watching the entire thing. And I will see you in some other video some other time.
Goodbye.
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.